Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
surya.narayana
surya.narayana
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
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
surya.narayana
surya.narayana
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 81
Hi,
Can someone reply to this post? Is it possible to do this?
Surya
Tom Ryan-401196
Tom Ryan-401196
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
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.
qq-522733
qq-522733
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
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.
Crispin Proctor
Crispin Proctor
SSC Eights!
SSC Eights! (917 reputation)SSC Eights! (917 reputation)SSC Eights! (917 reputation)SSC Eights! (917 reputation)SSC Eights! (917 reputation)SSC Eights! (917 reputation)SSC Eights! (917 reputation)SSC Eights! (917 reputation)

Group: General Forum Members
Points: 917 Visits: 414
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!
surya.narayana
surya.narayana
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search