Importing Tab Delimited Flat Files Using SSIS

Question

Importing Tab Delimited Flat Files Using SSIS

kscott-851323

Right there with Babe

Points: 759
More actions
July 22, 2010 at 7:46 am

#387408

Hello,
I have multiple SSIS packages importing data from flat files that are tab delimited. The issue I am having is the source files column position can change without my knowledge. When the flat file changes, data is importing into the incorrect columns. For example, the first file can have Name as the first column in the file. The next time the client sends the file, the column Name could appear as the third column in the file.
Can someone tell me if there is a way to get the package to recognize the Column Names instead of the column position? I have tried both SSIS 2005 and SSIS 2008 and both versions are importing data based on the column position.
Thanks in advance.

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

sturner SSC-Insane Points: 22411 More actions · Answer 1

I would bulk insert this file into a stage table first then do the insert into the production table by specifying the column names in the order you want them to be.

I

The probability of survival is inversely proportional to the angle of arrival.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 2

sturner (7/22/2010)
I would bulk insert this file into a stage table first then do the insert into the production table by specifying the column names in the order you want them to be.
I

I'm thinking that might still be a problem if the order of the columns changed in the file without warning. If you have an automated solution for this problem, I'd love to see it in code.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 3

kscott-851323 (7/22/2010)
Hello,
I have multiple SSIS packages importing data from flat files that are tab delimited. The issue I am having is the source files column position can change without my knowledge. When the flat file changes, data is importing into the incorrect columns. For example, the first file can have Name as the first column in the file. The next time the client sends the file, the column Name could appear as the third column in the file.
Can someone tell me if there is a way to get the package to recognize the Column Names instead of the column position? I have tried both SSIS 2005 and SSIS 2008 and both versions are importing data based on the column position.
Thanks in advance.

I have no clue as to how to do this in SSIS. I do believe that this can be accomplished with a bit of T-SQL, though. So, my question is, does it have to be done in SSIS or can we use some T-SQL to pull this off?

{edit} Also, I need to know... do the number of columns ever change?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

kscott-851323 Right there with Babe Points: 759 More actions · Answer 4

Hello Jeff,

Unfortunately, I have do not have much control on what the client sends in the files, so the number of columns can change constantly. The Client can either add or remove columns before sending the files.

Many of these packages were updated from DTS, which was never an issue since the mapping was based on the column name and not the position.

At this point, I believe the next steps is to look and the BCP utility.

sturner SSC-Insane Points: 22411 More actions · Answer 5

My suggestion was assuming you can write a C# program to bulk insert the file into the stage table first.

It could determine the column order of the file then create the stage table accordingly and bulk insert into it. Thene the actual load procedure would be called that does the insert specifying the column names and the insert order.

The probability of survival is inversely proportional to the angle of arrival.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 6

kscott-851323 (7/22/2010)
Hello Jeff,
Unfortunately, I have do not have much control on what the client sends in the files, so the number of columns can change constantly. The Client can either add or remove columns before sending the files.
Many of these packages were updated from DTS, which was never an issue since the mapping was based on the column name and not the position.
At this point, I believe the next steps is to look and the BCP utility.

The BCP utility is position sensitive, as well.

I'm trying to hammer down your requirements so I can show a way to do this. What do you want to happen when the client sends a file that contains columns of information that your target table doesn't have columns for?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

kscott-851323 Right there with Babe Points: 759 More actions · Answer 7

Hey Jeff,

The client will send a flat file containing all the columns defined in the target table. For example, the target table will have 3 columns defined as such: CustomerName, CustomerAddress and CustomerCity.

The data file will contain the same Column names as the table but in a different order. For example, the data file would have CustomerCity, CustomerName and CustomerAddress.

When the package is run against a flat file with CustomerCity populated as the first column in the flat file, the package is storing the CustomerCity data in the CustomerName field instead of storing the data by matching the column names.

I understand that I can open the Flat File Connection Manager and click the Reset Columns Button to allow the data to be stored in the correct columns.

The goal is to find a way to get this done in a automatically without having to open each package and change the column order by clicking the Reset Columns button.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 8

kscott-851323 (7/22/2010)
Hey Jeff,
The client will send a flat file containing all the columns defined in the target table. For example, the target table will have 3 columns defined as such: CustomerName, CustomerAddress and CustomerCity.
The data file will contain the same Column names as the table but in a different order. For example, the data file would have CustomerCity, CustomerName and CustomerAddress.
When the package is run against a flat file with CustomerCity populated as the first column in the flat file, the package is storing the CustomerCity data in the CustomerName field instead of storing the data by matching the column names.
I understand that I can open the Flat File Connection Manager and click the Reset Columns Button to allow the data to be stored in the correct columns.
The goal is to find a way to get this done in a automatically without having to open each package and change the column order by clicking the Reset Columns button.

And tomorrow the customer could just as likely send the datafile as CustomerAddress, CustomerName, CustomerCity?

Also, what's the max number of columns we're talking about for any given file? And any chance that your using a 32 bit version of SQL Server or are you using 64 bit? (There's actually a good reason why I'm asking that)

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

kscott-851323 Right there with Babe Points: 759 More actions · Answer 9

Correct, the customer could send the file the next date in the order of CustomerAddress, CustomerName, CustomerCity. The position of the columns names in the flat file could change each time they send the file.

Currently, the environment we load the data is 32-bit. In the near future we may go to 64-bit.

The maximum number of the columns we can receive in a file from a client is 251 columns.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 10

Got a bit side tracked, yesterday. One does have to work for a living. 😀 Since you have the 32 bit environment, I can show you a couple of neat tricks to make this a pretty easy task. You might like them well enough to keep your ETL system(s) at 32 bit and use them to feed your future 64 bit systems.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 11

I believe the easy way to do this will be to use "OPENROWSET" against the file because it will read the first row with the correct column names. Then you only need to update your table by column name instead of position. Of course, in order to make the file name changeable, it'll all need to be done in dynamic SQL. Take a look at how to read a text file using "OPENROWSET" in Books Online. If you have any questions, post back.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

kscott-851323 Right there with Babe Points: 759 More actions · Answer 12

Thanks for all the assistance. I will give the OpenRowSet and try and hope it resolves the issues.