running SSIS packages in msdb with SQL Agent on SQL Server 2008 R2

Question

running SSIS packages in msdb with SQL Agent on SQL Server 2008 R2

bianchim

SSC Veteran

Points: 272
More actions
March 18, 2013 at 3:32 am

#269896

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?

Viewing 6 posts - 1 through 5 (of 5 total)

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

GRussell31 SSCrazy Points: 2116 More actions · Answer 1

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

bianchim SSC Veteran Points: 272 More actions · Answer 2

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?

GRussell31 SSCrazy Points: 2116 More actions · Answer 3

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/[/url]

Kind regards,

Graham

bianchim SSC Veteran Points: 272 More actions · Answer 4

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.

</DTSConfigurationHeading>

<ConfiguredValue>DWH.TestDB</ConfiguredValue>

</Configuration>

<ConfiguredValue>\Test\Child1</ConfiguredValue>

</Configuration>

<ConfiguredValue>G:\SSIS\Projects\Test\Child2.dtsx</ConfiguredValue>

</Configuration>

<ConfiguredValue>\Test\Child2</ConfiguredValue>

</Configuration>

</DTSConfiguration>

GRussell31 SSCrazy Points: 2116 More actions · Answer 5

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