-1

I am using SQL Server 2012. I need to create an FTP Task that will pick up an Excel file (or it can be a csv file if needed) and upload it on an FTP site on a daily basis. The basic idea here is to automate the whole process from the creation of the Excel file to uploading it on the FTP site.

The content of that Excel file will be the result of a T-SQL query that will run on a daily basis and overwrite the existing one. My idea is to use SQL Server Agent Job to do this part. However, I am having a really hard time figuring out how to write my T-SQL codes so that it outputs the results into an Excel file.

The creation of the FTP Task is not really an issue right now as I think I can handle that by creating a SSIS package.

For simplicity, let us assume, my SQl query stands as follows:

Select * 
From TravelAgency

and let us also assume that I want to save that file as 'myfile.xlsx' in the 'C:\Test' folder.

Can it be done in a T-SQL query?

user3115933
  • 4,303
  • 15
  • 54
  • 94

1 Answers1

0

Here example (1251 is russian encoding, you do not need it)

set @cmd ='bcp "' + @query + '" queryout "' + @SourceFileName + '" -T -c -C 1251' 
--print @cmd
exec @ret = master..xp_cmdshell @cmd
if @ret <> 0 throw 50000, 'shell error while saving file', 1
Mikhail Lobanov
  • 2,976
  • 9
  • 24