SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Dynamic File Column mapping in SSIS.


Dynamic File Column mapping in SSIS.

Author
Message
Hiske Bekkering
Hiske Bekkering
SSC Journeyman
SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)

Group: General Forum Members
Points: 79 Visits: 48
Comments posted to this topic are about the item Dynamic File Column mapping in SSIS.
gabtugm
gabtugm
SSC Rookie
SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)

Group: General Forum Members
Points: 37 Visits: 196
<comment deleted>
jonathan.crawford
jonathan.crawford
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4936 Visits: 1452
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
Hiske Bekkering
SSC Journeyman
SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)

Group: General Forum Members
Points: 79 Visits: 48
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
jonathan.crawford
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4936 Visits: 1452
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
mtassin
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20835 Visits: 72547
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
For tips on how to post your problems
Hiske Bekkering
Hiske Bekkering
SSC Journeyman
SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)

Group: General Forum Members
Points: 79 Visits: 48
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
ghalib.mustafa
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 9
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
Hiske Bekkering
SSC Journeyman
SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)

Group: General Forum Members
Points: 79 Visits: 48
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
ghalib.mustafa
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 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
Hiske Bekkering
SSC Journeyman
SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)

Group: General Forum Members
Points: 79 Visits: 48
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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search