I'm trying to execute a SQL task in SSIS. This loads the query from the script file and executes it. Contents of the script - Create PROCEDURE sp.MySPROC @p1 int, @p2 int AS INSERT INTO myTable (value1, value2) VALUES (@p1, @p2, blah), (@p1, @p2, blah), etc GO EXECUTE sp.MySPROC ?, ? Now, when I try to map the variables defined in SSIS to these parameters, my task returns an error - "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done." However, if I hardcode the values in my EXEC command, the task runs just fine. I tried using the profiler but looks like the task errors out even before the query gets to the db. Any ideas on how I can resolve this? I guess I could have 3 separate task to create a sproc, execute it and drop it ...but ideally, I only want to have one sql task for the entire operation. Is this possible?

I don't think that's possible - the parser isn't that robust. But then, I don't really understand why you're defining a sproc inside a batch that uses the sproc, then drops it right away... ? Why don't you do away with the sproc entirely, and just use the statement itself? Talk to me now on

AS Todd explained, you can achieve this by using simple Query. Your query inside execute sql task will be like this INSERT INTO dbo.TABLE VAlues(?,?) , then you will go to mapping tab and map these parameters to some variables you have. This all will be done in one Execute SQL task and you don't have to Create, Execute and Drop SP. Thanks http://sqlage.blogspot.com/

Yup, I understand that I could have it all as a simple query but I created a sproc to solve the problem of mapping the same variable to different parameters at the same time. For example, I would need to map the same variable for every row (value row) that I'm trying to insert into the table and that just seems like a lot of grunt work and error prone when I have many rows to insert. A sproc seemed like a good solution since I will only have to map the parameters once to the exec sproc and it does all the internal value mappings. So I'm hearing that the parser isn't robust enough to do this for this operation?

You don't need to use a SProc to do that. Doing this will work: DECLARE @p1 AS INT DECLARE @p2 AS INT SET @p1 = ? SET @p2 = ? INSERT INTO myTable (value1, value2) VALUES (@p1, @p2, blah), (@p1, @p2, blah), etc Talk to me now on

Hmm, you are right. I was thrown off when I tried to build the sample query from within the Execute SQL task windows and it told me that the DECLARE statement was not supported in the query. But, this does not work when I have multiple batches in my SQL script which need to create a view in the same task. I'm resorting to just having 2 tasks, 1 to create the sproc and the other to execute the sproc with the parameters mapped. I guess this is the best I can do. Thanks.