Exporting SQL Server data (fixed length) to an SFTP server on a daily basis

Hey guys, I do most of my work in MySQL, but have recently been tasked to do some database work on SQL Server. I realize the databases work in a very similar fashion.

My task is sim开发者_C百科ple, export data from a SQL Server 2008 database as a fixed length file to a secure ftp server on a daily basis.

If I was using php I would probably just write a php script that does this with a cron job. But in this case, I dont have access to any of that. From what I read it seems like I can use SQL Server 2008 itself to do this for me.

Can anyone give any pointers and let me know if this is an easy task. I don't want to take on a job that is beyond my skill set ;)

Every SQL Server installation (including Express editions) comes with little tool called bcp.exe (Bulk Copy utility). It supports two formats, native and character. You are probably interested in the character format, see Using Character Format to Import or Export Data. Adding a comma , field terminator will produce a CSV file:

bcp database.dbo.table out datafile.csv -c -t, -T

If you want a fixed length output, then probably the best solution is to specify a format file. See Specifying Data Formats for Compatibility by Using bcp.

Once you have the file, you can copy it to your SFTP server. The equivalent of cron on Windows platforms is the Scheduler, see The Task Scheduler.

There are more fancier solutions, like using SSIS as Eugene suggested, which would give you more power and control. SSIS comes with SQL Server (but not with Express Edition) and on non-Express Editions you have the SQL Server Agent to help you schedule tasks.

If you can use SQL Server Integration Services, take a look at SFTP task in our BizCrypto product. With integration services you can build a file and transfer it to the remote SFTP server.