SSIS - DB Destination Dynamic Table Name

1
votes

After creating tables A, B, C with Control Flow in ssis package, reading Excel with Data Flow. I want to put the data in tables A, B, C generated after processing.

Control Flow (Create table (A,B,C)) -> Data Flow (Read Excel File -> split -> insert table (A,B,C))

Control Flow

enter image description here

Data Flow

enter image description here

Create Question Table SQL Script functoid

Question table

Destination error message

Invalid object error

I think that Dynamic Table is necessary. please help you how to connect to .

1
dynamicssis
One more thing - Why are you creating your table in your SQL Task? You do it only once - Why dont you just create them, and then select them from the list instead? Unless there is some logic before i cannot see why - SqlKindaGuy
I just reproduced your scenario and it's fine unless the tables have never been created before - in this case the package validation will fail. I second @Thomas inquiry if you really need to create these tables. Could it be that you just want those tables to be cleared before inserting your data? - Filburt
Agree with @Filburt - I also just tried to recreate the scenario and i dont have any errors. Only when the table doesnt exists. Furthermore if you have same tablename under different Schemas, you might not insert into the right one. So have that in mind. Its good practice to declare schema-name infront of your tablename - SqlKindaGuy
Thank you for your answer. I'll try it tomorrow(declare schema-name). and I will add comment tomorrow. @ Filburt @ Thomas Thank you very much! :) - kthyun
Check if your tables is created first. Its the most crucial. - SqlKindaGuy

1 Answers

2
votes

You use the variables holding the desired taget table name in your Destination Table configuration.

On the Connection Manager page of your Destinations, just select Tabel name or view name variable from the Data access mode dropdown option.

Then select the variable holding the desired table name.