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...
Using stored procedure as OLE DB source with parameters from package variables
Raul Serrano
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.
Daniel Germanus
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
Orellang
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.
Bob Y
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.
briartoys
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.
Peter Giffin
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.
Christiawan
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 "@"
lamp
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
iifu
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
J Smith
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...
MaksTretyak
Those were parameter names, not variable names.... You'd map a variable name to the parameter name.
Patricia
Shashi Shekhar
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
LaserCorey
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.