Building Packages for Data Transfers

This example shows how to import a Sybase database table into a Microsoft SQL Server database.

Start Microsoft SQL Server Business Intelligence Development Studio.
Create a Studio project or select an existing project.
You must start the SSIS Import and Export Wizard to import a database table into Microsoft SQL Server. To do this, select Project / SSIS Import and Export Wizard. The main screen of the wizard appears.





Click Next. The Choose a Data Source window appears.





In the Data source drop-down list, select a data provider to use for importing the data into Microsoft SQL Server. In this example, DataDirect SSIS OLE DB Provider for Sybase is selected. After selecting this value, the Choose a Data Source window reappears.





Click Properties. The Data Link Properties Window appears.





In this window, perform the following steps:
In the Data Source field, specify the path and file name of the data source (the .ids file) to use for the Sybase connection.
Enter the information needed to log on to the server. For example, a user name and password.
Click OK to go back to the Choose a Data Source window.

NOTE: You can test the connection to the database by clicking Test Connection. This example connects to the Sybase database using the testSSIS6 data source.

Click Next. The Choose a Destination window appears.





In this window, you can either select an existing Microsoft SQL Server database or create a new database to which the Sybase data will be transferred. In this example, we create a new Microsoft SQL Server database. After clicking New, the Create Database dialog box appears.





In this window, you specify the name and properties for the Microsoft SQL Server database. For example, name the database SSExampleDB. Click OK to go back to the Choose a Destination window.

Click Next. The Specify Table Copy or Query window appears.





In this window, you specify whether to copy the data of one or more tables and views to the new Microsoft SQL Server database or to copy the results of a query to the new Microsoft SQL Server database. In this example, data is copied from a table.
Click Next. The Select Source Tables and Views window appears.





In this window, select the tables and views that you want to copy to the new Microsoft SQL Server database. For example, select test.test04.EMP. To ensure that each Sybase column is mapped to an appropriate data type, click Edit in the Mapping column next to the table you are copying. The Column Mappings window appears.





In this window, the Source and Destination columns list the names of the data columns in the Sybase table and their corresponding names in the new Microsoft SQL Server table. You can specify the attributes (data type, whether the column can contain a NULL value, size, precision, and scale) for each data column in this dialog box. After defining the attributes of each data column, click OK. The Select Source Tables and Views window appears.
Click Next. The Complete the Wizard window appears.





Click Finish to create the data transfer package. The wizard displays a screen to let you know the execution was successful.





Click Close to exit the wizard and go to Microsoft Visual Studio. At this point, a package has been created, but the data has not been transferred.
In Visual Studio, you must execute the package to transfer the data. In the Solution Explorer pane, right-click the package name and select Execute Package. In this example, the package name is Package2.dtsx.





The Sybase database table EMP has now been imported into the Microsoft SQL Server database SSExampleDB.