Combine tables from source database to destination database in SSIS

Question

Combine tables from source database to destination database in SSIS

incubator123

Grasshopper

Points: 21
More actions
July 13, 2011 at 5:11 am

#244036

Hi All
i have a set of tables names in a flat file.I need to read the names from the file and to take the data from each table and transfer it to a single table in a different database.Daily i need to do this and each day the tables names will be different.
for ex:property_data 1,property_data2 ,property_data3 are my input tables
destination table:property_data
need to transfer all the data from property_data 1,property_data2 ,property_data3 to property_data
Can you suggest any method to implement this process.
Regards
Jenniffer

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 1

You can read the contents of the file and use a Recordset destination to store it into an object variable.

Then you can use a ForEach Loop that uses an ADO.NET enumerator to loop over the object variable (in every iteration of the loop you'll get a different tablename, which you save into a string variable).

Inside the ForEach Loop I would place an Execute SQL Task. The SQL statement comes from yet another variable, let's call it SQLStatement. This variable has the property EvaluateAsExpression to TRUE and the value is:

"select * into DBDestination." + @[User::TableName] + " FROM DBSource." + @[User::TableName]

I made the assumption that the destination table doesn't exist yet.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

incubator123 Grasshopper Points: 21 More actions · Answer 2

Hi

Thank you for reply.But in my case the destination table is already exist and i want to maintain the data already there in that table also..Please help me!!

Regards

Jenniffer

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 3

incubator123 (7/14/2011)
Hi
Thank you for reply.But in my case the destination table is already exist and i want to maintain the data already there in that table also..Please help me!!
Regards
Jenniffer

In that case you'll need to dynamicall build the INSERT ... SELECT statement using the metadata of the columns. I do hope they have the same column names?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

incubator123 Grasshopper Points: 21 More actions · Answer 4

Hi

Yes all the tables have the same colum names.

Thanks

Jennifer

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 5

In that case I would build a dynamic INSERT statement using the metadata found in the INFORMATION_SCHEMA tables. Find all the columns for a specific table and create on the fly your insert statement.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

krish.sett SSC Eights! Points: 974 More actions · Answer 6

Other way of doing is to use staging table...

First insert the data from excel to a staging table and then write union all Query on the staging table to append the data from all columns and then insert into targetn table...