Dynamic File Column mapping in SSIS.

  • Hiske Bekkering

    SSC Enthusiast

    Points: 141

    Comments posted to this topic are about the item Dynamic File Column mapping in SSIS.

  • gabtugm

    Valued Member

    Points: 55

    <comment deleted>

  • jonathan.crawford

    SSCertifiable

    Points: 6366

    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

    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: 6366

    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: 23096

    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

    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
  • This was removed by the editor as SPAM

  • This was removed by the editor as SPAM

  • ghalib.mustafa

    Valued Member

    Points: 67

    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

    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

    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

    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

    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

    Here is the package with test file(modified).

    Regards,
    Ghalib

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

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