creating dataflow dynamically

Question

creating dataflow dynamically

perdijc

SSC Journeyman

Points: 98
More actions
October 10, 2008 at 3:10 am

#194430

I need to copy data from some tables to another tables in other server. The tables structure are the same.
I need to copy data from one server to another one. The tables structure are the same.
At the moment, I finish this project creating a data flow tasks for each pair of the tables.
Now I would like to change to just one data flow and dynamically change SQL command Statement from source connection and the table name for destination connection, using dataflow into the foreach loop with variables. I think this is the way for the solution.
I created 2 variables as the following:
Name: strSQL,
Scope: Package1
DataType: String
Value: SELECT * FROM Table1 where dDate >= ‘2001-09-01 00:00:00’
Name: TableName,
Scope: Package1
DataType: String
Value: Table1
I created a script task and I wrote the following code:
Dts.Variables("strSQL").Value = "* FROM Table1 where dDate >= ‘2001-09-01 00:00:00’"
Dts.Variables("TableName").Value = "Table1"
Also I created a data flow with source and destination oledb and I configured its using sql command from variable for the source connection and table variable for destination connection. I also setup the ValidateExternalMetadata to FALSE in both connections tasks.
I execute this package and it works perfectly.
SECOND STEP
I deleted the records into the destination table
I changed the variables values to
Name: strSQL,
Value: SELECT * FROM Table2 where dDate >= ‘2001-09-01 00:00:00’
Name: TableName,
Value: Table2
I Also changed the values in Script task:
Dts.Variables("strSQL").Value = "* FROM Table2 where dDate >= ‘2001-09-01 00:00:00’"
Dts.Variables("TableName").Value = "Table2"
I execute the package and it doesn't work.
There is a problem about mapping because the columns names changed.
So, I cannot pass dynamically the variables to source and destination connection.
Is really possible to create a dataflow dynamically?
If yes, please I need help.
Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

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

hodgy SSCertifiable Points: 5906 More actions · Answer 1

i think when you set the strSQL variable you should include the "SELECT " before the rest of the statement.

also, you don't need script tasks to change variable values; you can do it within the variable itself by setting the EvaluateAsExpression property to true, and set the value with the expression builder.

tom

Life: it twists and turns like a twisty turny thing

perdijc SSC Journeyman Points: 98 More actions · Answer 2

Thanks for your answer.

I think the problem is not the statement because I created a temp table and then I used it for the source connection and I get the same error.

I think the problem is about mapping (input and output columns). Manually, if we change the source table, then we must mapping the source destination.

I don't know if there is a technique to do this programmatically. I think there is. Normally, if we do manually, also we can do programmatically

hodgy SSCertifiable Points: 5906 More actions · Answer 3

Ah, I see. I misread your first post.

So, what you are trying to do is run one data flow task for two sets of tables that have differing metadata. Is that correct?

If so, then you would have to create the data flow programmatically. I'm not sure, but you may need to create the whole package programmatically. I'm sure somebody will correct me on this if this is wrong.

However, if you have only two tables, and you already have built the data flow tasks, why would you need to do this?

Tom

Life: it twists and turns like a twisty turny thing

perdijc SSC Journeyman Points: 98 More actions · Answer 4

Yes, it is true what you wrote.

I don’t have only 2 tables, I’m testing with 2 tables, if it works with 2 tables, then it will work with many tables.

At the moment I have about 60 tables and in future I will have more because the database is in development.

So, I created a table, called A1_SSIS where I inserted the name of tables and a critDate column to use as date criteria as the following SELECT * FROM variableTableName WHERE dDate >= @critDate

The idea is, I must add a sql task where it memorizes the names of the tables and critDate to a recordset and then I must add a foreach loop who should be pass the parameters to dataflow that is inside of foreach loop until completing the loop.

What I need, is creating the package programmatically, but I don’t know how I can do.

I still need help.