Transferring Data – Integration Services

Leave a comment (1) Go to comments

Now will learn how to transfer large amounts of data to and from Microsoft® SQL Server® 2008 using a variety of import and export tools, including bcp, bulk insert, and SQL Server Integration Services.  The power of SQL Server can be magnified by connecting it to other data sources.

What is Data Transfer?

Copying Data Between Servers

To bulk-transfer data from one Microsoft® SQL Server® database to another, data from the source database must first be bulk-exported into a file. The file is then bulk-imported into the destination database.

The most common tools for copying data between servers are bcp, the T-SQL commands BULK INSERT and INSERT…SELECT, and the SSIS Import and Export Wizard.

Copying between different collations

As databases expand to support a growing global market, users must be able to work with character data in meaningful ways. Collations let users sort and compare strings according to their own conventions. Collations are a critical part of creating a database and manipulating data.

In SQL Server, data can be moved between Unicode columns seamlessly and conversion is unnecessary. Data moved between columns containing non-Unicode character data, however, must be converted from the source code page to the target code page. A code page is an ordered set of characters of a given script in which a numeric index, or code point value, is associated with each character. Code pages support character sets and keyboard layouts used by different Microsoft Windows locales.


For an introduction to code pages and collations, see Collation and Unicode Support. For in-depth information, see Working with Collations.

Exporting query data to a file

If transferring data from a query, most likely the best tool is bcp.

Bulk exporting data from a table or view does not guarantee the order in which the data is written to the data file. You can use a query to ensure that a bulk-export operation preserves the order of the table data in the data file.

Note that some Transact-SQL statements returns multiple result sets; for example, a SELECT statement that specifies the COMPUTE clause or a stored procedure that contains multiple SELECT statements. If the Transact-SQL statement returns multiple result sets, only the first result set is copied; subsequent result sets are ignored.

Moving to or from a temporary table or a view

The bcp command is useful to export data to a temporary table or a view.  Importing can be accomplished with BULK INSERT.

Tools for Data Transfer

Bcp

The bcp utility bulk copies data between an instance of Microsoft SQL Server 2008 and a data file in a user-specified format. The bcp utility can be used to import large numbers of new rows into SQL Server tables or to export data out of tables into data files. Except when used with the queryout option, the utility requires no knowledge of Transact-SQL. To import data into a table, you must either use a format file created for that table or understand the structure of the table and the types of data that are valid for its columns.

BULK INSERT

A T-SQL command for Import only.  The following is the syntax:

BULK INSERT
[ database_name . [ schema_name ] . | schema_name . ] [ table_name | view_name ]
FROM 'data_file'

XML Bulk Load

To bulk import data from a data file into a SQL Server table or non-partitioned view, you can use the following:

bcp utility

You can also use the bcp utility to export data from anywhere in a SQL Server database that a SELECT statement works, including partitioned views.

BULK INSERT

INSERT … SELECT * FROM OPENROWSET(BULK…)

SQL can import and export XML in the following ways:

  • BULK importing XML data as a binary byte stream
  • Bulk importing XML data in an existing row
  • Bulk importing XML data from a file that contains a DTD
  • Specifying the field terminator explicitly using a format file
  • Bulk exporting XML data

The Import and Export Wizard and the Copy Database Wizard

Please refer to the topics specific to these wizards later in this document.

OPENROWSET(BULK)

SQL Server 2005 introduces the OPENROWSET bulk rowset provider, which is accessed by calling the OPENROWSET function and specifying the BULK option. The OPENROWSET(BULK…) function allows you to access remote data by connecting to a remote data source, such as a data file, through an OLE DB provider.

To bulk import data, call OPENROWSET(BULK…) from a SELECT…FROM clause within an INSERT statement. The basic syntax for bulk importing data is:

INSERT … SELECT * FROM OPENROWSET(BULK…)

When used in an INSERT statement, OPENROWSET(BULK…) supports table hints. In addition to the regular table hints, such as TABLOCK, the BULK clause can accept the following specialized table hints: IGNORE_CONSTRAINTS (ignores only the CHECK constraints), IGNORE_TRIGGERS, KEEPDEFAULTS, and KEEPIDENTITY.

The Import/Export Wizard

The SQL Server Import and Export Wizard can copy data to and from any data source for which a managed .NET Framework data provider or a native OLE DB provider is available. The list of available providers includes the following data sources:

  • SQL Server
  • Flat files
  • Microsoft Office Access®
  • Microsoft Office Excel®

Note that on a 64-bit computer, Integration Services installs the 64-bit version of the SQL Server Import and Export Wizard (DTSWizard.exe). However, some data sources, such as Access or Excel, only have a 32-bit provider available. To work with these data sources, you might have to install and run the 32-bit version of the wizard. To install the 32-bit version of the wizard, select either Client Tools or Business Intelligence Development Studio during setup.

The Import and Export Wizard can be used to rapidly create a package with some limitations.  Besides being quick to implement, the wizard does not require in depth understanding of Business Intelligence Development Studio.

In Next Post, we will cover

Database Copy Wizard

  • Considerations for Bulk Import/Export
  • Bulk Import/Export Tools
  • XML Bulk Load
  • Introduction to SQL Server Integration Services

Here is the next post

EOF - Transferring Data – Integration Services, SQL Server 2012 , 2008 R2 , 2008 , 2005 , 2000
Leave a comment

1 Comments.

Leave a Reply

Your email address will not be published. Required fields are marked *


*


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Notify me of followup comments via e-mail. You can also subscribe without commenting.

Trackbacks and Pingbacks: