The SQL Server Integration Service is an ETL (Extract Transform and Load) Tool from Microsoft for building enterprise-level data integration and transformation solutions.
It can be used for essential data integration and workflow applications and to perform broad data migration tasks. It features a data warehousing tool for data extraction, transformation, and loading.
Required Softwares:
This tutorial assumes that you have ODBC drivers installed and a data source configured. If you have not yet done these steps, please follow these instructions. Ensure that Microsoft Visual Studio and ODBC Driver architecture matches. For example, if you have a 64-Bit Microsoft Visual Studio installed then you need to have the 64-Bit version of Progress DataDirect ODBC drivers installed. Most likely, your visual studio would be a 32-bit version and you would need a 32-bit driver.
1. Downloadand install the Progress DataDirect ODBC driver using the 15-day trial software. In this tutorial, we are connecting to DB2 Database.
2. Configure a data source for your database. See the DataDirect documentationfor assistance with setting up your data source. You will learn how to configure a DB2 Data source in the following steps.
1. After the DataDirect DB2 ODBC driver has been installed, open ODBC Administrator to configure the connection and click on “Add”.
2. Choose the “DataDirect X.X DB2 Wire Protocol” and click on “Finish.”
3. The ODBC Db2 Wire Protocol Driver Setup window will pop up.
Enter your credentials, click on “Apply” and then click on “Test Connect.”
4. The Db2 Logon screen will pop up. Provide logon credentials and click on “OK.”
5. A dialog box will confirm that the connection is established.
1. Open Microsoft Visual Studio. You need to create a new SSIS project by going to File → New → Project.
2. A new window will appear, and inside that select Integration Services Project (If not appearing, left side of the window go to Templates → Business Intelligence → Integration Services).
3. From the left side of the window, Under SSIS Toolbox → Favorites → Data Flow Task, drag and drop the Data Flow Task into Package.dtsx[Design]* Page section.