December 4, 2008 at 8:54 am
I need to create a job in SSIS to import a file into a SQL 2005 table. However the file name will never be the same.
for example the first file maybe 0000001_ABC_123456.txt and the next maybe 0000002_ABC_654321.txt but they will have the same type of data in them and will need to go into the same table.
What is the easiest way to do this in 2005?
December 4, 2008 at 9:50 am
Will they be in the same folder?
One of the simplest ways to deal with this is to change the name of the file or copy it to a static location and name. If the files end up in the same folder, you can loop through the files in the folder and use a file system task to move or copy the file to a static location and name, then it is easy to use the static location and name for our connection manager source.
You can dynamically set the connection manager source file name, but it gets a little more tricky to work with the package in BIDS when you do this.
December 5, 2008 at 9:02 am
Will you know or be able to generate the name of the file name? You can setup the file connection managers connection dynamically using expressions. You can also use variables to help you out if needed if you need to loop through the directory to retrieve a dataset of the objects and then shred the dataset to get and set variables that you can then use in your expression to connect to the files.
I would highly recommend heading out to Jamie Thomson's site for any type of reference for SSIS and code examples - http://blogs.conchango.com/jamiethomson/[/url]
----------------------------------------------------------------------------------------
Dan English - http://denglishbi.wordpress.com
December 5, 2008 at 10:46 am
Micheal
Yes they will all be in the same folder. The only thing that will change will be the name of the file.
December 5, 2008 at 1:48 pm
I kind of know what the file name will be. The first part will be a seq. number then ARH and the last part will be the files ID key.
December 8, 2008 at 2:19 pm
Checking out Jamie's site is a must if you want to work on SSIS, lots of good stuff over there.
As for your requirement: I would use a For Each Loop Container, and let that container loop the files in the directory to import. You can then let each filename that matches your requirements (see the Collection tab of the Foreach Loop editor) be put in a variable, and use that variable in your flatfile connection manager.
In the FLC you put a dataflow to import the flatfile to a database table, and a task to delete and/or move the file from the sourcefile if it should only be imported once.
Give it a try and see where it brings you!
Peter Rijs
BI Consultant, The Netherlands
December 8, 2008 at 2:24 pm
yep, I am working on doing just that right now. Thanks all for your answers I think this is going to do what I need. I will let you know.
December 8, 2008 at 2:26 pm
Yeah, like I stated before if you haven't searched his site, Jamie knows SSIS inside out.
Here are a couple of links that might help you out in setting up your package, but he has plenty more where these came from:
SSIS: Expressions on ForEach enumerators
SSIS Nugget: Execute SQL Task into an object variable - Shred it with a Foreach loop
Use variables and set the connection manager connection to an expression based on your variable name retrieved from the recordset of file names.
----------------------------------------------------------------------------------------
Dan English - http://denglishbi.wordpress.com
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply