Post reply

Error when trying to update table with RowCount Transformation Variable

  • June 27, 2008 at 11:44 am

    #122939

    so I have a data flow task with rowcount transformation. dataflow task runs fine and I even see the rows passing through.

    the variable assigned to rowcount transformation varNameBadRC is of datatype int32.

    after the dataflow task I have created a execute sql task. in which my SQLSourceType =variable and SourceVariable = user::varNameBadRC .

    within execute sql task i have also defined a expression of type SqlStatementSource which looks like this:

    "update wptable set ErrorRecodCount = " + (DT_WSTR,12)@[User::varNameBadRC] + " where tablename = 'NAME'"

    when I evalute the expression I do get a return value back of 0.

    but when I run the package the execute sql task fails with following eror:

    [Execute SQL Task] Error: Executing the query "UPDATE metadata32.dbo.wptable SET ErrorRecordCount = @varNameBadRC WHERE (TableName = 'NAME') " failed with the following error: "Must declare the scalar variable "@varNameBadRC".". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

    the column ErrorRecodCount in table wptable is of datatype int.

    what does "Must declare the scalar variable" mean? what do i need to do?

    wouldnt expression evaluation give me an error in case my variable was typed in incorrectly or doesnt exist?

    here is the evalutated expression:

    update wptable set ErrorRecodCount = 0 where tablename = 'NAME'

    I think my conversion is wrong. My variable datatype is int32. My column datatype in sql server is int. whereas I am using function DT_WSTR to cast. what function should i be using?

  • June 30, 2008 at 9:44 am

    #836220

    Yeah, you're actually making this harder than it really is. There's no need to build this statement the way you are:

    "update wptable set ErrorRecodCount = " + (DT_WSTR,12)@[User::varNameBadRC] + " where tablename = 'NAME'"

    The way you define vars in the exec sql statement is with '?'. So you would simply define User::varNameBadRC as an input variable, and then use it in the statement like this:

    update wptable set ErrorRecodCount = ? where tablename = 'NAME'

    I've actually created a video that shows you how to do this. The vid itself uses an SP in the SQL statement, but the concept is exactly the same with an adhoc SQL statement. Just take what the vid tells you and apply it to your situation and you should be fine.

    Here's the link:

    http://midnightdba.itbookworm.com/SSISExecSQLwParams/SSISExecSQLwParams.html

    Watch my free SQL Server Tutorials at:
    http://MidnightDBA.com
    Blog Author of:
    DBA Rant – http://www.MidnightDBA.com/DBARant

    Minion Maintenance is FREE:

Viewing 2 posts - 1 through 1 (of 1 total)

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