asked on

SSIS Column mapping

Hey...
I have a SSIS package that loads a CSV file and sends on to a SQL Server Table
Straight forward easy-peasy works great... except
Occasionally, the person that drops the file in the FTP will have a leading or trailing space on the column header text.
Is there any way to easily correct that?

Jim Horn

Somewhere in the process, either in an SSIS derived column or Execute SQL or executed SP, do this on the column..

UPDATE your_table
SET your_column = LTRIM(RTRIM(your_column))

Open in new window

>column header text.
Expand on what this means..

Larry Brister

ASKER

Jim Horn,
Here is the issue...
If the CSV file is opened in NotePad...
You can see the trailing space on the Status column
This will fail in the Mapped columns,

FirstName,LastName.Status , emailaddress
Bill, Bentley,1,test@csv.com

Arifhusen Ansari

You can solve this issue by manually giving name to columns in flat file connection manager.
Like

Col1
Col2
Col3

etc.

use this in your flat file source and than map that columns.

In that case no mater what your column name is your package will not fail.

Refer the screen shot.

ASKER CERTIFIED SOLUTION

Jim Horn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Larry Brister

ASKER

Looks right...
I'll just have toi add a script and read the top line of the csv file and do a replace of ' ,',','
When I get the time
and after I smack them up side the head.

Rest of the guys...
The columns MAY be in a different order at times.