April 12, 2011 at 9:19 am
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
April 12, 2011 at 11:51 am
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.
April 12, 2011 at 12:45 pm
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
April 12, 2011 at 1:41 pm
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
April 12, 2011 at 4:16 pm
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
April 13, 2011 at 3:38 pm
I could find and replace, that is what I want to do but how?
April 14, 2011 at 7:57 am
yosiasz (4/12/2011)
GreetingsI 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?
April 14, 2011 at 9:01 am
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.
April 14, 2011 at 9:04 am
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.
April 14, 2011 at 9:26 am
I load them programmatically or by editing the config file?
April 14, 2011 at 9:55 am
April 14, 2011 at 6:04 pm
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.
April 15, 2011 at 7:31 am
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