September 11, 2008 at 6:46 am
I download about a dozen files biweekly. They all come with have a soft-copy layout definition file. In Access, I had a query that took those Master File definitions, and returned a result table that could be pasted into the definition for the import file process. Once defined, I could simply reference the saved import definition and run the import process from VBA code.
Now that I am trying to migrate to SSIS, I assumed that the programming interface to the SSIS tasks would allow me to do the same thing, but a (perhaps too) quick review leads me to believe that all the programming interface can do is inspect, but not change or define, the tasks.
I sure don't want to go though by hand (looks to be about 4 clicks and edits per column) and define all those flat files, some of which have 100+ columns.
There must be a better way -- what am I missing?
September 11, 2008 at 10:06 am
Maybe I am trying to find an automated approach too soon. Hell, I have been totaly unable to even do it by hand with a Flat File Data Source and Connection manager.
(I'd summarize my struggle with SSIS so far, but I think Steve discourages profanity. Geez, what a kludge!)
September 14, 2008 at 5:44 pm
SSIS and utilising how to load and export dynamically isn't the most intuitive tool. I would suggest the two things you should start looking into are using variables (in the SSIS menu) and Expressions in the properties of the connections.
If you want to loop through a number of files that are in a folder then using a ForEach loop, creating a variable to hold the File name as it loops through each file. You will then need to add a source and destination connection. If you are loading into the same table from multiple source files in the property of the source connection (on the right hand side of the screen) go to the Expression property and click on the parentesis to open the expression window. I think then selecting connectionstring and setting it to the same variable you added in the ForEach loop (it should be something like @User::FileName and will appear in the drop down list under variables).
I hope this helps. If you search on ForEach loops in SSIS, expressions, and variables i'm sure you'll find what you are after. Other than that i'd suggest the Wrox book Professional SQL Server 2005 Integration Services if you want to learn more about SSIS.
If you give this a try and let me know how you go on i'll see what more i can suggest if this doesn't give you a solution,
Matt
September 16, 2008 at 4:53 am
I have used (after a struggle) ForEach loops, variables,and expressions, with some success for things like dynamic Excel workbook and worksheet names. But what I need now is a dynamic way to define the input file format for fixed field flat files(column names, sizes, types, etc., particularly for files with 100+ fields.)
I see no solution so far, except for direct editing of the .dtsx html. My current thought is to create a query fed by a table of field specifications (I have several soft copy formats from which to choose) that generates the .dtsx html Flat File Column properties needed, and as a first test, paste them into a prototype .dtsx using XML Notepad 2007 (a very nice utility.) If that succeeds, the next step is to try to do a dynamic editing of the .dtsx html, perhaps using some sort of insert query or linq commands.
Might that have some promise? Are there any better alternatives? Any suggestions?
September 18, 2008 at 5:24 am
Did you ever try to solve the problem with a Stored Procedure ?
September 18, 2008 at 5:29 am
ulile (9/18/2008)
Did you ever try to solve the problem with a Stored Procedure ?
Do you mean skip SSIS completely? That might be attractive.
September 18, 2008 at 5:36 am
Not complete. Create the CREATE Table... and the import.sql's with the Stored Procedures and run them in SSIS
September 18, 2008 at 5:49 am
Thanks for the suggestion, ulile.
I may be suffering from "If your only tool is a hammer..." blinders, because I saw the problem as "How do I get SSIS to..." rather than "How do I import flat file data to my DataBase."
If I can figure out a way to create a procedure that is driven by tables of flat file paths, layouts, and destination tables/columns, I don't think I would bother with SSIS at all.
I'll have to ponder that...
September 18, 2008 at 8:27 am
SSIS is extremely metadata driven.
A lot of guys hit the pitfall where in DTS they could have a single source, single destination, single transformation, and then through the use of an Active Script component set things to change dynamically, including the actual transformation pipes. In SSIS, forget about this notion altogether, many have tried without success, there needs to be a formal handshake between source and destination which is compiled.
What you may consider is to have a source connection per layout though. Assuming you have 5 or 6 layouts for 50 or 60 files, it means you will have to create the 5 or 6 required source connections. From here the SSIS becomes easy. During your for each file enumeration, you can have conditional flows which will allow you to execute a certain task if a certain value is true (i.e. if file 1 is layout 1, then execute dataflow 1 etc...).
A bit top-heavy, but workeable...
~PD
September 18, 2008 at 8:35 am
But the problem is not (yet) multiple sources and multiple destinations. Take the simple case of one input file and one output destination. When the input file (fixed field length, no headers) has 100 or 200 columns, how do you define it? One column at a time typing in all the field names, sizes, and data types by hand?
September 18, 2008 at 8:59 am
Why not try usin the import/export wizard to do all the grunt work for you?
September 18, 2008 at 9:05 am
My bad...
Didnt read the no column headers fixed file bit...
I know in SQL 2000 you could use a layout file with the BCP utility. Personally never played with it, but pretty confident that this would be a feature in 2005 as well
Apologies for misreading earlier
~PD
September 18, 2008 at 9:22 am
Pduplessis - The duck just dropped down (or don't you go back that far)!
I suspect BCP with a Format file is exactly what I need & was missing.
THANKS!!
September 18, 2008 at 9:25 am
Only a pleasure man.
Post with a solution if you can, I havent used it but would like to know the semantics
September 25, 2008 at 6:21 am
Not yet a solution, but a plan:
Your (pduplessis) BCP suggestion was a very good one; BCP will create and reference format files (either .txt or .xml) which would help in many cases. Unfortunately, since my fixed field length input files are imported into varchar columns, it is not as simple as trying to ask BCP to generate the format file from the original destination tables.
My current plan (pending a performance trial) is to use the Jet ODBC driver to input the files using appropriate Schema.ini definitions (which should be easy to programmatically generate--not that the BCP format would be any harder.)
Two things led me in that direction:
First: An excellent article from Steve Kass at
http://users.drew.edu/skass/sql/TextDriver.htm
which shows how to create a Linked Server that represents a directory of .txt files. Once defined, any/all .txt files in that directory are available (Linked Servers/YourDirName/Catalogs/default/tables) and can be addressed in the normal four-part naming convention.
Second: The realization that a Schema.ini file in that directory can contain multiple [filename] entries (rarely mentioned), each followed by the specifications for the individual (different) file formats.
So the plan (unless I trip over unforseen obstacles) is:
1.Build a script/query to generate the Schema.ini file describing all my input file formats in the linked directory.
2. Download all my input .txt files to that same directory.
3. Run one script that, for each input file, truncates the old target and appends the data from the new linked .txt file.
4. Rename (timestamp) the input .txt file and move it to an archive directory.
The best part is, no SSIS at all!
(Sorry Jenny, it dosen't work on a directory of .xls files -- I sort of knew it wouldn't, but it sounded so good that I had to try anyway!)
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply