CODEWITHSUNDEEP
variablesssis
mroselius
mroselius

Reputation: 83

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

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!

Upvotes: 8

Views: 46726

Answers (4)

Sam
Sam

Reputation: 1376

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

https://learn.microsoft.com/en-us/sql/integration-services/control-flow/execute-sql-task

Upvotes: 0

Sepster
Sepster

Reputation: 4848

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).

Upvotes: 1

user756519
user756519

Reputation:

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.

Upvotes: 16

ZERO
ZERO

Reputation: 133

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

Upvotes: 0

Related Questions