Migrate data from Azure SQL to DB2 via SSIS. Insert if id does not match and update name if Id matches

Ritu Murlidhar Khilrani 1 Reputation point
2022-01-25T10:08:00.38+00:00

I want to create an SSIS package with source as azure sql and destination as DB2 table.
I have to check if Id (int) in sql matches with Id (X12 basically varchar) in DB2, it it matches, I have to update name in db2 with the name in SQL
else insert new record in DB2
Since, datatype of id does not match, I have to do a data time conversion- that is if id in sql is 1 .. I have to convert it to varchar with leading zeros - 00000000001
Can some one please provide a step by step approach?
and where exactly can I make this conversion.. in which task?

SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,452 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.
494 questions
C#
C#
An object-oriented and type-safe programming language that has its roots in the C family of languages and includes support for component-oriented programming.
10,234 questions
0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. ZoeHui-MSFT 32,736 Reputation points
    2022-01-26T06:27:12.74+00:00

    Hi @Ritu Murlidhar Khilrani ,

    You may use Lookup Transformation to meet your need.

    Note that the reference dataset can be a cache file, an existing table or view, a new table, or the result of an SQL query. The Lookup transformation uses either an OLE DB connection manager or a Cache connection manager to connect to the reference dataset.

    I'd suggest that you may import the reference table to SQL Server database via SSIS first.

    lookup-transformation

    an-overview-of-the-lookup-transformation-in-ssis

    Regards，

    Zoe

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments