May 20, 2009 at 6:45 pm
Hi All,
I've created a package variable,MyVar, and set its value to 10. Now what i want to do is i want to insert this variable value into a table called t_RowCount. For that i've selected Execute sql task.
In that i'm using the following query.
DECLARE @sql VARCHAR(60)
SET @sql = 'insert into t_RowCount values('+Cast(@MyVar as varchar(10))+',getdate())'
exec(@sql)
and in the parameter mapping i've selected the @MyVar variable.
But its throwing error. Can someone explain me how to acheive this using variable.
May 20, 2009 at 7:24 pm
What error are you seeing?
Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices
May 20, 2009 at 8:04 pm
Good practice dictates that you should name your target fields:
insert into t_RowCount(f1, f2) ....
Other than that, looks like it should work.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
May 20, 2009 at 11:20 pm
HI Tim,
I'm receiving the following error
exec(@sql)" failed with the following error: "Parameter name is unrecognized.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: Execute SQL Task
Warning: 0x80019002 at RowCountUsingVB: The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "RowCountUsingVB.dtsx" finished: Failure.
May 20, 2009 at 11:32 pm
Is the Resultset property of the Execute SQL task set to None?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
May 21, 2009 at 5:22 am
yes the resultset property is set to none
May 22, 2009 at 6:29 am
You need to create an expression for the SQLStatementSource property
Set the expression to equal:
"insert into t_RowCount values('+Cast(" + @[User::MyVar] + " as varchar(10))+',getdate())"
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply