Post reply

procedure expects parameter @year, which was not supplied

  • 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

  • December 9, 2015 at 8:04 am

    #1844911

    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

  • December 9, 2015 at 8:22 am

    #1844922

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

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • December 9, 2015 at 8:55 am

    #1844942

    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

  • December 9, 2015 at 9:24 am

    #1844961

    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

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

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