locked
Insert Statement in execute sql task RRS feed

  • Question

  • Hi,

    my sourse is sql server and destination is falt file.

    i got the data from sql server to flat file, but i have rowcount of that table. i checked the rowcount  using script task uwith MsgBox("Row count is " + Dts.Variables("rowcount").Value.ToString)
     it  shows 14000 roes.
    i want update sql server table with rowcount value.
    i have done with execute sql task using insert statement , i got the error here.

    here i have created two varibles. ROWCOUNT AND STATEMENT

    statement = INSERT INTO [tempdb].[dbo].[rowCounttable] values('+ rowcount +')

    SourseVarible is: User::Statement

    Parameter mapping: varible name is: user::rowcount

    . plz let me know where is the wrong.





    Tuesday, December 8, 2009 6:17 AM

Answers

  • Pass a parameter in insert statement:
    INSERT INTO TABLE Select ?
    Then go to Parameter Mapping inside Execute SQL Task editor and select the rowcount variable with direction as inout and datatype as Int or Long
    Nitesh Rai- Please mark the post as answered if it answers your question
    • Proposed as answer by Kunal Joshi Tuesday, December 8, 2009 6:53 AM
    • Marked as answer by Zongqing Li Monday, December 14, 2009 8:25 AM
    Tuesday, December 8, 2009 6:22 AM

All replies

  • Pass a parameter in insert statement:
    INSERT INTO TABLE Select ?
    Then go to Parameter Mapping inside Execute SQL Task editor and select the rowcount variable with direction as inout and datatype as Int or Long
    Nitesh Rai- Please mark the post as answered if it answers your question
    • Proposed as answer by Kunal Joshi Tuesday, December 8, 2009 6:53 AM
    • Marked as answer by Zongqing Li Monday, December 14, 2009 8:25 AM
    Tuesday, December 8, 2009 6:22 AM
  • hi Nitesh,
     can send that insert statement  with parameter. thats very help full for me.
    Tuesday, December 8, 2009 6:50 AM
  • Take a execute SQL task and configure it like:
    SQL Statement: INSERT INTO [tempdb].[dbo].[rowCounttable] (COLNAME) SELECT ?
    Go to Parameter Mapping tab and click on ADD
    Select the variable ROWCOUNT from the drop down box. Direction as Input,Datatype as LONG, PARAMETERNAME as 0 and PARAMETERSIZE as -1
    Nitesh Rai- Please mark the post as answered if it answers your question
    Tuesday, December 8, 2009 8:06 AM