adding variables to a table with an execute SQL task
-
Hi, I am having issues with a logging table I setting up.
I have 5 variables that are populated in the package and then I am using an execute SQL task to add them to a table.
The SQL is
INSERT INTO CONFERMA_LOG (ROWS_ADDED, PASSED_COUNT,CITY_ERROR_COUNT,CLIENT_ERROR_COUNT,TOTAL_ERROR_COUNT)
SELECT , @[User::PASSED_COUNT], @[User::CITY_ERROR_COUNT], @[User::CLIENT_ERROR_COUNT], @[User::TOTAL_ERROR_COUNT]The error is
Must declare scalar variable @
All 5 variables are INT datatypeCan anyone suggest what I need to do to achieve this please?
Dave
- david_h_edmonds - Tuesday, March 13, 2018 4:54 AM
Sure. You have two obvious options. One is to add the 5 parameters to your ExecuteSQL task and change the query to
INSERT INTO CONFERMA_LOG (ROWS_ADDED, PASSED_COUNT,CITY_ERROR_COUNT,CLIENT_ERROR_COUNT,TOTAL_ERROR_COUNT)
VALUES (?,?,?,?,?)
The second is to build the entire SQL instruction within a calculated variable, using a suitable Expression, and use that as your query source.
I prefer option 1 for its tidiness, but sometimes (in complicated queries) you'll get a parsing error using option 1 and option 2 is the only way.The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel -
Hi Phil,
Thanks for taking the time to reply.
I have gone with option 1.
I have added all 5 parameters to the parameter mapping page like so
and changed the query to your suggestion.
I now get this error:
Attempt to parse the expression "INSERT INTO CONFERMA_LOG (ROWS_ADDED, PASSED_COUNT,CITY_ERROR_COUNT,CLIENT_ERROR_COUNT,TOTAL_ERROR_COUNT)
VALUES(?,?,?,?,?)" failed. The expression might contain an invalid token, an incomplete token, or an invalid element. It might not be well-formed, or might be missing part of a required element such as a parenthesis.
Any ideas?Dave
- david_h_edmonds - Tuesday, March 13, 2018 5:22 AMHi Phil,
Thanks for taking the time to reply.
I have gone with option 1.
I have added all 5 parameters to the parameter mapping page like soand changed the query to your suggestion.
I now get this error:
Attempt to parse the expression "INSERT INTO CONFERMA_LOG (ROWS_ADDED, PASSED_COUNT,CITY_ERROR_COUNT,CLIENT_ERROR_COUNT,TOTAL_ERROR_COUNT)
VALUES(?,?,?,?,?)" failed. The expression might contain an invalid token, an incomplete token, or an invalid element. It might not be well-formed, or might be missing part of a required element such as a parenthesis.
Any ideas?Dave
One initial idea. Change your parameter names to 0, 1, ..., 4 if you are using an OLEDB connection manager.
The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel -
I've just checked a few of my packages and noticed that I generally issue parameterised calls to stored procs, rather than parameterised DML. So I cannot confirm right now that parameterised DML works OK ... you may have to go with option 2.
The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel -
Hi Phil, renaming parameters hasn't helped.
I am going to approach this using a "derived column" method as I think it will make life easier.
I will let you know how I get on.Dave
- david_h_edmonds - Tuesday, March 13, 2018 5:33 AM
Hmmm, not sure how a derived column would work! A calculated variable is how I would do it.
The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel -
This sort of thing:
The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel -
Hi Phil, I am so sure this is easier than I am making it.
You are right, the derived column didn't work as required.So I have now mapped the parameters as follows
and set the sql statement to this:
DECLARE @0 int
DECLARE @1 int
DECLARE @2 int
DECLARE @3 int
DECLARE @4 intSET @0 = ?
SET @1 = ?
SET @2 = ?
SET @3 = ?
SET @4 = ?INSERT INTO CONFERMA_LOG (ROWS_ADDED, PASSED_COUNT,CITY_ERROR_COUNT,CLIENT_ERROR_COUNT,TOTAL_ERROR_COUNT)
VALUES (@0,@1,@2,@3,@4)This apparently is still wrong and is throwing up
The token "@" at line number "1", character number "9" was not recognized. The expression cannot be parsed because it contains invalid elements at the location specified.
-
Stand down!!!. All sorted now.
My error was to try and add the SQL as an expression here
instead of simply writing it hereNever mind.
all sorted now.
Thanks for the help.Dave
-
Parameter name should be 0, not @0, in the parameter mapping screen. All of that declare and set stuff should not be required. It really should be as simple as
INSERT INTO CONFERMA_LOG (ROWS_ADDED, PASSED_COUNT,CITY_ERROR_COUNT,CLIENT_ERROR_COUNT,TOTAL_ERROR_COUNT)
VALUES (?,?,?,?,?)As that does not work, I suggest you create a parameterised proc to do it. This definitely works:
exec dbo.InsertConferma_Log ?,?,?,?,?The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel -
I usually stay away from expressions when I assign values to variables. I generally use a SQL Task, once you figure it out, I find it easier to maintain.
Also depending on the connection type (ADO or OLE), you handle variables differently.
When I am working with Loging and small data sets, I prefer ADO which enables you to use meaning variable names like @Reccount as opposed to ?.
One more danger of using expressions, is date assignment. If you start a process before midnight and have the filename be an expression based on date, if the process runs past midnight the filename will change and since the filename changed it might impact processing (Speaking from experience). - Chris Hurlbut - Wednesday, March 14, 2018 10:55 AM
But this requires a call to the SQL database engine, which adds unnecessary load.
Dates in dynamically named files should usually be handled by a foreach container, to avoid any such issues.
Your ADO comment is valid, though SSIS seems to provide more all-round support for OLEDB. There are certain calls which just cannot be made from OLEDB connections.The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel -
which adds unnecessary load...
So, at the cost of keeping it simple and easy to maintain, I'll take it.
Also if the expressions are complex, the expression builder can be a pain as opposed to building the variable in SQL.
After programming for 30+ years, I like to keep my processes as simple and easy to maintain as possible. - Chris Hurlbut - Wednesday, March 14, 2018 11:43 AM
I have a similar level of experience, perhaps enough to ensure that I can maintain even complex expressions without difficulty.
I also believe that 'every little helps' when it comes to SQL Server performance.The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply