How to: Bulk Load Data Using the SQL Server Destination

  • Article

To add and configure a SQL Server destination, the package must already include at least one Data Flow task and a data source.

To load data using a SQL Server destination

  1. In Business Intelligence Development Studio, open the Integration Services project that contains the package you want.

  2. In Solution Explorer, double-click the package to open it.

  3. Click the Data Flow tab, and then, from the Toolbox, drag the SQL Server destination to the design surface.

  4. Connect the destination to a source or a previous transformation in the data flow by dragging a connector—the green or red arrow—to the destination.

  5. Double-click the destination.

  6. In the SQL Server Destination Editor, on the Connection Manager page, select an existing OLE DB connection manager or click New to create a new connection manager. For more information, see OLE DB Connection Manager.

  7. To specify the table or view into which to load the data, do one of the following:

    • Select an existing table or view.
    • Click New, and in the Create Table dialog box write an SQL statement that creates a table or view.

  8. Click Mappings and map columns from the Available Input Columns list to columns in the Available Destination Columns list by dragging columns from one list to another.

    Note

    The destination automatically maps same-named columns.

  9. Click Advanced and set the bulk load options: Keep identity, Keep nulls, Table lock, Check constraints, and Fire triggers.

    Optionally, specify the first and last input rows to insert, the maximum number of errors that can occur before the insert operation stops, and the columns on which the insert is sorted.

    Note

    The sort order is determined by the order in which the columns are listed.

  10. Click OK.

  11. To save the updated package, click Save Selected Items on the File menu.

See Also

Concepts

SQL Server Destination
Integration Services Transformations
Integration Services Paths
Data Flow Task
Data Flow How-to Topics (SSIS)

Other Resources

Integration Services Sources
Integration Services Destinations

Help and Information

Getting SQL Server 2005 Assistance