capturing errors from xp_cmdshell
-
I have a old dts package that we converted to SSIS with minimal changes. I don't know if dts was able to capture any errors previously, but SSIS is definitely not...
The purpose of the step below is to create a request file for FTP based on the data stored in a table. The first cmdshell executes a stored procedure that queries a table and creates an output file. The second cmdshell executes an ftp with the file created in the first one. No matter what I do I can't get the SSIS step to fail. I think it is because once the control passes to xp_cmdshell, SSIS considers it a success. How can I get the SSIS step to capture any error code the cmdshell receives and act on it?
declare @my_day as int
declare @my_query1 varchar(1000)
declare @my_query2 varchar(1000)
select @my_query2 = 'osql -E -h-1 -d[database] -S[server]
-Q "execute [some procedure]"
-o "c:[some results]" -w500'
exec @rc = master..xp_cmdshell @my_query2
exec master..xp_cmdshell 'c:[file created by above shell]'
-
I can't imagine why you'd be doing it like this anyway. The ways you were forced to do things in DTS aren't the ways you'd choose to do them in SSIS.
Instead of the process you've got, you should be using an oledb data source to put the data into the file, and either an execute process task or ftp task for the next step.
Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARantMinion Maintenance is FREE:
-
Thanks for the pointer. Splitting into two steps and using the SSIS tasks works much better. Now I just need to convince management to spend the time on making these types of improvements in all our jobs. I think the upgrade to SQL Server 2005 (before my time) was a real "quick and dirty".
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply