ideas for file import and process
-
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
-
If names and schedules (possibly format/columns) are different, why don't you create 2 packages with the correspondent schedules?
For the names, just set the connection string as an Expression and use something like this for the month:
"In-force for Sales and Client Services - " + (MONTH(GETDATE()) == 1 ? "January" : MONTH(GETDATE()) == 2 ? "February" : MONTH(GETDATE()) == 3 ? "March" :
MONTH(GETDATE()) == 4 ? "April" : MONTH(GETDATE()) == 5 ? "May" : MONTH(GETDATE()) == 6 ? "June" :
MONTH(GETDATE()) == 7 ? "July" : MONTH(GETDATE()) == 8 ? "August" : MONTH(GETDATE()) == 9 ? "September" :
MONTH(GETDATE()) == 10 ? "October" : MONTH(GETDATE()) == 11 ? "November" :
MONTH(GETDATE()) == 12 ? "December" : "InvalidMonth") +" "+ (DT_WSTR, 4 )YEAR(GETDATE()) + ".txt"
Or for the Quarter:
"In-force for Sales and Client Services - Q" + (DT_WSTR, 4 )DATEPART("QUARTER", GETDATE())+" "+ (DT_WSTR, 4 )YEAR(GETDATE()) + ".txt"
-
If these are flat files, you could also create stored procedures using bulk insert.
-
scottcabral (1/8/2016)
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
A simple solution would be to have two separate packages and to put the source files in separate folders. Create two discrete jobs which call the packages.
Another solution would be to have three packages: a master package which loops round all txt files and calls either the monthly or quarterly package, depending on the 'current' file's name.
In any case, I would suggest putting a foreach loop in the packages to avoid errors in those cases which there is no file to process.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
you could also use a simple ForEach Loop Container, and have the Files us a pattern -"In-force for Sales and Client Services*.txt"
inside the loop you could have something conditional to process one way or the other . I'd probably make two different import packages, and if the Current File Name had 'Quarterly' in it, "The Quarterly Package" would process it, else run it through the monthly process no matter what.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply