How to pass the value of a system variable to a SQL query in an Execute SQL task?

Question

SSIS 2008. A very simple task. I want to get a system variable and use it in SQL INSERT. I want to get the value of System:MachineName and use it in the insert statement.

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

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

I am not trying to pass a parameter to the request. I searched for one day, but could not find how to do this one simple thing!

Answer 1

Here is one way to do it. The following sample package was created using SSIS 2008 R2 and uses SQL Server 2008 R2 as a backend.

• Create a sample table in the SQLServer database named dbo.PackageData

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

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

• Run the package.
• You will see a new record inserted into the table. I saved the package name as Package. This is why the table

Hope this helps.

Answer 2

I have never used it before, but maybe you can check the use of the expression in the Execute SQL task for this.

Or just put the entire query in a variable expression with the calculateAsExpression parameter set to true. Then use OLE DB to insert

Answer 3

In my comment on @ZERO's answer (repeated here as an answer, so it is not forgotten by SSIS beginners).

The OP question is used heavily for SSIS property expressions.

To pass SSIS variables to the query string, you can combine it into an expression set for the SqlStatementSource property:

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

This does not mean that the accepted answer is not a good template, since, as a rule, a parameterized approach is safer (against SQL injection) and faster (when reused) than directly manipulating the query string. But for a system variable (as opposed to a user-entered string), this solution should be safe from SQL injection, and it will be about as fast or faster than a parameterized query if it is reused (since the machine name does not change).

Answer 4

Along with @ user756519 answer, Depending on your connection string, your variable names and SQLStatementSource changes