SSIS PACKAGE DATA DUMP FROM SQL VIEW TO CSV AND PUT IT ON A FTP SERVER

• ntreelevel

  Say Hey Kid

  Points: 663

  More actions

  September 11, 2013 at 12:54 pm

  #282940

  Hi,

  I have a scenario where I need to create a SSIS Package and make it run automatically every night.

  What the package should do is that it should pull data from a sql view and dump it into a CSV file on the local server. The CSV file should have a date stamp appended to the file name.

  Now I have to dump the latest csv file from the local path on to the FTP Server.

  Can anyone give me step wise as to how this can be achieved?

  Example:

  I have csv files say

  data_2103_09_08

  data_2013_09_09

  data_2013_09_10

  data_2013_09_11

  on the local path c:\timesheets

  when the package is run on the night of 2013_09_11 the csv file data_2013_09_11 should be picked and be placed on the FTP Server.

  Thank You all in Advance

• sturner

  SSC-Insane

  Points: 22411

  More actions

  September 11, 2013 at 1:24 pm

  #1649471

  I basically do the same thing except multiple times per day. I have a .net program that pulls the data via a stored procedure and builds the pipe delimited data file. Then a second program pushes it (and any other ones that need to be) over to the SFTP server.

  It generates a new file every time it runs but it is possible the FTP server (or network) may not be ready at various times so the program needs to be smart & robust enough to handle that and resend any backed up files when the FTP server is on-line. That sort of logic is not easily handled with SSIS, but easy to do in .NET. I'd still recommend doing it in a similar way in your case for some or all of the same reasons.

  The probability of survival is inversely proportional to the angle of arrival.

• ntreelevel

  Say Hey Kid

  Points: 663

  More actions

  September 12, 2013 at 8:56 am

  #1649763

  THank You

  But I do not want to use .net since I am not a .net person.I want to use only sql server and ssis package.

  Can anyone help me out in acheiving this.

  Thanks in advance