Multiple-step OLE DB operation generated errors
Hi All, When I run the following query on SSMS , it works fine. SET NOCOUNT ON declare @FileName nvarchar(1000) declare @RejCd int declare @TableName nvarchar(1000) declare @sql nvarchar(1000) declare @sqlscript nvarchar(max) set @FileName ='[dbo].[SUB_REPORT]' set @TableName ='[dbo].[SUB_REPORT]' set @RejCd = 1 set @sqlscript='SELECT * FROM [dbo].[SUB_REPORT] WHERE DATEDIFF(D,REJ_DT,GETDATE())>=3' select @sql = 'bcp "'+@sqlscript+' " queryout Y:\MyFileFolder\'+@FileName+'['+Convert (nVarChar(10) , ''+@RejCd+'' , 120 )+']'+ Convert (nVarChar(10) , GETDATE() , 120 )+'.txt -c -e -t, -T -S 1xx.18.2xx.1x1' exec master..xp_cmdshell @sql But when i run the same query with following changes in ssis package , i get an error message. I inserted this code in execute sql task. Input parameters for sqlsript is varchar type. Parameter lengh is -1. SET NOCOUNT ON declare @FileName nvarchar(1000) declare @RejCd int declare @TableName nvarchar(1000) declare @sql nvarchar(1000) declare @sqlscript nvarchar(max) set @FileName =? set @TableName =? set @RejCd = ? set @sqlscript=? select @sql = 'bcp "'+@sqlscript+' " queryout Y:\MyFileFolder\'+@FileName+'['+Convert (nVarChar(10) , ''+@RejCd+'' , 120 )+']'+ Convert (nVarChar(10) , GETDATE() , 120 )+'.txt -c -e -t, -T -S 1xx.18.2xx.1x1' exec master..xp_cmdshell @sql but then i get this error message. Can anyone help me to fix this pls? [Execute SQL Task] Error: Executing the query " with the following error: "Multiple-step OLE DB operation generated errors. . reasons: Problems with the query, "ResultSet" property not correctly, parameters not set correctly, or connection not established correctly. set Possible failure Check each OLE DB status value, if available. No work was done." shamen
January 14th, 2011 4:46pm

Hello, Instead of passing variable, build your whole query in Execute SQL Task. Step1--Select the connection manager in Execute SQL Task 2--Write any query in Statement , lets say SELECT 1 3--Go to the Expression of Execute SQL Task , then Select the Expressions and SqlStatmentSource Then build your whole query for example "SELECT * From myTAble where ID="(DT_STR,10,1252)@[User::VarID] +" ORDER BY ID" That way you don't have to worry about passing parameters. Thanks
Free Windows Admin Tool Kit Click here and download it now
January 14th, 2011 5:16pm

Thanks Aamir... I was afraid to do a big change to current SSIS package. Also I am using a BCP commpand. sql statement is inside this bcp command.so i am not sure I can use this in expression builder. I found i have set up my parameters name field incorrectly. 1,2, 3 etc. When i changed it to 0,1,2 , it works fine. This blog was helpful. http://codingassistance.blogspot.com/2007/10/execute-sql-task-ssis-with-parameters.html Thanks shamen
January 14th, 2011 6:13pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics