SSIS Variables In SQL Statements
Posted on September 22nd, 2008
Is your first time on TechnoSNACK? Subscribe our feeds!
You can use the SQL task in SSIS to compose a quick SQL statement that returns some results or does some DML that returns nothing.
If you try and put in your SQL statement the way you might normally do it in .NET, you are going to find out that SSIS is not fond of the @ syntax for parameters.
You will see an error like this when you try and parse.
You can instead write a statement like this...
INSERT INTO tblStores (StoreNumber, StoreName)
VALUES (?, ?)
Then in the parameter mapping, define your parameters with the names 0 and 1 for their respective "?" placeholders.
This ALSO won't parse, but it will work if you just "OK" your way out of the settings box.
It depends… You are correct for OLEDB connections.
If you use an ADO.Net Connection Manager, you are allowed to use @ syntax
INSERT INTO tblStores (StoreNumber, StoreName)VALUES (@myStoreNumber, @StoreName)
of course, that supposes that you have mapped those SQL variable names to SSIS variables in the Parameter Mapping of the SQL Task.
It’s needlessly complicated, but works well enough once you get the hang of it.