Dynamically assign connection to Flat File Source
-
I need to support loading of data from different flat file formats.
The files are dropped into a directory. Each file has the same data elements but they are from different data sources and their formats are different. For example, File1 may have Lname,Fname and File2 may have Fname,Lname etc...
I can create a connection for each of the file formats, such as FileFormat1, FileFormat2.
I have a table that correlates the filename to the fileformat
File Connection
File1 FileFormat1
File2 FileFormat2
so using the for each file loop I can the lookup in the table to know which connection to use for the current file.
How do I set the connection property of the Flat File Source operator dynamically? I would like to do this via a script task. I did this in SQL2000 DTS by referencing the FlatFileSource object and changing its attributes, but don't see how to do this in SQL2005 SSIS.
Help is appreciated,
Thanks
-
I think changing the File Source will affect everything downstream in your Data Flow also. I'm not saying that it can't be done, just that it may get very complicated.
How about using conditional logic in the workflow? You could define each separate Data Flow possibility and include it in your loop, but only execute it for the file type encountered and bypass all of the non-applicable Data Flows. I think you could use a Script Task inside the loop to set a variable to the current file type and use that in the workflow logic.
-
Ed,
Yes - I have considered that and had already started to implement that given I did not have a solution otherwise. What I don't like about that method is that I still need to create a seperate flat file connection for each format and make the control flow much more complicated than it would otherwise need to be. Say I have 30 different file formats... then there are 30 branches to the control flow and 30 different data flows to load the files.. Far more complex than it needs to be.
It would be much cleaner to just change the data sources connector to the appropriate one dymanically in the control flow, then run the same modified data flow.
Anyone out there know how to dynamically change the connector associated with a flat file data source (or any data source for that matter) or if it can or cannot be done?
Thanks
-
Gordon,
SSIS is so metadata dependant that any change to a data source that would result in a metadata change is going to cause problems. Outside of using a third party component like CozyRoc suggests, you'll have to account for each file type in your package.
There are a couple of ways to do this, first, you could create a seperate data flow for each file type. This will work just fine, but you'll end up with quite a bit of maintenance and duplication of code. One thing you may consider is creating a single data flow that works for a specific file type and create a 'converter' program or step to convert each of your file types into the file type defined in your single data flow. This would allow all of your ETL logic to live inside just one data flow, but you'd still have the task of maintaining a coversion step for each file type.
Some of the complexity of this depends on how different your files actually are. If there are a common group of fields that exist in each file that you are looking to extract out, you could consider having a step that bulk inserts the file into a new table in SQL Server, then inside your data flow, use an OLE DB source connection with the SQL source set to a variable. Construct the variable using expressions to get your common colum names and add the table name from the bulk insert.....SELECT Col1, Col2, Col3 FROM YourTable. Again, you'd keep all of your ETL logic inside one data flow step.
If you can't just grab out common columns from the files and you maybe need to massage/transform the data to get it into the right format, this approach may not work.
These are just a few ideas off the top of my head, let's discuss them more if you think one will work for you.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply