search and replace SSIS package

Question

search and replace SSIS package

yosiasz

SSCommitted

Points: 1699
More actions
April 12, 2011 at 9:19 am

#389256

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

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

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

ZZartin SSC-Dedicated Points: 30921 More actions · Answer 1

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.

andrewkane17 SSCertifiable Points: 7529 More actions · Answer 2

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

yosiasz SSCommitted Points: 1699 More actions · Answer 3

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

andrewkane17 SSCertifiable Points: 7529 More actions · Answer 4

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

yosiasz SSCommitted Points: 1699 More actions · Answer 5

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

COZYROC One Orange Chip Points: 28499 More actions · Answer 6

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/

yosiasz SSCommitted Points: 1699 More actions · Answer 7

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.

COZYROC One Orange Chip Points: 28499 More actions · Answer 8

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/

yosiasz SSCommitted Points: 1699 More actions · Answer 9

I load them programmatically or by editing the config file?

COZYROC One Orange Chip Points: 28499 More actions · Answer 10

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/

ryanbesko Default port Points: 1424 More actions · Answer 11

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.

Anjan Wahwar SSCommitted Points: 1617 More actions · Answer 12

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 !!!