I'm trying to pass a SSIS Package parameter to a Execute SQL Task. Here is what I have done at Package level: ******** Package Variable * Name: MOL_DDTblName Scope: InportDASP2LocalDDNumberData Data Type: String Value: dbo.MyTableName Here is what I have done at the Execute SQL Task level: ******** Parameter Mapping * Variable Name: User::MOL_DDTblName Direction: Input Data Type: NVARCHAR Parameter Name: 0 Parameter Size: 64 Here is the SQL in the Execute SQL Task: ******** DECLARE @LocalTblName nvarchar(64) SET @LocalTblName = ? EXEC( N'TRUNCATE TABLE ' + @LocalTblName ) Here is the error I am getting: ******** Error: 0xC002F210 at Execute SQL Task TSQL code, Execute SQL Task: Executing the query " " failed with the following error: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. ******** ******** It is my understanding that the first question mark '?' in the SQL will be the first mapped Parameter. I am using MSSQL 2008 R2 Thank you in advance for any help you can give. albanello
I still have not found the answer to my original post. I have seen so many SSIS Red Fail boxes I forgot what Green looks like!!!!!!!! Here is the link I found that BEST gives a Step by Step of how to do what I am trying to do. https://www.simple-talk.com/sql/ssis/passing-variables-to-and-from-an-ssis-task/ I believe I am doing EXACTLY what the link say BUT I still am getting a fail as described in my initial post to this forum. There has to be a property that I have not set properly OR there is a syntax error in my code I am not seeing. I have tried many different things, with NO success. As near as I can tell the problem in in the "SET @LocalTblName = ?" statement. My Connection is a OLE DB connection and as I understand everything I have read the '?' is what is suppose to be used. If I hard code the table name "SET @LocalTblName = 'dbo.MyTableName' the code work as expected. Pleas help! There has to be someone on this planet that has had the same problem. Thanks in advance albanello
I have a SSIS Package that has two tasks: 1) Execute SQL Task-MyTask 2) Connected by a "Complete Constraint 3) Data Flow Task-MyTask There is one Package parameter that is used by the "Execute SQL Task" to determine if a table exists and then either TRUNCATEs or CREATEs the table and the "Data Flow Task" uses the same Package parameter for the Source and Destination table name When I Execute the Package it will do everything it is suppose to do BUT the Data Flow Task ends with a Green box and the Execute SQL Task ends with a Red box. The end result to the table, the Package is working on, is correct with the end result as expected. The error is reported as: If I Execute each task by them self each one ends with a Green box. The Red box only occurs when I Execute the Package as a whole. Basically the "Execute SQL Task" executes correctly but throws a error???????? Question: - Is there a parameter in the Package, Execute SQL Task or Data Flow Task that would cause this if not set correctly. - Any ideas as to what needs to be done to get a Green box for both tasks Any help would be appreciated albanello
!!!!!!!!HERE IS THE ROOT CAUSE/SOLUTION!!!!!!!! if there is anyone out there interested ? !!!!!!!! Some invisible character AFTER the "GO" statement in the "Execute SQL Task"!!!!!!!! I don't know what the characters were but after trying EVERYTHING I saw there was some white space after the "GO" so I placed the cursor after the "GO" and hit the delete key a bunch of times. I spotted the white space by hitting the down arrow on the key board and the cursor went down past the line the "GO" statement was on. Once I delete this white space the SSIS Package works as expected with NO Red box........Everything is a Green box. This had to be one of the most difficult problem I have ever had to find. I spent 5 day trying all different kinds of stuff and it ends up being white space. Who would have guessed. I hope this saves someone else from this headache.
