September 22, 2009 at 9:46 am
I wanted to copy multiple tables from one SQL Server to another SQL Server using SSIS package with filtering condition. for example
Source OLE DB connection will be like this.
select * from table where coulumn = 4 and column = 5
I don't want to do one by one because there are 300 tables and also those tables are huge in size.
I wanted to use FOR EACH LOOP component in SSIS package where I can rotate table name.
Appreciated if somebody will send me the article.
September 23, 2009 at 3:20 pm
Did not get any reply but I did figure out the way but there is little issue in that.
Steps created in SSIS package to import data from multiple tables,
1. Placed component Executed Task which will grab all the tables from the database
2. Placed Foreach Loop Container which will rotate all the tables in the database.
3. Created following variables
a. sSQL variable will hold the SQL query
b. sTableName will hold the table name
c. sTableOBJ will be the recordset for tables in the database.
4. Placed Script Task on the Foreach loop container which will initialize the sSQL variable and stores following value.
mSQL = "SELECT * FROM " + Dts.Variables("sTABLENAME").Value.ToString + " WHERE record_number > 700"
Dts.Variables("sSQL").Value = mSQL
5. Placed Data flow Task on Foreach loop container to change the query each time table change.
A. Placed OLE DB Source and change Data access mode to SQL Command from variable and use sSQL variable.
b. Placed OLE Destination and change the Data access mode to Table name or view name variable - fast load and variable name is user:: sTableName
It works fine if there is only one table but if there are more then one then I get the error message like
[DTS.Pipeline] Error: "component "OLE DB Source" (3445)" failed validation and returned validation status "VS_NEEDSNEWMETADATA".
Looks to me that when table changes it does not change the meta data of that tables, I don't know how to fix this issue.
need help
September 24, 2009 at 2:26 am
Yeah, the meta data issue is going to make this a tough job for you, because you need a dynamic data flow and SSIS isn't good at that.
Cozyroc has an extended DF component (see link) that might give you what you need, at a price.
Otherwise, I think you are going to end up scripting this.
As a possible alternative, you could bring across all of the data and then run some SQL at the end to delete the records you do not need. Not elegant, but does the job.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
September 24, 2009 at 9:41 am
Thanks Phil, Yes it is the problem, I might creating custom script to load data,
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply