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

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 Integration Services » How to modify sql statement for all Oledb...

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

Rate Topic

Display Mode

Topic Options

Author

Message

surya.narayana

Posted Monday, August 04, 2008 9:59 AM

Grasshopper

Group: General Forum Members
Last Login: Sunday, April 19, 2009 11:56 PM
Points: 15, Visits: 81

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

Post #546203

surya.narayana

Posted Monday, August 04, 2008 11:31 PM

Grasshopper

Group: General Forum Members
Last Login: Sunday, April 19, 2009 11:56 PM
Points: 15, Visits: 81

Hi,
Can someone reply to this post? Is it possible to do this?
Surya

Post #546517

Tom Ryan-401196

Posted Tuesday, August 05, 2008 8:25 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, January 15, 2009 5:17 AM
Points: 6, Visits: 35

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.

Post #546765

qq-522733

Posted Tuesday, August 12, 2008 2:01 PM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, March 31, 2010 9:03 AM
Points: 9, Visits: 59

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.

Post #551400

Crispin Proctor

Posted Tuesday, August 12, 2008 2:17 PM

SSC Eights!

Group: General Forum Members
Last Login: Tuesday, April 16, 2013 8:31 AM
Points: 917, Visits: 399

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,
Crispin

I can't die, there are too many people who still have to meet me!

It's not a bug, SQL just misunderstood me!

Post #551418

surya.narayana

Posted Tuesday, September 02, 2008 2:45 AM

Grasshopper

Group: General Forum Members
Last Login: Sunday, April 19, 2009 11:56 PM
Points: 15, Visits: 81

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

Post #562253

« Prev Topic | Next Topic »

Permissions