How to modify sql statement for all Oledb Source from Script Task programmatically

Question

Post reply

How to modify sql statement for all Oledb Source from Script Task programmatically

surya.narayana

Mr or Mrs. 500

Points: 543
More actions
August 4, 2008 at 9:59 am

#122628

Hi,
I am exporting data from AS400 to SQL Server. How can i change sql query dynamically for each oledb source from Script Task?
Please help.....
Thanks in advance
Surya

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

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

surya.narayana Mr or Mrs. 500 Points: 543 More actions · Answer 1

Hi,

Can someone reply to this post? Is it possible to do this?

Surya

Tom Ryan-401196 SSC Veteran Points: 218 More actions · Answer 2

Not sure about the specifics for the AS400, but I created a "SQL command from a variable" and then use Script Component to create the SQL command. The Script Component is called before the Data Flow task.

qq-522733 SSC Journeyman Points: 97 More actions · Answer 3

You can create a variable e.g. sQuery. In the script task you make this variable writable (specify it in the ReadWriteVariables field) and then set the value of this variable in the script

Dts.Variables("sQuery").Value = "select....". Later on, you can set the expression for the SqlStatementSource to execute this query @[User::sQuery] . Hope that answers your question.

Crispin Proctor SSCoach Points: 18161 More actions · Answer 4

While the two suggestions are spot on, you do not need a script task to modify the variable value. Look at variable expressions (Highlight variable, F4). You can make the variable dynamic and do exactly what you are after.

Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!

surya.narayana Mr or Mrs. 500 Points: 543 More actions · Answer 5

Thanks a lot. I have resolved that issue as per your suggestion.

I need another help. I have prepared ssis package for downloading data from AS400 to sql server. I would like to verify row counts for both source table and destination table. How to Compare Source Tables rows and destination Tables rows in ssis package? I would like to abort the ssis process if both row counts are not the same. Do we have any component for doing this?

Please help me..... it is very urgent

Thanks,

Surya