Ok. I've scoured the universe searching for an answer. I've tried just about everything but can't get this to work right. I apologize for the up & coming descriptive email but it will be necessary to show you my work. I'm running both "Execute SQL Task" in OLE DB. Task #1 goes to Server-A (for example) and Task #2 goes to Server-B (for example). I have a select statement that will ALWAYS return 1 record. 5 values. This works fine in Task #1. I've set my variables on the left to a SSIS Package scope. Task #1: Connection: Server-A (for example) ResultSet: Single Row (tried all options in here by the way) ConnectionType: OLE DB SQLSourceType: Direct Input BypassPrepare: True ResultSet: Task #1 completes successfully. On Success go to Task #2: Connection: Server-B (for example) ResultSet: None ConnectionType: OLE DB SQLSourceType: Direct Input SQLStatement: (this is a stored procedure) Exec AddScheduledInfo ?, ?, ?, ?,? Parameter Mapping: (NOTE: I started with 0, 1, 2, 3, 4 on the "Paramter Name" but even if I run a simple "Select ?, ?, ?, ?, ?" I got errors with the numbers. When I changed it to the current values of @variablename, the "Select...." statement worked.) Now here's the kicker. If I hard-code values into my stored proc, it runs great. No errors. Green across the board!! But when I replace the hard-coded values with the ? I get the following error: "[Execute SQL Task] Error: Executing the query "Exec AddScheduledInfo ?, ?, ?, ?, ?" failed with the following error: "The statement has been terminated.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly." Connection is good because it works fine with hard-coded values, so I eliminate that "problem". "ResultSet property..." issue, I don't buy. I don't want a result set. But for troubleshooting purposes I tried them all. I get a different type of error (still) when I try any of them. This leads me to the "parameters not set correctly" problem. I'm at a loss of what to do at this point. I've been off 'n on this issue for 2 days now & feel I'm just forgetting 1 simple thing. I don't know what that 1 thing is though. So finally reaching out for some ideas. Thanks ahead of time & hope to get this resolved.

Someone told me to change the Paramater names to 0, 1, 2, 3, 4 (which I stated I did already "(NOTE: I started with 0, 1, 2, 3, 4 on the "Paramter Name" ..." and the result of me doing that is: "[Execute SQL Task] Error: Executing the query "Exec AddScheduledInfo ?, ?, ?, ?, ? " failed with the following error: "The statement has been terminated.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly."

Hello Cowski, For OLE DB Execute SQL task type the Parametername has to be specified by position of the parameter. Please take a look at the following link. http://phe1129.wordpress.com/2008/06/27/execute-sql-task-and-parameter-mapping/Hima, To help the community better identify relevant thread, please don't forget to mark as answer or vote as helpful if and answer helps.

Hi, SSIS parameter and variable handling is quite confusing. I had similar problems and here's how I solved it. All SQL stored procedures come with an implicit parameter called Return value that has to be taken into consideration, even if it's not visible in your SP code. Here's how I invoke a stored procedure of mine from an SSIS SQL Task. EXEC ? = SampleDB.dbo.CarSalesByProvince ?, ? OUTPUT This stored procedure has an input parameter (province) shown as [?] an output parameter (sold units) shown as [? OUTPUT] and the implicit return value to the left of the [=] sign. I configured ResultSet property to None (because my stored procedure returns a scalar) Now, here's what I configured on the Parameter Mapping tab Variable Name = User::ReturnValue (an Int variable I defined at the package level) Direction = Return Value Data Type = Long Parameter Name = 0 Parameter Size = -1 Variable Name = User::Province (my input parameter, a variable defined at the package level as well) Direction = Input Data Type = Long Parameter Name = 1 Parameter Size = -1 Variable Name = User::SoldUnits (my output parameter, a variable defined at the package level as well) Direction = Output Data Type = Long Parameter Name = 2 Parameter Size = -1 After executing the SP, SSIS will pick up my sales numbers on the User::SoldUnits variables.Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

Sebastian, Thanks for the detailed reply. Here are the screenshots of what I set up per your suggestion: And with running this I get the same error: Error: String or binary data would be truncated. [Execute SQL Task] Error: Executing the query "Exec ?= AddScheduledInfo ?, ?, ?, ?, ? " failed with the following error: "The statement has been terminated.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. I must add though, the top error "...data would be truncated." is something I just noticed. My stored proc though, I set my varchars in there as (MAX). Still plugging away at it!

Hi, As far as I know, SSIS strings are limited to 8000 characters. Try defining your strings as VARCHAR(8000) instead. Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

Set those to 8000 instead of MAX. Same 2 errors. What gets me is if I put a breakpoint on task #2, using "OnPreExucutingEvent", check out the local window, all my USER::variables are there, pretty as can be, ready to be used.

Yes, but did you modify your stored procedure code as well? (to return a VARCHAR(8000) instead of VARCHAR(MAX)) Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

Hello Cowski, Please set the parameter size to the right values. Another thing I would like to suggest is, if you are not going to use the variable user::RetrunValue, then try to execute the proc with out the return value (Exec AddScheduledInfo ?, ?, ?, ?, ?) and change the parameter mapping accordingly. Hima, To help the community better identify relevant thread, please don't forget to mark as answer or vote as helpful if and answer helps.

Yes, but did you modify your stored procedure code as well? (to return a VARCHAR(8000) instead of VARCHAR(MAX)) Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu Yes, I changed the VarChar(Max) to VarChar(8000) in my stored procedure.. Same 2 errors.

Hi, May you, please, paste the header of your stored procedure (e.g. : parameter definition) and the parameter mapping of your SSIS Execute SQL Task?Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

Hi, May you, please, paste the header of your stored procedure (e.g. : parameter definition) and the parameter mapping of your SSIS Execute SQL Task? Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu Sebastian (and all), I do believe I've found the culprit albeit I have not fixed it. It is my stored procedure. Here is what I have found out. I took all my code out of my stored procedure (you'll see it below) which checks for the existence of a record and if it's there, update else insert. I took that code out, replaced it with just PRINT statements but left the header. I renamed it by adding "_test" at the end. I went back to my SSIS package, added the "_test" to the end of my stored procedure in Task #2, changed nothing else & ran it. I got 2 green boxes!!! So...my stored procedure is nothing fancy at all. See below for what it is. USE [VALID_DATABASE_NAME_IS_HERE] GO /****** Object: StoredProcedure [dbo].[AddScheduledInfo] Script Date: 10/31/2012 13:46:52 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[AddScheduledInfo] (@ServerName VARCHAR(8000), @JobName VARCHAR(8000), @StartTime VARCHAR(8000), @JobRunDuration VARCHAR(8000), @LastRunStatus VARCHAR (8000)) AS IF EXISTS(SELECT ServerName FROM dbo.ScheduledJobs WHERE ServerName = @Servername) --Update BEGIN UPDATE dbo.ScheduledJobs SET [ServerName] = @ServerName, [JobName] = @JobName, [SSStartTime] = @StartTime, [SSAvgDuration] = @JobRunDuration, [LastRunStatus] = @LastRunStatus WHERE ServerName = @ServerName END ELSE INSERT INTO Database_Information_Warehouse.dbo.ScheduledJobs (ServerName, JobName, SSStartTime, SSAvgDuration, LastRunStatus) VALUES (@ServerName, @JobName, @StartTime, @JobRunDuration, @LastRunStatus) --================================================================================ The really funny thing, as I stated earlier, my SSIS package ran with hard-coded values with no problems. That's why I didn't go too deep into my stored procedure. If I could get some insight on this on how to fix this I would appreciate it. Thanks!

