Post reply

SSIS reusable funtions

  • February 2, 2012 at 4:37 am

    #150772

    i am getting postal code data from various source systems (excel , database text file etc )

    now i want to write a custom function in SSIS so that i can format the postal code

    Example

    Postal code = DFR45

    the above should be formatted to DF R45

    here i dont want to use the built in funtiones in SSIS package because in later point of time the format will change so i dont want to change in all the places .

    So can any one help me how to write a custome function in SSIS and how to call that function in expressions.

  • February 2, 2012 at 4:53 am

    #1441678

    You could create a stored procedure/function in the database, and then use this in Execute SQL Tasks.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • February 2, 2012 at 5:09 am

    #1441692

    I DONT WANT TO CREATE ANY FUNCTIONS IN DATABASE BECAUSE I AM NOT PULLING THE DATA FROM A DATABASE THE SOURCES ARE DIFFERENT LIKE (EXCEL , ORACLE , SQLSERVER , TEXT FILE ) THE FORMATTED DATA WILL BE SENT TO ANOTHER SYSTEM IN THE FORM OF TEXT FILE.

    i WANT A GENERIC SOLUTION FOR THIS .

  • February 2, 2012 at 5:10 am

    #1441693

    Or you could set up a variable with the "function" logic in it, and use that in your expression. I prefer Koen's method, I think - if something can be done in set-based fashion, it's best to do it in the database rather than SSIS.

    John

  • February 2, 2012 at 5:13 am

    #1441695

    simhadriraju (2/2/2012)

    I DONT WANT TO CREATE ANY FUNCTIONS IN DATABASE BECAUSE I AM NOT PULLING THE DATA FROM A DATABASE THE SOURCES ARE DIFFERENT LIKE (EXCEL , ORACLE , SQLSERVER , TEXT FILE ) THE FORMATTED DATA WILL BE SENT TO ANOTHER SYSTEM IN THE FORM OF TEXT FILE.

    i WANT A GENERIC SOLUTION FOR THIS .

    I'm sure this is a language thing, but a reminder - use of capital letters is equivalent to shouting, and excessive use of the word "want" and absence of the word "please" makes you sound discourteous.

    Anyway, I think what Koen is suggesting is that you bring everything into a staging table, and then use a stored procedure.

    John

  • February 2, 2012 at 5:27 am

    #1441711

    Since i dont have any authority to create stored procedures or functions in the database i want to prefer only SSIS reusable fuctions to achieve this.

    Please anyone Help meeeeeeeeeee.. Its an urgent task for me.

  • February 2, 2012 at 5:30 am

    #1441714

    Then you have two options:

    (1) Get all the data into a staging table and update it with an Execute SQL task

    (2) Use my original suggestion.

    John

  • February 2, 2012 at 5:34 am

    #1441721

    So can't we create any reuable function for this

    because like postal code i have some other fields as well with different set of formats i need to have some common function like . so that i future if the format of the fileds are changed at that point of time we have very less work .

  • February 2, 2012 at 5:37 am

    #1441723

    John Mitchell-245523 (2/2/2012)

    Then you have two options:

    (1) Get all the data into a staging table and update it with an Execute SQL task

    (2) Use my original suggestion.

    John

    (3) Don't use SSIS?

  • February 2, 2012 at 5:46 am

    #1441732

    Since SSIS doesn't offer you generic solutions such as functions, you need to put up a webservice. In this webservice, code a function that achieves the desired result.

    In the dataflow, you can call this webservice for every row using the script component.

    Since you were so polite before, I will not tell if this post was sarcastic or not. :smooooth:

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • February 2, 2012 at 5:56 am

    #1441744

    Please anyone can help me out on this

  • February 2, 2012 at 6:00 am

    #1441746

    simhadriraju (2/2/2012)

    Please anyone can help me out on this

    What, you don't like the webservice solution?

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • February 2, 2012 at 6:10 am

    #1441750

    Any other thoughts

  • February 2, 2012 at 6:23 am

    #1441762

    There's no such thing as reusable functions in SSIS (edit: not true! - thanks Phil) so if you do use it you will have to create multiple tasks containing the function & update them all if there is a future change.

    If you don't want to do that, consider the possibility that SSIS is not the right tool for this job.

    Or petition for access to the database so you can load & transform the data using a proc/function.

  • February 2, 2012 at 6:42 am

    #1441770

    There's no such thing as reusable functions in SSIS ...

    That's not quite true, though it certainly isn't as easy as it might be. If you are prepared to venture into the world of custom assemblies and the GAC, it is all possible. Here's a link which gives an example.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

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

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