procedure expects parameter @year, which was not supplied

Question

procedure expects parameter @year, which was not supplied

DamianC

SSCrazy Eights

Points: 8023
More actions
December 9, 2015 at 7:58 am

#306991

Hi
I have been trying to get my head around parameter and variable passing in SSIS
It should be pretty straight forward but I can't get variable passing to a stored procedure working
I have a SP (mspDetail) that takes 2 variables and runs a select statement to return a set of data
In SSIS, I have created 2 variables, theMonth and theYear
Both Int32
theMonth has a value of 11
theYear has a value of 2015
I have created a data flow task
The source runs a sql command, which is my SP
The destination is a table
I've mapped the Parameters as:
Parameter @theYear; Variables User::theYear; Param direction Input
Parameter @theMonth; Variables User::theMonth; Param direction Input
Data Access Mode is SQL command
My SQL command text is:
exec mspDetail @theYear = ?, @theMonth = ?
This returns an error 'Procedure or function 'mspDetail' expects parameter '@year', which was not supplied
If I change the code the following, it works
declare @theYear int = 2015
declare @theMonth int = 15
exec mspDetail @theYear, @theMonth
What am I doing wrong here?
Ultimately, I want to be able to prompt and pass to the package at runtime but can't get past this bit!
Thanks
Damian.
- Damian

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Damian

The "?" placeholder isn't used by all providers. Check your connection manager to see what provider you're using to connect to the database, then check the documentation to see what the appropriate placeholder is. Alternatively, just go with what you've already found to work!

John

Phil Parkin SSC Guru Points: 246993 More actions · Answer 2

Also, note that SSIS is not really suitable for 'run-time prompting' as it is intended to run unattended on a server.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

DamianC SSCrazy Eights Points: 8023 More actions · Answer 3

Am I going about this the wrong way?

I really want to create a csv file

Give the file a unique dynamic name based upon the date and time (I haven't got to that part yet)

The data contained in the file should relate to the current month and year (the bit I am on with)

It comes from multiple places and goes into one table so I have created multiple data flows

The default will be current month and year but I do want it to be flexible so other months/years can be passed

I've created SP's to populate the table

I was looking to parameterise everything and pass the calculated month/year as the first part

That way I can schedule with 2 (automatically calculated) variables that I pass to the package when it runs

Problem is, I can't seem to get the whole parameter passing side working correctly

Thanks

- Damian

DamianC SSCrazy Eights Points: 8023 More actions · Answer 4

ok, so it looks like I was inconsistent with my variable names

You have to use the exact same names as used in the SP

e.g. if called @year in the SP, call the parameter @year in SSIS

- Damian