Hi, I suspect you should refresh your stored procedure cache Start by identifying its handle SELECT plan_handle, st.text FROM sys.dm_exec_cached_plans CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st WHERE text LIKE N'%AddScheduledInfo%'; It will return an hexadecimal value Then remove it from cache DBCC FREEPROCCACHE (your hexadecimal value with no apostrophes or double quotes http://msdn.microsoft.com/en-us/library/ms174283(v=sql.105).aspx Then run again your SSIS packageSebastian Sajaroff Senior DBA Pharmacies Jean Coutu

Hi, I suspect you should refresh your stored procedure cache Start by identifying its handle SELECT plan_handle, st.text FROM sys.dm_exec_cached_plans CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st WHERE text LIKE N'%AddScheduledInfo%'; It will return an hexadecimal value Then remove it from cache DBCC FREEPROCCACHE (your hexadecimal value with no apostrophes or double quotes http://msdn.microsoft.com/en-us/library/ms174283(v=sql.105).aspx Then run again your SSIS package Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu There are 28 of these. I'm guessing I'll need to run the DBCC command on all 28?

You have 2 options : a. Run it on just one of the faulty stored procedures, then test SSIS to confirm if it works (or not). b. Run DBCC FREEPROCCACHE with no parameters, it will refresh all caches but at the cost of slowing down performance for a while (until all caches are regenerated) If I were you, I'd go with #a Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

I took the hard route since I'm desperate & ran it on all 28. Then reran my SSIS. Same results. I firmly believe it's my SP.

Try the following to refresh your stored procedures metadata definitions, then run your SSIS package again USE yourdatabase EXEC sp_refreshsqlmodule 'dbo.AddScheduledInfo'Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

Because the stored procedure parameters are being used to update or insert in to the table dbo.schedulejobs, please make sure that the length of the values being passed to the stored procedures is not longer than the width of the corresponding columns in dbo.ScheduledJobs. Hima, To help the community better identify relevant thread, please don't forget to mark as answer or vote as helpful if and answer helps.

I am quite ashamed I overlooked one small detail. And I would like to think, as IT people, we have all done this at one time or another. We sometimes overthink the solution when it is actually something very, very simple. I found out it wasn't my SSIS package, nor was it my stored procedure. It was my database. One of the fields in my database was designed too small. I believe it was the JOBNAME field. I lengthened everything to 100 for testing purposes and the SSIS package gave me 2 green boxes!!! I want to thank EVERYONE, especially Sebastian Sajaroff, for pitching in on this issue. If anything, I learned again to not overlook the simplistic stuff.

I'm glad to know that your package is running OK now. Thanks!Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu