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 typedatetime 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...

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

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...

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.

That sound promissing... I'll give it a shot tomorrow and let you know how it went...

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.

Yes, It is very true... Parsing it and preview threw error message within editing environement, but itWORKS FINEduring 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

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.

Agree that using ado.net driver works better. I have used both. In some scenarios, OLEDB still is a good choice. From designviewpoint, 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.

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.

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.

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 "@"?

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.

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. ButI 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

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 itthrows 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 avariable 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

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 itthrows 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 avariable 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 avalue? What is the datatype of that returned value? -Jamie

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.

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 workingwith SSIS for the first time. You're using the incorrect syntax to execute a stored procedure. Here'sa link to the correct syntax: http://msdn2.microsoft.com/en-us/library/ms188332.aspx

Icorrected it toEXEC and now its working. Thanks a lot for helping me.

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?

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??????????

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