How to make ssis package to read data from db2 and insert to SQL server ?

Ahmed Abd El Aziz 315 Reputation points
2023-08-10T07:51:22.77+00:00

I have two servers one server have sql server database and another server have db2 database

I need to read data from db2 database on server A then after read it insert it into sql server database that exist on server B

I use sql server 2017

so can you give me How to create ssis package to make that by details

I not working on ssis package before so if you can give me answer by more details will be better ?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,788 questions
SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,577 questions
SQL Server Migration Assistant
SQL Server Migration Assistant
A Microsoft tool designed to automate database migration to SQL Server from Access, DB2, MySQL, Oracle, and SAP ASE.
532 questions
0 comments

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Amira Bedhiafi 24,531 Reputation points
    2023-08-10T09:20:19.37+00:00

    First, ensure that the appropriate DB2 drivers are installed on the machine where you're designing the SSIS package. The drivers will allow SSIS to communicate with the DB2 database and that you have the required permissions to read from the DB2 database and write to the SQL Server database.

    Then, create a New Connection to the DB2 Database and configure the connection using the appropriate DB2 drivers and credentials.

    Create a New Connection to the SQL Server Database and select "OLE DB" as the connection manager type. and configure the connection to your SQL Server database using the correct server name and authentication method.

    Create a Data Flow Task and in the data flow designer, drag an "ODBC Source" from the toolbox to the design surface and configure it to use the DB2 connection manager you created earlier.

    Add a Destination for the SQL Server Database and choose the destination table or write the SQL query to insert the data into the SQL Server database.

    Inside the OLE DB Destination, click on "Mappings" to map the columns from the source to the destination.

    You can adjust any transformations or mappings as needed.

    0 comments
  2. Javier Villegas 900 Reputation points MVP
    2023-08-10T12:47:10.1533333+00:00

    Please make sure you have the latest and greatest ODBC drivers for DB2 installed on the computer where you run the SSIS package. that's the most important part. then SSIS will allow you to use it as source

    0 comments
  3. ZoeHui-MSFT 36,511 Reputation points
    2023-08-11T02:34:20.7433333+00:00

    Hi @Ahmed Abd El Aziz,

    You may use ODBC to connect to DB2 and then load data to sql server database.

    Check Connect to DB2 database from SQL Server Integration Server for detail steps.

    Regards，

    Zoe Hui

    If the answer is helpful, please click "Accept Answer" and upvote it.

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.