February 6, 2013 at 4:13 pm
I am downloading files once a week and put into a network folder. I need to distribute files to different folders but 2 of them will need to be imported into a database. The files each time I download them will have different dates on them but the last part of the file names will distinguish what is in them.
In regards to the 2 files I need to import to a table on the server, this weeks files are named...
20130202_CCHReturns.txt
20130202_SLVReturns.txt
Last weeks files were named...
20130126_CCHReturns.txt
20130126_SLVReturns.txt
So basically I need to import *CCHReturns.txt into one table and *SLVReturns into another table so that I can run some queries on the data. I can manually rename these files to exclude the date and then run my Bulk Insert script to do that. I'm trying to get this done through SSIS though. I've come across countless forum and blog posts about how to take a file such as, for example, text.txt and then rename it with the current date like so 20130206text.txt. But I can't figure out how to remove the date from a file so that an SSIS process can look for a static file name to import.
Either I remove the date and to let SSIS work on a static file name or I need to figure out how to import *SLVReturns.txt to one table and *CCHReturns.txt to another table. I can't find anything on that either. In fact, I found a post (from 2007) that talked about doing that and one of the people said something along the lines of 'good luck with trying to do that', and there was no solution posted.
Can I do an import using a wild card? If not, how do I chop off the date (rename the file) so that it's a static file name so that I can run a Bulk Insert script on it?
February 6, 2013 at 4:26 pm
Never mind. I found this post and tweaked a few of my settings on my SSIS package and I was able to rename the file to a static name for me to import.
(instead of move, I chose Rename)
If there is a way to import files using a wildcard character without having to rename the files (using a static file name), I'd sure like to know.
February 6, 2013 at 8:20 pm
J M-314995 (2/6/2013)
If there is a way to import files using a wildcard character without having to rename the files (using a static file name), I'd sure like to know.
You can use a Foreach Loop Container to dot that. The file mask will be *FileSuffix.txt and then inside your Container a Data Flow can import the file, the name of which will be stored on a Variable by the Container.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
February 7, 2013 at 1:36 am
opc.three (2/6/2013)
J M-314995 (2/6/2013)
If there is a way to import files using a wildcard character without having to rename the files (using a static file name), I'd sure like to know.You can use a Foreach Loop Container to dot that. The file mask will be *FileSuffix.txt and then inside your Container a Data Flow can import the file, the name of which will be stored on a Variable by the Container.
+1. this is the way to go.
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply