SQL Svr 2008 import multiple large fixed row length text files into existing table

Question

I need to import approximately 100 large text files into a SQL Server 2008 table.  The table currenytly exists.  The files are fixed row length without delineation.

I am able to take a single file, open it in excel to create a csv file, place all the field delimiters (900 characters fixed row length, many fields), save as a csv and then import the csv into the table.  However, this is tedious and requires setting the delimiters each time for each csv file. 

Is there a way to import fixed row text files into a SQL Server 2008 table without having to deliniate each text file?

Answer 1

If they are all the same format then use a foreach loop container to loop through the files - assign filename to variable - expression to set file name in data connection.  Then you put your dataflow task inside the container.  Pretty standard construct

Answer 2

Here is a good example

http://www.sqlshare.com/looping-through-and-loading-files-with-ssis_34.aspx

Answer 3

I viewed your video.  While this is good information for a column delineated flat file, it does not answer the special condition of having fixed length row text files where there is no column delineation.  Column 1 is field 1, column 2 - column 15 is field 2, etc.  All field lengths are different, each line is 900 characters long.  How can I use SSIS to load fixed row length data into the table?  Especially note that there are 100 files of over hundreds of thousands of rows.

I see the assistance of loading csv or column delineated files via the flow loop method and could use this if need be after converting all the fixed row length files to csv files - which requires tedious delineation.

---

Julia Mary White

Answer 4

Process is the same for fixed length files - just setup the file connection as such.

Answer 5

Sorry, old jeep.  I don't know how to do that.  That is why I'm seeking help.  I can use the import wizard to bring in a csv flat file to the table, but I don't know how to bring in a fixed row length flat file.  I'm experienced in using SQL in Oracle and Access, but am new to SQL Server 2008.  Can you help me?

---

Julia Mary White

Answer 6

You need to forget about the import wizard and open up BIDS to design your own SSIS package.  You will follow the directions in that video - except that when you define the flatfile connection you will be definining a fixed width format rather than a csv format.   It sounds like you might want to buy an SSIS book and get up to speed on the basics

Answer 7

You can use the Import/Export Wizard, but instead of picking (leaving the default) Format of "Delimited", select "Fixed Width" instead.  When you do that, the next page in the wizard allows you to define the columns.

As the last step in the wizard, you can save your instructions as a DTSX file instead of running it.  Save it, then open it in BIDS and do the Foreach alterations you see in the video.

---

Talk to me now on