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