SSIS Mapping Columns from Flat Files

I had to use use ssis 2005 in a short project recently & had little
time to work it out. I was importing a whole bunch of flat files into
SQL Server tables with many derived columns and transformations in
between.

It seems to automatically map columns from the flat file to columns in
the sql table where the names of the columns are equal. But can it
also do it automatically on position, so flat file column 1 goes to
sql table colum 1, etc, etc? In each flat file I had to manually click
and drag the columns across to map them which took a very long time as
there were hundreds of columns in some tables!

Thanks.

Re: SSIS Mapping Columns from Flat Files

Hi Stephen,

No, SSIS can't map columns automatically unless the names are the
same.

Why aren't the names the same? If you're transforming your data, you
can rename your columns. Or your connection manager could assign the
correct column names and you can ignore the names supplied by the file
(if that is why they are different).

It would be just as frustrating if columns *were* automatically mapped
by position for those who do not want this behaviour as it is now for
those who do :)

J

Re: SSIS Mapping Columns from Flat Files

On Jan 4, 1:51=A0pm, jhofm...@googlemail.com wrote:
> Hi Stephen,
>
> No, SSIS can't map columns automatically unless the names are the
> same.
>
> Why aren't the names the same? =A0If you're transforming your data, you
> can rename your columns. =A0Or your connection manager could assign the
> correct column names and you can ignore the names supplied by the file
> (if that is why they are different).
>
> It would be just as frustrating if columns *were* automatically mapped
> by position for those who do not want this behaviour as it is now for
> those who do :)
>
> J

If i have to rename hundreds of columns from the flat file that would
take longer than doing the mappings manually. It seems that if I'm
importing a flat file into a table and there is a 1 to 1 relationship
between the columns ssis could easily map column 1 from the flat file
to map to column 1 from the table, etc, etc. For me, in most cases
this would be correct and it would take much less time to undo the
mappings I don't want than to have to manually map the ones I do want
(which in my recent project was 1000s of columns over almost 200
tables).

But I know this wouldn't apply to every scenario.

Re: SSIS Mapping Columns from Flat Files

If this feature is important to you, consider submitting this feature
request via Connect feedback (http://connect.microsoft.com/sqlserver).
Perhaps it will make it into a future SQL version.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"stephen" wrote in message
news:ac0a0ea9-4d43-43c4-b073-7fcf4351bbf2@f10g2000hsf.google groups.com...
On Jan 4, 1:51 pm, jhofm...@googlemail.com wrote:
> Hi Stephen,
>
> No, SSIS can't map columns automatically unless the names are the
> same.
>
> Why aren't the names the same? If you're transforming your data, you
> can rename your columns. Or your connection manager could assign the
> correct column names and you can ignore the names supplied by the file
> (if that is why they are different).
>
> It would be just as frustrating if columns *were* automatically mapped
> by position for those who do not want this behaviour as it is now for
> those who do :)
>
> J

If i have to rename hundreds of columns from the flat file that would
take longer than doing the mappings manually. It seems that if I'm
importing a flat file into a table and there is a 1 to 1 relationship
between the columns ssis could easily map column 1 from the flat file
to map to column 1 from the table, etc, etc. For me, in most cases
this would be correct and it would take much less time to undo the
mappings I don't want than to have to manually map the ones I do want
(which in my recent project was 1000s of columns over almost 200
tables).

But I know this wouldn't apply to every scenario.

Re: SSIS Mapping Columns from Flat Files

On Jan 10, 5:17=A0am, "Dan Guzman" online.sbcglobal.net> wrote:
> If this feature is important to you, consider submitting this feature
> request via Connect feedback (http://connect.microsoft.com/sqlserver).
> Perhaps it will make it into a future SQL version.
>
> --
> Hope this helps.
>
> Dan Guzman
> SQL Server MVP
>
> "stephen" wrote in message
>
> news:ac0a0ea9-4d43-43c4-b073-7fcf4351bbf2@f10g2000hsf.google groups.com...
> On Jan 4, 1:51 pm, jhofm...@googlemail.com wrote:
>
> > Hi Stephen,
>
> > No, SSIS can't map columns automatically unless the names are the
> > same.
>
> > Why aren't the names the same? If you're transforming your data, you
> > can rename your columns. Or your connection manager could assign the
> > correct column names and you can ignore the names supplied by the file
> > (if that is why they are different).
>
> > It would be just as frustrating if columns *were* automatically mapped
> > by position for those who do not want this behaviour as it is now for
> > those who do :)
>
> > J
>
> If i have to rename hundreds of columns from the flat file that would
> take longer than doing the mappings manually. It seems that if I'm
> importing a flat file into a table and there is a 1 to 1 relationship
> between the columns ssis could easily map column 1 from the flat file
> to map to column 1 from the table, etc, etc.

This or a variant would take a few minutes, but might be worth it.

Get a function that parses a delimited string and returns a table.
(There's one on Erland's web site.)
For each flat file....
Read the first row, which contains the column names.
Parse that into a table and join it to the system table that
indentifies the columns in the target table, by ordinal position.
Go through that in order and build a "create view" statement that has
the effect of renaming the columns.
Execute the statement, and now you can load into the view directly.
The only external parameters are the associations between flat file
and tables.

Jim Geissman