question

Kumar Magesh avatar image
Kumar Magesh asked

SSIS Dynamic Mapping

How to Map dynamically in SSIS a b c d 1 2 3 4 a c b d 1 3 2 4
ssis
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Pavel Pawlowski avatar image Pavel Pawlowski commented ·
What you mean by Map dynamically? Can you provide more details what you exactly want?
0 Likes 0 ·
Kumar Magesh avatar image Kumar Magesh commented ·
user gives the data like columns a b c d and values like 1 2 3 4 in flat file. for another month user gives like a c b d(column changes) and values are 1 3 2 4. i have to use the same package but i should not do any modifications. and i have to get the data like a b c d values like 1 2 3 4.
0 Likes 0 ·
WilliamD avatar image
WilliamD answered
You could do something with scripting tasks inside SSIS to find the column names and map them. However, you would be far better off informing your users to supply data in a predefined format. That way is much simpler and more sensible IMO. You are supplying a data import service and they want to use the service. If they don't comply to the agreed standard, you don't import the data. Maybe I'm too harsh, but that is how I expect both sides of a "contract" to behave.
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack avatar image Fatherjack ♦♦ commented ·
@mrs_fatherjack you spelled half-assed wrongly! You can retract a vote by clicking the vote again. 1st click = vote (up or down). 2nd click cancels that vote leaving you at starting point.
1 Like 1 ·
Mrs_Fatherjack avatar image Mrs_Fatherjack commented ·
@WilliamD tried to upvote this and it down voted it, sorry have asked @Fatherjack to sort.
0 Likes 0 ·
Pavel Pawlowski avatar image
Pavel Pawlowski answered
One possible workaround could be to define an ODBC DSN for that file and then use ADO NET source with ODBC driver in the SSIS data flow task. The source in the ADO NET component will be a query to the flat file with single order of fields.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack avatar image
Fatherjack answered
You would have to re-work your SSIS but you could use LogParser to get past this issue. Because it uses a SQL type language to query a flat file the column order is irrelevant. So long as they are all named the same thing each time then LogParser will move your data into SQL reliably every time it is run. The last article mentions using LogParser from within script but you may want to read through the whole series to understand what it does and how it works if you have never used it before... Part 1 - http://www.simple-talk.com/community/blogs/jonathanallen/archive/2010/05/24/LogParser_part_1.aspx Part 5 - http://www.simple-talk.com/community/blogs/jonathanallen/archive/2010/07/05/93370.aspx
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.