No SSIS? No Problem. Create a csv File Using SQL

Let me start by saying I never knew how much I would miss Sql Server Integration Services until I was asked to build a simple CSV export and FTP transfer package without it.  Something like this would take a matter of minutes to build in SSIS.  Unfortunately this is not an option for me at this time so I was forced to try to find another way to accomplish my task.  After a bit of Googling I stumbled across a great article over at simple-talk.com that talks about using something called BCP along with a stored procedure (or table/view) to export data into a CSV file.

Here is the sample SQL:


declare @sql varchar(8000)
select @sql = 'bcp master..sysobjects out c:\bcp\sysobjects.txt -c -t, -T -S'+ @@servername
exec master..xp_cmdshell @sql

This technique worked perfectly for me in that it allows me to build a stored procedure to export the specific data I want and then automate file creation by simply building a small executable file to execute this command.

That’s a Sticky Wicket!

Apparently I missed my calling and should be betting on Cricket…99.6 percentile!  Nice!  I’m sure this won’t last but figured I’d document the moment.