Ideas for file import and process

SSIS and Import/Export
1

Hi,

I have a process that will import a txt file each month. There will be 2 separate txt files. One is a monthly file that will be delivered to a share between the 7th - 10th day of each month. There will also be a quarterly txt file that will be delivered on each month following each quarter end month (Q1 - delivered in April, Q2 - delivered in July, Q3 - delivered in October and Q4 - delivered in January of new year). All of the quarterly files will be delivered around the 21st of each month listed above.

the Monthly file is named 'In-force for Sales and Client Services - monthname 2015.txt'
the Quarterly file is named 'In-force for Sales and Client Services - Quarter 2015.txt'

what i need to do is automate this process in SSIS. I need to have the package check for file existence and if it is monthly file, go down one path, if it is Quarterly file go down another path. The package should be able to know what monthly or Quarterly file is coming next based on date. Once the file is found, it should follow correct path based on type of file. Once completed, file should be removed and archived and package should be ready to process next file when it gets dropped.

I've imported files using SSIS, but never did anything with this type of flexibility. Usually the file is always named the same thing. This process is a little different because the file names will be different based on the month and/or quarter and I will need to know wether it is a monthly or quarterly file based on file name.

thanks
Scott

2

We used a Powershell listener job that would run constantly. Once it finds a file in the folder, it then kicks off the job that execute the SSIS package.

We used a different listener job for each package that needed it, but you could instead have the listener checking for multiple files and knowing which job to start when a certain file is found.

3

You can do it in SSIS with a For Each File loop.

Just run the package at intervals to meet your biz requirements