none
Using stored procedure as OLE DB source with parameters from package variables

    Question

  • Hi Guys,

    (I have searched this forum extensively, but still can't find the solution to this problem)

    Here it is:

    I have step in my ETL process that gets facts from another database. Here is how I set it up:

    1) I have to package variables called User::startDate and User::endDate of data type datetime

    2) Two separate Execute SQL Tasks populate those variables with appropriate dates (this works fine)

    3) Then I have a Data Flow Task with OLE DB source that uses a call to a sproc of the form "exec ETL_GetMyData @startDate = ?, @endDate = ?" with parameters mapped accordingly (0 -> User::startDate, 1 -> User::endDate)

    When I run this I get an error 0xC0207014: "The SQL command requires a parameter named "@startDate", which is not found in the parameter mapping."

    It is true that the sproc in fact requires @startDate and @endDate parameters, so next thing I tried to do is call the sproc the following way: "exec ETL_GetMyData @startDate = ?, @endDate = ?"

    To no avail. It gives me the same error. Incidentally, when I hard code both dates like "exec ETL_GetMyData '2006-04-01', '2006-04-02'" everything works well.

    Also, I want to mention that in the first two cases, I get an error right in the editor. When I try to parse the statement it gives me "Invalid parameter number" message.

    This has been such a pain in my neck. I've waisted the whole day trying to monkey with the various parts of package/statements to get this to work and it still doesn't. I dont' want to say anything about Integration Services design right now, but you probably know what I'm thinking...

     

    Tuesday, April 04, 2006 9:37 PM

Answers

  • The parameters used by an EXEC statement are mapped by name. The parameter names must match the names that the stored procedure, run by the EXEC statement, expects. So in this case, you should name your parameters as @startDate and @endDate, including the @ sign, instead of 0/1/2... etc.

     

    The parameter used by a select statement are mapped by order.

    Tuesday, April 04, 2006 10:53 PM

