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

  • 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?

  • 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

  • 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?

  • 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

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

  • 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

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

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