Dynamic File Column mapping in SSIS.

Question

Post reply

Dynamic File Column mapping in SSIS.

Hiske Bekkering

SSC Enthusiast

Points: 141
More actions
June 11, 2018 at 10:12 pm

#351086

Comments posted to this topic are about the item Dynamic File Column mapping in SSIS.
November 15, 2018 at 2:53 am

This was removed by the editor as SPAM
November 15, 2018 at 5:39 am

This was removed by the editor as SPAM

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

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

gabtugm Valued Member Points: 55 More actions · Answer 1

gabtugm

Valued Member

Points: 55

June 11, 2018 at 10:21 pm

#1993628

jonathan.crawford SSCertifiable Points: 6610 More actions · Answer 2

So now I just need one task to do everything, I just need to store the mappings in my table.

Perhaps a bit sarcastic, but still....nice solution!

-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses

Hiske Bekkering SSC Enthusiast Points: 141 More actions · Answer 3

jonathan.crawford - Tuesday, June 12, 2018 9:55 AM
So now I just need one task to do everything, I just need to store the mappings in my table.
Perhaps a bit sarcastic, but still....nice solution!

Hi Jonathan,

Nope... You can store your mappings anywhere you like! Could even be an XML file. The point being that storing the mappings outside of the code makes them configurable. It just happened to be the case that the mappings table was already there, having been used by the DTS 2000 package before the migration.

Regards,

Hiske

jonathan.crawford SSCertifiable Points: 6610 More actions · Answer 4

Hiske Bekkering - Tuesday, June 12, 2018 1:23 PM
jonathan.crawford - Tuesday, June 12, 2018 9:55 AM
So now I just need one task to do everything, I just need to store the mappings in my table.
Perhaps a bit sarcastic, but still....nice solution!
Hi Jonathan,
Nope... You can store your mappings anywhere you like! Could even be an XML file. The point being that storing the mappings outside of the code makes them configurable. It just happened to be the case that the mappings table was already there, having been used by the DTS 2000 package before the migration.
Regards,
Hiske

Huh. so I can make the BA team create and manage the file with the mappings, and I can just run it. I like this idea even better......thanks!

-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses

mtassin SSC-Insane Points: 23099 More actions · Answer 5

I'd recommend using Cozyroc for this. The Data Flow+ Task SSIS component has the capability to dynamically map columns on the fly

--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link[/url]
For tips on how to post your problems[/url]

Hiske Bekkering SSC Enthusiast Points: 141 More actions · Answer 6

mtassin - Wednesday, June 13, 2018 2:50 PM
I'd recommend using Cozyroc for this. The Data Flow+ Task SSIS component has the capability to dynamically map columns on the fly

Hi Mark,

Yes, I did think about that option too. Had I been the DBA responsible for the server, I might have been inclined to use a commercial component and be done with it. But I'm a developer and so I have to depend on other people to buy a licence and install the product on the server. And all that takes a lot of time which I did not have.

Being a developer, I like to roll my own, whenever possible.

Regards,

Hiske

ghalib.mustafa Valued Member Points: 67 More actions · Answer 7

Your package runs excellently but when I introduced my extra column in your InputFile it produced errors as if OutputBuffer didn't produced the respected value.

I have already updated the destination table, mapping table and mapping_count (in the code).
Is there anything else I should do, I also tried to introduced my new column in CreateNewOutputRows(function) but it gave compile time error as the OuputBuffer didn't have the reference value of this new column.
I would really appreciate if you can help me out, I am stuck in finding the solution of Dynamic Mapping for weeks now. This would be of great help.
Regards,
Ghalib

Hiske Bekkering SSC Enthusiast Points: 141 More actions · Answer 8

ghalib.mustafa - Thursday, November 15, 2018 5:42 AM
Your package runs excellently but when I introduced my extra column in your InputFile it produced errors as if OutputBuffer didn't produced the respected value.
I have already updated the destination table, mapping table and mapping_count (in the code).
Is there anything else I should do, I also tried to introduced my new column in CreateNewOutputRows(function) but it gave compile time error as the OuputBuffer didn't have the reference value of this new column.
I would really appreciate if you can help me out, I am stuck in finding the solution of Dynamic Mapping for weeks now. This would be of great help.
Regards,
Ghalib