All replies

  • Don't panic. Messing about with parameters is infuriating, believe me I know that. But you have an easy alternative. Use a variable with EvaluateAsExpression=TRUE to build your sproc call.

    Here's how: http://blogs.conchango.com/jamiethomson/archive/2005/12/09/2480.aspx

    -Jamie

     

    Tuesday, April 04, 2006 10:01 PM
  • I have read your blog entry prior to submitting this question even though it really looks like trying to push an elephant up the stairs (anyway I appreciate your efforts to just make this stuff work). I gave it a shot, but still unsuccessfully...

    I created a variable called User::Query of type string with EvaluateAsExpression = True. Then I tried to set the expression to:
    "exec ETL_GetMyData '2006-04-01', " + (DT_STR)@[User::endDate]
    (if I remember correctly, as I don't have my work pc with me right now)

    As you see, I ran into a casting issue, which I tried to solve by hardcoding the first parameter and then focusing on the last one and using a to-string casting. Still no go... Can you tell me what exactly this expression should be? Either the original expression or this, ugly one. I really appreciate your help...


    Tuesday, April 04, 2006 10:38 PM
  • The parameters used by an EXEC statement are mapped by name. The parameter names must match the names that the stored procedure, run by the EXEC statement, expects. So in this case, you should name your parameters as @startDate and @endDate, including the @ sign, instead of 0/1/2... etc.

     

    The parameter used by a select statement are mapped by order.

    Tuesday, April 04, 2006 10:53 PM
  • That sound promissing... I'll give it a shot tomorrow and let you know how it went...
    Tuesday, April 04, 2006 10:57 PM
  • OK, it worked! Thank you Ranjeeta for your help...

    Parsing the query still gives me errors and preview doesn't work, but the important thing is that I get the data over.

    Wednesday, April 05, 2006 2:09 PM
  • Yes, It is very true...

     

    Parsing it and preview threw error message within editing environement, but it WORKS FINE during runtime... Sounds weird.  This way is a lot like DTS in the old days.  And easier to use than using a variable.  But Jamie's method is also very useful when dealing with dynamic SQL commands.

     

    Thanks and I did learn something here.  Nice posting !

     

    Steve

    Thursday, August 10, 2006 8:34 PM
  • I had hoped they'd fix the parse and preview bugs in the released version, or at least in the service pack. Hopefully they'll fix them at some point.


    In the meantime, using ADO.NET driver works better, in my experience.


    Hopefully someday doing parameters in stored procedure calls from SSIS will be less like black magic, and more easy -- the environment certainly should fill in the names when appropriate.
    Friday, August 11, 2006 3:29 PM
  • Agree that using ado.net driver works better.  I have used both.  In some scenarios, OLEDB still is a good choice. 

     

    From design viewpoint, it makes no sense to make such a pain to use stored proc with parameters.  Wish Microsoft SSIS team should look into this issue and make it easy and simple to use.

     

     

    Friday, August 11, 2006 6:53 PM
  • i'm experiencing this same issue when using a parameterized query and an ole db connection manager.  preview throws an error, but everything executes just fine.

    weird.

    Saturday, September 02, 2006 7:28 AM
  • I am having the same problem.  Profiler shows that there is an additional set of single quotes around the variable bieng passed.  So I am passing in a date variable which should be '2006-09-13 07:47:07:000' but ends up looking like ''2006-09-13 07:47:07:000''.  I take the profiler string and execute it as is and it fails.  I remove the quotes and of course it works.

    Wednesday, September 13, 2006 12:02 PM
  • When I try to create parameters with name starting @, I get an error "Could not create a variable with the name "@VARIABLE" in the namespace "USER": Object name "@VARIABLE" is not valid. Name must begin with an alphabetical character or underscore "_".

    How did you manage to create variable with "@"?

     

    Wednesday, January 03, 2007 2:51 AM
  •  AnotherAlien wrote:

    When I try to create parameters with name starting @, I get an error "Could not create a variable with the name "@VARIABLE" in the namespace "USER": Object name "@VARIABLE" is not valid. Name must begin with an alphabetical character or underscore "_".

    How did you manage to create variable with "@"?



    Those were parameter names, not variable names....  You'd map a variable name to the parameter name.
    Wednesday, January 03, 2007 5:24 AM
  • Hi

    I am trying to execute a stored procedure with one input parameter,

    I am using Execute SQL Task: I tried using Expression->SqlStatementSource ->"exec s_Staging '"+ @[User::tblName] +"'"

    User::tblName is a global variable with a data type string and value as Table_Name.

    But I am not able to find out what I should wite in General->SQLStatement ????

    Please help me, I am trying this since yesterday. Any help will be useful.

    Thanks

    Paarul

    Thursday, January 11, 2007 2:49 PM
  •  

    I tried using procedure name in Execute SQL Task->General->SQLStatement -->s_Staging(this is my stored procedure)

    It picks up the expression at run time as  exec s_Staging   'Table_Name'

    But it throws an error 

    [Execute SQL Task] Error: Executing the query "exec s_Staging   'Table_Name'"
     failed with the following error: "An error occurred while extracting the result into a
     variable of type (DBTYPE_I4)". Possible failure reasons: Problems with the query,
    "ResultSet" property not set correctly,
     parameters not set correctly, or connection not established correctly. Thanks

     

    Thursday, January 11, 2007 3:38 PM
  •  Paarul wrote:

     

    I tried using procedure name in Execute SQL Task->General->SQLStatement -->s_Staging(this is my stored procedure)

    It picks up the expression at run time as  exec s_Staging   'Table_Name'

    But it throws an error 

    [Execute SQL Task] Error: Executing the query "exec s_Staging   'Table_Name'"
     failed with the following error: "An error occurred while extracting the result into a
     variable of type (DBTYPE_I4)". Possible failure reasons: Problems with the query,
    "ResultSet" property not set correctly,
     parameters not set correctly, or connection not established correctly. Thanks

     

    Does the sproc return a value? What is the datatype of that returned value?

    -Jamie

     

    Thursday, January 11, 2007 5:15 PM
  •  

    Hi Jamie

    This stored Proc does not return any value. We are using this SProc to transfer the table data from Staging to Main Database.

    It takes only param as Input i.e. the table name. This is the error I am getting

    [Execute SQL Task] Error: Executing the query "exe s_Staging  'Table_TBl'"
     failed with the following error: "Incorrect syntax near ' Table_TBl'.".
    Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly,
    or connection not established correctly.

    I am working with SSIS for the first time.

     

    Thursday, January 11, 2007 6:52 PM
  •  Paarul wrote:

     

    Hi Jamie

    This stored Proc does not return any value. We are using this SProc to transfer the table data from Staging to Main Database.

    It takes only param as Input i.e. the table name. This is the error I am getting

    [Execute SQL Task] Error: Executing the query "exe s_Staging  'Table_TBl'"
     failed with the following error: "Incorrect syntax near ' Table_TBl'.".
    Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly,
    or connection not established correctly.

    I am working with SSIS for the first time.

     

    You're using the incorrect syntax to execute a stored procedure.

    Here's a link to the correct syntax: http://msdn2.microsoft.com/en-us/library/ms188332.aspx

    Friday, January 12, 2007 7:17 AM
  •  

    I corrected it to EXEC and now its working.

    Thanks a lot for helping me.

    Friday, January 12, 2007 7:00 PM
  •  

    I am converting a DTS into SSIS, this DTS package first truncates 5 tables and than uses 5 different transformation to insert data from ORACLE source to SQL Destination. I am using Execute SQL Task to truncate the tables and than 5 Data Flow Task to carry out the tranfer of data. My question is,is it avisable to put these Data Flow Task in one Sequence Container. Will it improve the performance of SSIS? 

    Friday, January 12, 2007 7:10 PM
  •  

    I have an SSIS with fthis flow

    Execute SQL Task(Truncate table1)---on completion--->Data Flow Task(transfer data from ORA to SQL in Table1)---Success----->Execute SQL Task(Truncate table2)---on completion--->Data Flow Task(Transfer data from ORA to SQL in Table2)---On Success----->Execute SQL Task---..................

    I tried using Foreach Loop Container for this but was not able to figure out how to set the connection string,Select command for the Source and Destination.

    Any thoughts or ideas??????????

    Friday, January 12, 2007 10:48 PM
  • Hi Ranjeeta,

    I am trying to use a Stored procedure as an OLE DB Source and I manage to define the parameters but somehow the component refuses to parse AND there are no columns defined.

    I don't think usingg variables as a definition of the sql command would be easy because one of the parameters to the stored procedure will be a piece of XML that could be quite big and I don't want to parse for single quotes, etc...

    My Command is defined like this

    EXEC Maintenance.p_XmlUpload @Debug = 0, @XmlData = ?,@XmlJobDef = ?

    if you replace the "?" by decent values, it works of course.

    I looked into the DataReader source but there seems to be no parameters handling at all...

    Any idea what I might be doing wrong?


    Eric Mamet _ MCDBA, SQL Server 2005 MCTS, MCAD .Net
    Wednesday, February 02, 2011 5:16 PM