SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
bianchim
bianchim
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
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?
GRussell31
GRussell31
SSC-Enthusiastic
SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)

Group: General Forum Members
Points: 184 Visits: 156
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
bianchim
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
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?
GRussell31
GRussell31
SSC-Enthusiastic
SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)

Group: General Forum Members
Points: 184 Visits: 156
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
bianchim
bianchim
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
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>
GRussell31
GRussell31
SSC-Enthusiastic
SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)

Group: General Forum Members
Points: 184 Visits: 156
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search