Hi Ghalib,

Can you give a bit more information about all changes you made?
Or perhaps send me the package so I can take a look?

Regards,
Hiske

ghalib.mustafa Valued Member Points: 67 More actions · Answer 9

Hiske Bekkering - Thursday, November 15, 2018 8:52 AM
Hi Ghalib,
Can you give a bit more information about all changes you made?
Or perhaps send me the package so I can take a look?
Regards,
Hiske

Thankyou very much Sir, for helping me out. I really appreciate.
So, here is what I did.
I introduced one new column in your inputFile1.txt namely A2

I then insert a new record in 'tempTableMapping' as

I also introduced new column in destination table i.e 'TempTable' for new incoming column as.

But as you see your package run fine but it didn't mapped the new incoming column.

Here is what I did on coding side.
I updated the value of MAPPING_COUNT to 7, as extra column is added in temp table. But, updating CreateNewOutputRows() produced errors like this

Which I believe is because the corresponding code for new column is not produced at runtime which was supposed to be as per documentation.

For just confirmation if the RecordSet is getting all the values, I debugged the code and found that variable was fine holding all the mappings.
As you see.

I know I am missing something but couldn't figure out yet. Please Help me out of this I shall be thankful to you as this is the only Solution I can find online to achieve dynamic behavior. I am sorry, I have not much understanding of VB, so, couldn't tell much in technical terms as I am from web Development background.

And Thank-you so much for your precious time,

Regards,
Ghalib

Hiske Bekkering SSC Enthusiast Points: 141 More actions · Answer 10

ghalib.mustafa - Friday, November 16, 2018 5:42 AM
Hiske Bekkering - Thursday, November 15, 2018 8:52 AM
Hi Ghalib,
Can you give a bit more information about all changes you made?
Or perhaps send me the package so I can take a look?
Regards,
Hiske

Thankyou very much Sir, for helping me out. I really appreciate.
So, here is what I did.
I introduced one new column in your inputFile1.txt namely A2

I then insert a new record in 'tempTableMapping' as

I also introduced new column in destination table i.e 'TempTable' for new incoming column as.

But as you see your package run fine but it didn't mapped the new incoming column.
Here is what I did on coding side.
I updated the value of MAPPING_COUNT to 7, as extra column is added in temp table. But, updating CreateNewOutputRows() produced errors like this

Which I believe is because the corresponding code for new column is not produced at runtime which was supposed to be as per documentation.
For just confirmation if the RecordSet is getting all the values, I debugged the code and found that variable was fine holding all the mappings.
As you see.
I know I am missing something but couldn't figure out yet. Please Help me out of this I shall be thankful to you as this is the only Solution I can find online to achieve dynamic behavior. I am sorry, I have not much understanding of VB, so, couldn't tell much in technical terms as I am from web Development background.
And Thank-you so much for your precious time,
Regards,
Ghalib

Hi Ghalib,
You need to add the new column to the outputs as well!
Open the editor of the "Read File Contents" script, go to the "Inputs and Outputs" page, expand "Output 0" and add the definition for the new column.
See the below image.

Success!

Regards,
Hiske

ghalib.mustafa Valued Member Points: 67 More actions · Answer 11

Hiske Bekkering - Friday, November 16, 2018 9:03 AM
Hi Ghalib,
You need to add the new column to the outputs as well!
Open the editor of the "Read File Contents" script, go to the "Inputs and Outputs" page, expand "Output 0" and add the definition for the new column.
See the below image.
Success!
Regards,
Hiske

Thankyou sir,
This is exactly what I was missing. It did resolved the error but still, the package can't make the mappings.
I have debugged the code and looked in every for loops as if it is missing the "Column5" or "A2" anywhere but the code is not missing anything and everything is looking fine to me. Here is the screenshot of column mappings:

it must be really annoying to help a dumb developer, but I would really appreciate that and there may be thousands more like me, who could get help from this.

ghalib.mustafa Valued Member Points: 67 More actions · Answer 12

Here is the package with test file(modified).

Regards,
Ghalib