Column delimiter - Merging Columns

Question

Post reply

Column delimiter - Merging Columns

A Little Help Please

SSCarpal Tunnel

Points: 4764
More actions
December 8, 2008 at 5:03 am

#196884

My SSIS package imports a FlatFile into a table.
An example line is:
0007,24843.00,AAL.L,ANGLO AMERICAN,B1XZS82,05-DEC-08,24843.00,1.0000,GBP,GB00B1XZS820
All the columns import correctly apart fromthe last two.
They seem to join together in the same column, so I get
0007,24843.00,AAL.L,ANGLO AMERICAN,B1XZS82,05-DEC-08,24843.00,1.0000,GBPGB00B1XZS820
I have checked the row {LF} and column delimiter (Comma,) are set correctly and the rest of the lines import fine.
Any suggestions.
Thanks

Viewing 15 posts - 1 through 15 (of 15 total)

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

A Little Help Please SSCarpal Tunnel Points: 4764 More actions · Answer 1

Attched is a screen shot of the FlatFile Connection Manager.

Shows a preview of the column which is incorrect.

Column 8

steveb. SSC-Forever Points: 46733 More actions · Answer 2

Do these values have quote identifiers (") around them in the flat file?

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

A Little Help Please (12/8/2008)
Attched is a screen shot of the FlatFile Connection Manager.
Shows a preview of the column which is incorrect.
Column 8

Column 9 here:

0007,24843.00,AAL.L,ANGLO AMERICAN,B1XZS82,05-DEC-08,24843.00,1.0000,GBPGB00B1XZS820

Open the flat file using Wordpad or some other simple text editor, have a look at the delimiters.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

A Little Help Please SSCarpal Tunnel Points: 4764 More actions · Answer 4

Answer to the above ttow questions:

This is exactually how it is in a notepad file.

0007,24843.00,AAL.L,ANGLO AMERICAN,B1XZS82,05-DEC-08,24843.00,1.0000,GBP,GB00B1XZS820

No delimiters i.e. ""

Just commas between each column

When the file is imported, if I do a SELECT on that table in column 8 i get GBPGB00B1XZS820 instead of GBP

steveb. SSC-Forever Points: 46733 More actions · Answer 5

Open the file in a Hex Editor to check the commas are all there and that there are no hidden value codes causing havoc

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

A Little Help Please (12/8/2008)
Answer to the above ttow questions:
This is exactually how it is in a notepad file.
0007,24843.00,AAL.L,ANGLO AMERICAN,B1XZS82,05-DEC-08,24843.00,1.0000,GBP,GB00B1XZS820
No delimiters i.e. ""
Just commas between each column
When the file is imported, if I do a SELECT on that table in column 8 i get GBPGB00B1XZS820 instead of GBP

There are ten columns...

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

LOOKUP_BI-756009 SSCertifiable Points: 7286 More actions · Answer 7

Go into your Flat File Connection Mng and ensure the following:-

Advanced Tab > There should be 10 columns and the data type and length of each are correct.

Also these are my settings on the General Tab of flat file connection Mng

Format : Delimeted

Text Qualifier : None

Header Row Delimiter : {CR}{LF}

I copied your line into a text pad and was able to dump the data to a sql table and perform a Select and here is my results

000724843.00AAL.LANGLO AMERICANB1XZS8205-DEC-0824843.001.0000GBPGB00B1XZS820

A Little Help Please SSCarpal Tunnel Points: 4764 More actions · Answer 8

Yes, correct there is 10 columns but I am only using 8.

Eight are in connection manager and Eight are mapped.

Following are my settings in Connection Manager

General

Delimited

NONE

{CR}{LF}

0

Column

Row Delimiter {LF}

Column Delimiter Comma {,}

LOOKUP_BI-756009 SSCertifiable Points: 7286 More actions · Answer 9

Since the column delimeter is "," you should have total 10 columns coming in from your flat file and mapping to a table with 10 columns as well.

You should add columns in the Advanced tab of the flat fle connection mng,to ensure you have 10 columns. Once done,click on the 'Preview' tab to get glimps of how data is going to be coming in.

You might also need to do changes to the sql table as well,to ensure you have 10 column there as well.

A Little Help Please SSCarpal Tunnel Points: 4764 More actions · Answer 10

thats incorrect I dont need 10 columns,

I can state how many columns i want to use and map them to the relative DB columns.

I have justed select Ignor for the two columns I dont not need in the OLE DB Destination Editor!

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 11

LOOKUP_BI (12/8/2008)
Since the column delimeter is "," you should have total 10 columns coming in from your flat file and mapping to a table with 10 columns as well.
You should add columns in the Advanced tab of the flat fle connection mng,to ensure you have 10 columns. Once done,click on the 'Preview' tab to get glimps of how data is going to be coming in.
You might also need to do changes to the sql table as well,to ensure you have 10 column there as well.

Couldn't have put it better.

Keep it as simple as possible. Start again, with ten columns. Create the extra two columns in your destination table. When this works ok, remove the two columns you don't want.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

LOOKUP_BI-756009 SSCertifiable Points: 7286 More actions · Answer 12

A Little Help Please (12/8/2008)
thats incorrect I dont need 10 columns,
I can state how many columns i want to use and map them to the relative DB columns.
I have justed select Ignor for the two columns I dont not need in the OLE DB Destination Editor!

Glad you got your problem solved.Maybe you can explain your question more clearly next time

A Little Help Please SSCarpal Tunnel Points: 4764 More actions · Answer 13

Chris,

Thanks again.

I recreated the FlatFileConnection within SSIS.

Looks like there was a mapping issue.

All columns importing correctly now.

Regards.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 14

Well done, many thanks for the feedback. I guess since this is the root of the problem, the whole chain is fixed now?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden