Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

running SSIS packages in msdb with SQL Agent on SQL Server 2008 R2 Expand / Collapse
Author
Message
Posted Monday, March 18, 2013 3:32 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 23, 2013 12:08 AM
Points: 4, Visits: 41
Hello,
I am quite new to SSIS, and I've run into a problem deploying a set of packages on SQL Server 2008 R2.

I developed a set of packages for loading data from a source system in a SLQ Server,
I have a master package that coordinates the execution of a set of child packages.
The packages are configured using a set of common XML files, DEV, QA, and PROD run fine.
I scheduled the loading using SQL Agent with the packages on the file system, all runs through without problems.

Now I would like to have all my packages store inside msdb instead of file system.
I deployed my packages to msdb, and reset the agent to run the master in the msdb,
but at runtime the master package looks for the child packages on the file system not in the msdb.

What should I do to deploy everything so that the master uses the child packages in the msdb?
Post #1432118
Posted Monday, March 18, 2013 8:33 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, January 27, 2014 2:40 AM
Points: 95, Visits: 150
Hi,

The Execute Package Task gives you the option for the location of the SSIS package you want to execute (either in SQL Server or the File System).

I'm guessing this is set to the File System at the moment.

Did you amend this when you moved the packages into SQL Server?

Regards,

Graham
Post #1432208
Posted Monday, March 18, 2013 8:39 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 23, 2013 12:08 AM
Points: 4, Visits: 41
What you are suggesting is that I change manually all the option for the location of the child packages from FileSystem to
SQL Server before creating the deployment utility script at build time?

Isn't there a way to have it done at runtime, maybe using a setting in a variable?
Post #1432210
Posted Monday, March 18, 2013 8:43 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, January 27, 2014 2:40 AM
Points: 95, Visits: 150
Hi,

You can use a configuration file to make the changes.

In the "SSIS" drop down select the "Package Configurations..." and you can configure which values you would like to store as configuration, and these can be stored in an XML file or a SQL Table.

This article should help:
http://www.sqlservercentral.com/articles/Integration+Services+(SSIS)/66500/

Kind regards,

Graham
Post #1432214
Posted Thursday, March 21, 2013 1:12 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 23, 2013 12:08 AM
Points: 4, Visits: 41
Thanks, it worked.

I have a configuration file where I configure both the child as package on the file system and on the db.
I can switch between the two by changing the connection between the .dtsx and the DB, like in the
example below. Child1 in in the DB and Child2 on the filesystem, if I change Child2 connection to DWH.TestDB
the master executes the Child2 package in the DB. Thanks for your input.

<DTSConfiguration>
<DTSConfigurationHeading>
<DTSConfigurationFileInfo GeneratedBy="ADMINETATFR\da-bianchim" GeneratedFromPackageName="A - MDSI Master Loader" GeneratedFromPackageID="{100CFBFC-117A-4D44-9C64-F0E616721773}" GeneratedDate="20.03.2013 14:44:17" />
</DTSConfigurationHeading>
<Configuration ConfiguredType="Property" Path="\Package\Child1.Properties[Connection]" ValueType="String">
<ConfiguredValue>DWH.TestDB</ConfiguredValue>
</Configuration>
<Configuration ConfiguredType="Property" Path="\Package\Child1.Properties[PackageName]" ValueType="String">
<ConfiguredValue>\Test\Child1</ConfiguredValue>
</Configuration>
<Configuration ConfiguredType="Property" Path="\Child2.Properties[Connection]" ValueType="String">
<ConfiguredValue>G:\SSIS\Projects\Test\Child2.dtsx</ConfiguredValue>
</Configuration>
<Configuration ConfiguredType="Property" Path="\Package\Child2.Properties[PackageName]" ValueType="String">
<ConfiguredValue>\Test\Child2</ConfiguredValue>
</Configuration>
</DTSConfiguration>
Post #1433609
Posted Thursday, March 21, 2013 5:22 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, January 27, 2014 2:40 AM
Points: 95, Visits: 150

I'm glad it worked for you.

You can store the configuration information in a SQL table as well as an XML file, this may give you greater flexibility for updates.

Graham
Post #1433727
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse