Post reply

UPDATE USING EXECUTE SQL TASK

  • 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

  • July 29, 2008 at 9:56 am

    #849930

    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.

  • July 29, 2008 at 10:00 am

    #849935

    i didnt exactly understand what u said

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

  • July 29, 2008 at 10:10 am

    #849942

    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

  • July 29, 2008 at 10:17 am

    #849951

    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

  • July 29, 2008 at 10:18 am

    #849952

    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

  • July 29, 2008 at 10:55 am

    #849994

    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

  • July 29, 2008 at 12:21 pm

    #850051

    YEAH THAT SOLVED THE PROBLEM

    THANKS

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

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