Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to modify sql statement for all Oledb Source from Script Task programmatically Expand / Collapse
Author
Message
Posted Monday, August 04, 2008 9:59 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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
Posted Monday, August 04, 2008 11:31 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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
Posted Tuesday, August 05, 2008 8:25 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Tuesday, August 12, 2008 2:01 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Tuesday, August 12, 2008 2:17 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, October 11, 2013 7:41 AM
Points: 917, Visits: 410
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
Posted Tuesday, September 02, 2008 2:45 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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 »

Add to briefcase

Permissions Expand / Collapse