How do I pass system variable value to the SQL statement in Execute SQL task?

Question

SSIS 2008. Very simple task. I want to retrieve a System Variable and use it in an SQL INSERT. I want to retrieve the value of System:MachineName and use it in an insert statement.

Using the statement INSERT INTO MYLOG (COL1) SELECT @[System::MachineName] gives the error Error: ..failed to parse. Must declare the scalar variable "@"

Using the statements SELECT @System::MachineName or SELECT @@[System::MachineName] gives the error 'Error Incorrect systax near '::'

I am not trying to pass a parameter to the query. I have searched for a day already but couldn't find how to do this one simple thing!

score 16 · Accepted Answer · answered Apr 18 '12 at 21:56

Here is one way you can do this. The following sample package was created using SSIS 2008 R2 and uses SQL Server 2008 R2 as backend.

Create a sample table in your SQLServer database named dbo.PackageData

Table structure

Create an SSIS package.
On the SSIS, add an OLE DB connection manager named SQLServer to connect to your database, say to an SQL Server database.
On the Control flow tab, drag and drop an Execute SQL Task
Double-click on the Execute SQL task to bring the Execute SQL Task Editor.
On the General tab of the editor, set the Connection property to your connection manager named SQLServer.
In the property SQLStatement, enter the insert statement INSERT INTO dbo.PackageData (PackageName) VALUES (?)

General tab

On the Parameter Mapping tab, click Add button, select the Package variable that you would like to use. Change the data type accordingly. This example is going to insert the PackageName into a table, so the Data Type would be VARCHAR. Set the Parameter Name to 0, which indicates the index value of the parameter. Click OK button.

Parameter Mapping tab

Execute the package.
You will notice a new record inserted into the table. I retained the package name as Package. That's why the table

Package data

Hope that helps.

Perfect answer, and very nicely presented. I had tried this process before, and it never worked. My 'mistake' was setting 'BypassPrepare' to False, then using 'Parse Query' , which always returned an error message. Setting 'Bypass Prepare' to True was the magic secret trick. I suspected it was something simple and not well documented. Thanks so much Siva! 4 stars and a smiley face for you. — mroselius, Apr 19 '12 at 14:01
I had just learned what the "question mark" means and now this makes sense. Thank you for this thorough solution. Also, your images are broken, so I removed them. Feel free to re-add them. — Vippy, Aug 23 '17 at 17:10

Sepster · Answer 2 · 2016-05-18T06:14:09.737

Per my comment against @ZERO's answer (repeated here as an answer so it isn't overlooked by SSIS newcomers).

The OP's question is pretty much the use case for SSIS property expressions.

To pass SSIS variables into the query string one would concatenate it into an expression set for the SqlStatementSource property:

"INSERT INTO MYLOG (COL1) SELECT " + @[System::MachineName]

This is not to suggest the accepted answer isn't a good pattern, as in general, the parameterised approach is safer (against SQL injection) and faster (on re-use) than direct query string manipulation. But for a system variable (as opposed to a user-entered string) this solution should be safe from SQL injection, and this will be roughly as fast or faster than a parameterised query if re-used (as the machine name isn't changing).

score 0 · Answer 3 · answered Apr 18 '12 at 20:13

0

I never use it before but maybe you can check out the use of expression in Execute SQL task for that.

Or just put the whole query into an expression of a variable with evaluateAsExpression set to true. Then use OLE DB to do you insert

answered Apr 18 '12 at 20:13

ZERO

133
3

1

+1 This is the correct answer IMHO. The OP's question is pretty much the use case for SSIS property expressions. To pass SSIS variables into the query string one would concatenate it into an expression set for the `SqlStatementSource` property; `"INSERT INTO MYLOG (COL1) SELECT " + @[System::MachineName]`. This is not to suggest the accepted answer isn't a good pattern. – Sepster Jul 27 '15 at 00:46

score 0 · Answer 4 · answered Oct 13 '17 at 23:10

0

Along with @user756519's answer, Depending on your connection string, your variable names and SQLStatementSource Changes

answered Oct 13 '17 at 23:10

Sam

1,264
14
19

How do I pass system variable value to the SQL statement in Execute SQL task?

4 Answers4

Linked