BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
Posted by Andy Brown on 30 January 2015
You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.
How to use parameter names in an SSIS package
Another blog triggered by a question from a course: how do you use parameter names in SSIS?
The title of the column says Parameter Name, but we've used the index number of the parameter.
Note that the example used on this page comes directly from this blog, but it's easy enough to get the principles without recreating the example.
The answer to the question: if you're using a simple SQL statement, I don't think you can. However what you can do is to change your statement to use a stored procedure. The rest of this blog shows how!
Writing the stored procedure
For the package above, the SQL statement we're using is this:
The INSERT INTO statement - or at least, the first bit of it.
And since that wasn't very clear, let's drill down a bit:
The full SQL command, relying on the fact that the parameters will be numbered 0 and 1.
Here's a stored procedure to do the same thing (if you don't know stored procedures, you're probably reading the wrong blog - try here):
CREATE PROC spInsertFile(
@FileName nvarchar(max),
@LastUpdated datetime
)
AS
-- insert row into file table
INSERT INTO tblFile(
FileName,
LastUpdated
) VALUES (
@FileName,
@LastUpdated
)
Altering the SSIS package to call the procedure
Now we just need to change the SQL command run in SSIS:
I realise we're not yet using the parameter names, but softly, softly, catchee monkey ...
You don't need to change the IsQueryStoredProcedure property (which is just as well, as in this window at any rate it's read-only).
For the parameter mapping for the ExecuteSQL task, prefix the parameter names with @ signs (the bit which eluded me for ages):
Prefix the parameter names with @ signs.
This should now all work!