June 26, 2020 at 11:40 am
Hi there
I have a SSIS 2008 package which is using an Execute SQL Task to call a Stored Procedure
Now this Execute SQL Task takes in a SQL Statement as SourceVariable which is called User::LoadPushPortfolioData_Command
In my SSIS Variables window I have defined User::LoadPushPortfolioData_Command as the following
exec [System].[csp_LoadPushPortfolioData_Seg]
@RunID='"+(DT_WSTR,4)(@[User::RunID])+"',
@FileImportID='"+(DT_WSTR,4)(@[User::FileImportID])+"',
@pUpdateOnly="+(DT_WSTR,1)(@[User::UpdateOnly] ? 1 : 0)+",
@pClientBrandId="+(DT_WSTR,4)(@[User::ClientBrandId])+",
@pProductType="+(DT_WSTR,4) @[User::ProductType] +",
@pUserRole="+(DT_WSTR,4) @[User::UserRole]
And I have defined all of the User:: variables in the sp call above
However when I goto to run this I get the following error
[Execute SQL Task] Error: Executing the query "exec [System].[csp_LoadPushPortfolioData_Seg] @Ru..." failed with the following error:
"Incorrect syntax near '+(DT_WSTR,4) @[User::UserRole]
'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Do I need to place the definition of User::LoadPushPortfolioData_Command in double quotes
or do I remove the casting of the variables to data Types?
June 26, 2020 at 12:29 pm
I'm a 2017 user, and cannot remember how things looked in 2008, but is there a button/link called 'Evaluate' which allows you to see what the variable looks like after the expression is parsed and, err, evaluated?
(Please confirm that you have used an expression to define the variable.)
I would have expected something more like the following:
"exec [System].[csp_LoadPushPortfolioData_Seg] @RunID = '" + (DT_WSTR,4)(@[User::RunID]) + "', @FileImportID = '" + (DT_WSTR,4)(@[User::FileImportID]) + "'"
etc etc. It is the literal text which needs to be enclosed in quotes, not the variables.
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
June 26, 2020 at 1:29 pm
Hi Phil
There doesn't appear to be an Evaluate expression for variables in 2008 like you have in 2016, 2017
In my SSIS Variables window... I used this for the value
exec [System].[csp_LoadPushPortfolioData_Seg] @RunID='0',@FileImportID='0', @pUpdateOnly=0, @pClientBrandId=1001, @pProductType=4, @pUserRole=1
I then tried what you suggested for the Value
and got the following error message
[Execute SQL Task] Error: Executing the query ""exec [System].[csp_LoadPushPortfolioData_Seg] @Ru..." failed with the following error: "Incorrect syntax near '+'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
June 26, 2020 at 1:58 pm
Can you post a screenshot of the variables window, showing the expression?
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
June 26, 2020 at 3:00 pm
OK, thank you. Please take a look at this link: https://www.sqlchick.com/entries/2011/12/31/a-better-way-to-work-with-ssis-variable-expressions.html
Notice that, within the Expression Builder dialog, there is an 'Evaluate Expression' button. Use that button to fine-tune your expression (see the 'Evaluated Value' box). You should get to the point where you can copy the evaluated text into SSMS and execute it.
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
June 29, 2020 at 9:13 am
Hi phil
I figured this one out. OK in SSIS 2008....the Evaluate function for Variables is not in the Variables pane
Its actually in the properties of the Variables
So what I had to do was the following in Properties (For the Variable)
1 Ensure that EvaluteAsExpression is set to True
2 Then click on the Elipsis button on Expression
Its there that I can enter my expression as follows
"exec [System].[csp_LoadPushPortfolioData_Seg] @RunID='"+(DT_WSTR,4)(@[User::RunID])+"',@FileImportID='"+(DT_WSTR,4)(@[User::FileImportID])+"', @pUpdateOnly="+(DT_WSTR,1)(@[User::UpdateOnly] ? 1 : 0)+", @pClientBrandId="+(DT_WSTR,4)(@[User::ClientBrandId])+", @pProductType="+(DT_WSTR,4) @[User::ProductType] +", @pUserRole="+(DT_WSTR,4) @[User::UserRole]
In 2017 the EvaluateAsExpression is built into the bar
It now works!
thanks for your help on this 🙂
June 29, 2020 at 12:05 pm
Well done for finally getting there.
Next step: start a project to upgrade your SQL Server environment to something more modern!
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
June 29, 2020 at 2:42 pm
Thanks Phil
Re upgrading our SQL Server environment , I wish I could . But the powers that be ...seem to be at the mercy of product owners and happy to stay on SQL 2008 R2 for the time being (even though MS support for this has ended!)
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply