search and replace SSIS package

  • Greetings

    I have an SSIS package with Sequence containers that call other SSIS packages. How can I change values in the whole package including "sub-packages" via a script that uses SSIS object model if there is such a thing.

    I tried this via SSIS package configuration but do not see the values I want to change for the sub-packages in the xml-ish config file

    Thank you

  • This sounds like something you could do with variables and package configuration. You might be able to use a script task to set the variables in your parent package based on whatever conditions you set then in your child packages configuration use Parent package variable to pass the variables from the parent package to the child package. From their you should be able to use the values in your child package variables however you need.

  • Where do you have the packages stored, if it is in SQL server you can update the binary data in sysssispackages, I had to do it for a migration of 200+ packages to a new server. I got the code from a blog, can't remember where, you may need to mess with the replace portions:

    USE MSDB

    GO

    --changes Data Source and server connection in sysssispackages packagedata

    BEGIN

    WITH PackageCTE(Id, PackageDataString)

    AS

    (

    SELECT id, CAST(CAST(packagedata AS varbinary(MAX)) AS varchar(MAX))

    FROM sysssispackages

    )

    UPDATE P

    SET PackageData = CAST(

    REPLACE(

    REPLACE(C.PackageDataString,

    'Data Source=OldServerNameInstanceName','Data Source=NewServerNameInstanceName'),

    'server=OldServerNameInstanceName', 'server=NewServerNameInstanceName')

    as varbinary(max))

    FROM sysssispackages P

    JOIN PackageCTE C ON C.id = P.id

    WHERE C.PackageDataString LIKE '%server=OldServerNameInstanceName%'

    OR C.PackageDataString LIKE '%Data Source=OldServerNameInstanceName%'

    END

    GO

    Andrew

  • thanks Andrew. unfortunately mine is saved on file server not database. I had a script vbs that connected to object model of SSIS and changed values.

    I will keep on searching

  • I believe I saw a powershell script that did it as well, can't remember if it was for packages stored in sql or not. Push comes to shove maybe you could do a a find and replace on the .dtsx files?

    Andrew

  • I could find and replace, that is what I want to do but how?

  • yosiasz (4/12/2011)


    Greetings

    I have an SSIS package with Sequence containers that call other SSIS packages. How can I change values in the whole package including "sub-packages" via a script that uses SSIS object model if there is such a thing.

    I tried this via SSIS package configuration but do not see the values I want to change for the sub-packages in the xml-ish config file

    Thank you

    Can you provide details which values you are trying to change?

    ---
    SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

  • I have one parent table with multiple packages wrapped in sequence containers. the packages in the sequence containers have variables and connections that need to be changed as they move from different environs, dev, sqa, stg production.

  • yosiasz (4/14/2011)


    I have one parent table with multiple packages wrapped in sequence containers. the packages in the sequence containers have variables and connections that need to be changed as they move from different environs, dev, sqa, stg production.

    You can load package variables setup from configuration file.

    ---
    SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

  • I load them programmatically or by editing the config file?

  • yosiasz (4/14/2011)


    I load them programmatically or by editing the config file?

    You can load them from the config file.

    ---
    SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

  • Quoting yosiasz:

    I could find and replace, that is what I want to do but how?

    Response:

    With package open click the view menu, then click Code, then do your find and replace.

  • yosiasz (4/14/2011)


    I load them programmatically or by editing the config file?

    You just need to create separate configuration files for all the environment (dev, qa, test, prod). Change the variables and connections for the diff env. When you run your package let it point to the appropriate config files.

    __________________________________________
    ---------------------------------------------------
    Save our mother Earth. Go Green !!!

Viewing 13 posts - 1 through 12 (of 12 total)

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