How to monitor folder with incoming files and import it to database

• halifaxdal

  SSCoach

  Points: 19741

  More actions

  May 16, 2011 at 8:56 am

  #251124

  Hi folks,

  I have different files from different people in different locations, they will periodically send in some data in a fixed format. Those data files need to be processed and their data will be extracted and imported into a database on the server (I am not sure if the file server is the same as the database server), the files will arrive in email, or maybe a FTP put?

  Now, how do I do this, and schedule it as a task?

  Thank you very much.

• Welsh Corgi

  SSC Guru

  Points: 116520

  More actions

  May 16, 2011 at 10:22 am

  #1325258

  This should be in the SSIS Forum

  I have only provided you with a very high level description of the process.

  If you get the file via FTP you can use the FTP Task.

  If you get the file via email, you will use the script to read incoming email and save the attachments to a folder.

  Then you can use the File System Object (FSO) process the incoming file(s). Create variables so that you can save the file name & path for later use.

  You would create a flat file connection as the Source and SQL Server.

  As you process each file you will dynamically assign the properties of the connection by using the variables that you created in the Script task.

  You will want to move the file to an archive folder and you will probably want to add code to delete files after so many days old.

  You can then deploy the package and schedule to job to run.

  For better, quicker answers on T-SQL questions, click on the following...
  http://www.sqlservercentral.com/articles/Best+Practices/61537/

  For better answers on performance questions, click on the following...
  http://www.sqlservercentral.com/articles/SQLServerCentral/66909/