Create a SSIS Package

Sql Server Integration Services is a new ETL (Extraction Transformation and Loading) platform that ships with sql server 2005. In this blog, I will explain how to create a simple SSIS package which takes data from one table(source) which resides in database X to another table (destination) which resides in database Y. Both these databases can be in different system which can be in different locations and can also be in different domain.

You need to have proper access rights to these databases. Before creating the package, you first need to create a project that can host the package. If you have Sql server 2005 installed, Open Sql server Business Intelligence Development studio or go to Visual studio 2005 and open Integration Services project from Business Intelligence project. Specify a name for the project and click ok. Once project is created you will be able to see the package designer window of the default package called as Package.dtsx.

In solution explorer, right click the data source and click New data source. Give a data source name (for source), add the proper connection string and click ok. Repeat the same step for creating a data source for destination. Data sources are created with .ds extension. There is a connection managers section below the Package.dtsx section. Connection managers section will allow you to create connections which are needed for your package. You need to add the data sources which we have created to this section.

Right click on the connection manager and click a new connection from data source. Add the two connections which we have created in the data source. Drag and drop a data flow task from the tool box on to the control flow section. Data flow tasks helps you to move data between sources and destinations, providing the facility to transform, clean, and modify data as it is moved. Double click on data flow task --> drag and drop an oledb source and oledb destination.

Double click on oledb source, specify the connection manager ('source data source name' which we have created), data access mode (in our case its select Table or View), and the table name. Once you have selected the connection, the table dropdown will be automatically filled with all the tables in your DB. Now select the column tab and select the required columns in the table and click ok. Drag the oledb source output connector (indicated by green arrow) to the olebd destination. Open oledb destination, give the destination data source in connection manager, data access mode will be Table or View - fast load . Select the table into which the data has to be inserted. Select the mappings tab, and properly map the source columns to the destination columns... Thats it... Run the package, and if all went fine your data will be copied from the source table to destination table.