June 12, 2009 at 4:49 pm
Currently I have a SSIS package which SELECTS a subset of tables from a Source Database (SQL Server), and stores in an object variable. I want to transfer many tables from an oracle database to a sql server database with the same DDL structure.
Steps I have done so far:
1) Store Table Name in Object variable
2) Add a foreach loop container and use the object in Step 1 to loop through
3) Store the TableName in a variable
4) Add a Data Flow Task, and add a Source which uses "table name or view name variable"
5) Add a Destination which uses the same "table name or view name variable"
The package fails on the second table after the intial loop and cannot dynamically map the column names of the new table name.
My question is, Can this be done in a data flow task?
Thanks for your help in advance.
June 15, 2009 at 3:57 am
I have considered such a problem. I have thought of using sql command from variable for the data source and open rowset from variable for the destination.
If I was going to use SQL Server as source then I would loop through the tables required. Take their column details from sysobjects, and syscolumns to build two items(a sql statement to get all columns from the source table, and also a create table statement). Then for the destination I would check to see if the table exists (if not use the create to create the table). Then the OLE DB Source task would use the sql command to connect and retrieve the data. Oh forgot the openrowset from variable for the destination - use this to set the name of the target table.
:w00t:
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply