Read our blogs, tips and tutorials
Try our exercises or test your skills
Watch our tutorial videos or shorts
Take a self-paced course
Read our recent newsletters
License our courseware
Book expert consultancy
Buy our publications
Get help in using our site
546 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owl trainers only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
Search our website
We also send out useful tips in a monthly email newsletter ...
How to refer to parameters by name, not number, in SSIS |
---|
Referring to parameters by their numeric position in a list in SSIS is relatively easy, but there is a way to refer to them by name too, as explained in this blog. |
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!
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
)
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!
Some other pages relevant to the above blog include:
Kingsmoor House
Railway Street
GLOSSOP
SK13 2AA
Landmark Offices
99 Bishopsgate
LONDON
EC2M 3XD
Holiday Inn
25 Aytoun Street
MANCHESTER
M1 3AE
© Wise Owl Business Solutions Ltd 2024. All Rights Reserved.