This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

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.

Posted by Andy Brown on 30 January 2015

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

)

-- 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!

Some other pages relevant to the above blog include:

This blog has 0 threads

Add a new post

Head office

Kingsmoor House

Railway Street

GLOSSOP

SK13 2AA

London

Landmark Offices

99 Bishopsgate

LONDON

EC2M 3XD

Manchester

Holiday Inn

25 Aytoun Street

MANCHESTER

M1 3AE

Site map

Terms & Conditions

Book any course as classroom, onsite or online training, or as one-to-one consultancy

Free resources

Paid services

Getting help

Where we are

Where you are

Online

Transparent reviews

Delivery of courses

Nicer to work with

Reliable and established

Our training courses

C#

Excel

MySQL

Power Apps

Power Automate

Power BI and DAX

Python

Report Builder

SQL

SQL Server

VBA Macros

Other training resources

Blogs

Exercises

YouTube tutorials

YouTube shorts

Test your skills

Consultancy

Courseware

Self-paced courses

Newsletters

Publications

Our training venues

London

Manchester

Across the UK

At your site

Online

Why we are different

Hundreds of published reviews

Small class sizes

Outstanding courseware

Online classroom

(Almost) no cancellations

Wise Owl trainers only

Genuine integrity

Invoices after training

30+ years of Wise Owl

Our top 100 clients

BLOGS BY TOPIC

BLOGS BY AUTHOR

How to use parameter names in an SSIS package

Writing the stored procedure

Altering the SSIS package to call the procedure

Head office

London

Manchester