CODEWITHSUNDEEP
sql-serveretldynamically-generateddb2-400ssis-2008
Pad
Pad

Reputation: 45

Dynamically create destination table from source server with SSIS

I need a bit advice how to solve the following task:

I got a source system based on IBM DB2 (IBMDA400) which has a lot of tables that changes rapidly and daily in structure. I must load specified tables from the DB2 into a MSSQL 2008 R2 Server. Therefore i thought using SSIS is the best choice.

My first attempt was just to add both datasources, drop all tables in MSSQL and recreate them with a "Select * Into @Table From @Table". But I was not able to get this working because I could not connect both OLEDB Connections. I also tried this with an Openrowset statement but the SQL Server does not allow that for security reasons and I am not allowed to change that.

My second try was to manually read the tables from the source and drop and recreate the tables with a for each loop and then load the data via the Data Flow Task. But I got stuck on getting the meta data from the Execute SQL Task... so i dont got the column names and types.

I can not believe that this is too hard to archieve. Why is there no "create table if not exist" checkbox on the Data Flow Task?

Of course i searched for the problem here before but could not find a solution.

Thanks in advance, Pad

Upvotes: 1

Views: 4076

Answers (1)

Pad
Pad

Reputation: 45

This is the solution i got at the end:

  1. Create a File/Table which is used for selection of the source tables.
  2. Important: Create a linked Server on your SQL Instance or a working Connectionstring for the OPENROWSET (i was not able to do so - i choosed the linked server)
  3. Query source File/Table
  4. Build a loop through the resultset
  5. Use Variables and Script Task to build your query
  6. Drop the destination table
  7. Build another Querystring with INSERT INTO TABLE FROM OPENROWSET (or if you used linked Server OPENQUERY)
  8. Execute this Statement

Done. As i said above i am not quite happy with this but for now it should be ok. I will update this if i got another solution.

Upvotes: 1

Related Questions