LearnItFirst User Forum

Welcome Guest

Search | New Posts | Members | Log In | Register

SQL Server Forum - LearnItFirst.com » SQL Server Database Administration » Integration Services (SSIS) and DTS » Variable in Data flow task

Variable in Data flow task

Options

Previous Topic · Next Topic

Jose

Posted: Thursday, October 09, 2008 8:32:18 AM

Rank: Newbie

Joined: 10/9/2008
Posts: 2
Points: 6
Where do you live?: Lisboa

I need to copy data from some tables to another tables in other server. The tables structure are the same.

At the moment, I finish this project creating a data flow taks for each pair of the tables, as you know is very tired to add source and destination 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 watch your videos talking about variables basically the video z4_Variables but it doesn't work.

Let me write what I did and what failed.
I create 2 variables
strSQL and TableName.

Following this video (z4_variables) I create 2 variables
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 data flow with source and destination and I configured it as the steps as your video and I executed it and works perfectly.

Second step:
I changed the variables values to

Following the video (z4_variables) I change manually the variables
Name: strSQL,
Value: SELECT * FROM Table2 where dDate >= ‘2001-09-01 00:00:00’

Name: TableName,
Value: Table2

Also I changed the values in Script task:

Dts.Variables("strSQL" Wink

.Value = "* FROM Table2 where dDate >= ‘2001-09-01 00:00:00’"
Dts.Variables("TableName" Wink

.Value = "Table2"

Also I configured the values ValidateExternalMetadata to FALSE for source and destination connection as your video.

I execute the package and it doesn't work.

There is a problem about mapping because the columns names changed.

If the first step works, I would like to go to second step to use the foreach loop to automatically looping by tables that I want to copy data, but it fails in the first step.

Could you help me about this issue?

Thanks a lot

Scott Whigham

Posted: Sunday, October 12, 2008 8:02:49 AM

Rank: Super Mod

Joined: 3/20/2006
Posts: 460
Points: 1,002
Where do you live?: Dallas, TX

If the column names are changing, then you've got either (a) a lot of work to do in terms of writing scripts to access the properties of the task itself and update them, or (b) could add another branch that says "Execute this Data Flow task instead". I favor "b".

In essence, you would have two Data Flow tasks: one to handle the SQL command of the first statement and a second DFT to handle the second SQL statement. Then you'd write a Script task that includes the logic to choose which to run.

It seems like you are trying to create "The Ultimate SSIS Data Transfer Package" - one that can run on any source/destination. I think that's not really a wise choice since SSIS is not really your friend but rather your nemesis - always there fighting you, telling you that you are wrong, making you feel small and insignificant. I find in these cases that I need to write specific cases rather than general cases.

Jose

Posted: Sunday, October 12, 2008 5:20:50 PM

Rank: Newbie

Joined: 10/9/2008
Posts: 2
Points: 6
Where do you live?: Lisboa

Thanks Scott,

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.

From your answer, the solution a) takes long time and I don’t have many experience about SSIS., I’m trying solution b) but I didn’t understood how can I add a branch that says "Execute this Data Flow task instead", could you explain a little bit how can I do?

To develop my solution, 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.

This weekend I watched some videos about bcb, bulk insert, openrowset. Do you think, can I resolve my problem with openrowset? I need one solution to copy data from one table to another one with the same structure.
I’ll appreciate your suggestions.

Jose

Users browsing this topic

Guest

Forum Jump

You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.