Sunday, February 12, 2006

SSIS Variables in Execute SQL Task

When trying to use an input variable in my Execute SQL Task I was getting the following error:"Parameter name is unrecognized." and the package obviously failed.

After some playing around I noticed that it had something to do with the difference in variable mapping declaration based on your connection type.

Based on your connection type you have to choose the correct name in your Parameter Mapping window.

Here is a little overview (credits go to Kirk Haselden):
OLEDB takes ? in the query and a number as name (0, 1, ...)
ODBC takes ? in the query and a number as name (1, 2, ...)
ADO takes ? in the query and a @Variable as name
ADO.Net takes @Variable in both the query and the name

Also make sure to choose the correct datatype for the parameter!

10 comments:

Hemant J. Naidu said...

Thanks dude. You have no idea how nuts I was going trying to figure out why this wouldn't work.

WesleyB said...

I know the feeling, that's why I decided to blog it ;-)

Heather said...

Many many thank yous. You saved me from having throw my laptop out the window.

WesleyB said...

You're welcome Heather.
I'm glad I saved at least one person in this world from throwing his laptop out of the window :-)

Anonymous said...

thanks so much! was alomost gng to pull out my hair!

WesleyB said...

I know the feeling :-)

Ori said...

Thanks a lot! very helpful.

Anonymous said...

Thanks!

WesleyB said...

This seems to be a popular post.
Glad to be of help to all of you!

Anonymous said...

Thanks a lot. Your post is still helping people :-) Me in this case.