Post reply

Programming an SSIS Transform and UI

  • June 11, 2007 at 8:06 am

    #179363

    Hi

    I am trying to modify the reverse string http://www.sqlis.com/25.aspx example so that I can use it to create a regular expression transform.

    The idea would be that you are able ao select the column you wish to find a regular expression in and enter the regular expression along with a replace value in two additional columns supplied in the UI.

    I have modified the the UI to display additional columns into which I can pass my parameters, however I do not know how to reference the parameters which are entered. I need to enter the parameters to the pipeline or some other place?!, from where I can later access in the process input section.Does this make sense to anyone?! any help would be much appreciated

    Thanks

    PJ

  • June 13, 2007 at 1:57 am

    #712521

    Konesans already has a regex component but it just does matching, no replacing for you.

    As for the handling parameters, see the book called SQL Server Intergration Services by Worx. It has many examples to follow. You can also download the examples from their site although I am not sure this is technically right without buying their book(?)

    Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!

  • June 13, 2007 at 6:52 am

    #712592

    Thanks Crispin

    I have been working from the book you mentioned focusing on chapter 14 and 15. I have not been able to find a way in which to capture additional parameters that are not part of the input buffer pipeline to be later used in the process input section. Is there a way to append these parameters to the pipeline? or should I use a differnet approach?

    Thanks

    PJ

  • June 13, 2007 at 7:01 am

    #712597

    Sorry PJ - my knowledge of custom components is rather limited. I've hacked a couple together but not really done much else with it.

    A dirty way would be to use a script component and use variable etc in that. Not as elegant as a custom but....

    Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!

  • June 13, 2007 at 7:53 am

    #712619

    Thanks for your help anyway

  • June 25, 2007 at 9:36 am

    #714956

    You need to add custom properties to your component that allow you to enter the expression and replacement strings, then add a boolean custom column property that lets you flag which column to process.  I don't think you can modify the original column, this would be like a derived column transformation where the modified value shows up in a new column.

    Another variation would be to make the expression and replacement cusom column properties instead of component properties, so you could do multiple separate replacements in one step.

Viewing 6 posts - 1 through 5 (of 5 total)

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