UPDATE USING EXECUTE SQL TASK

Question

UPDATE USING EXECUTE SQL TASK

hassu-807119

SSC Eights!

Points: 834
More actions
July 29, 2008 at 9:24 am

#124330

hello
I am trying to update an SS table using the Execute sql task. I am using a variable to update a column as
update PKIDS set pk_value=? where pk_name='FORM1ID'
i defined the variable in the parameter mapping as
User::pkid numeric input 0
but it isnt updating??
please help

Viewing 8 posts - 1 through 7 (of 7 total)

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

quickermind SSC Eights! Points: 848 More actions · Answer 1

I had this problem some time ago, and I just constructed a User variable containing dynanmic SQL (EXEC statement) in the preceding VB Script Task.

Then I based SQL Script Task on that variable (see SQL Task Properties).

Hope it will help.

Alex.

hassu-807119 SSC Eights! Points: 834 More actions · Answer 2

i didnt exactly understand what u said

can u elaborate it as i am not using any sp here

VG-619426 Hall of Fame Points: 3914 More actions · Answer 3

If you are not using SP, then just simplu use simple t-sql stetemnts inside execute sql task..

update table1

set col1 = 'data'

Is that what you are trying to do ? If not, please explain the problem...

-V

quickermind SSC Eights! Points: 848 More actions · Answer 4

The method that I use is the most generic one - it works irrespective of whether you use an explicit dynamic SQL (INSERT, UPDATE, or DELETE statement), or you use an EXEC statement invoking a long stored procedure.

Create VB (C#) Script Task before your SQL Script Task, and construct a User variable, say User::gvDynamicSQL, then add your SQL Script Task, and set the following properties:

SQLSourceType: Variable

SourceVariable: User::gvDynamicSQL

ResultSet: SingleRow (or None)

That's basically it. Please let us know if it works for you.

Good Luck!

Alex

hassu-807119 SSC Eights! Points: 834 More actions · Answer 5

OK Here it goes

i wrote a script which is actually writing some value in the user defined variable. then i used the post execute method to update result after the data flow

Now in the execute sql task i am using the variable as the input using the parameter mapping to put in the query posted on the first post. and all the condition are also there but there seems to be some problem

quickermind SSC Eights! Points: 848 More actions · Answer 6

Hassu,

The whole difference that a dynamic SQL makes lies in the fact that it helps you avoid explicit input parameter mapping.

You need to construct the full SQL Statement (with all parameter values) in your User-defined package variable in the preceding VB.NET (C#.NET) Script Task. You do not map any input parameters at all.

Still, you can easily map resultset fields (if you say have a single-row resultset) to User-defined package variables.

Hope this time it will work for you.

Alex

hassu-807119 SSC Eights! Points: 834 More actions · Answer 7

hassu-807119

SSC Eights!

Points: 834

July 29, 2008 at 12:21 pm

#850051

YEAH THAT SOLVED THE PROBLEM

THANKS