Package Configuration in SSIS

Ramesh Chandran

Hi,

I am new to SSIS. I have an SSIS package which we are storing it in the application server ( not in SQL Server ). We are storing the database name etc.. in package configuration. Where should I store the configuration file In app server or in the database server. Also please tell how to execute the package stored as file system.

Thanks

ramesh

Rafael Salas


You can store the config file in any file system location you want; but you have to tell the pckage where to find it. When you create a package configuration using a XML file; you have to provide the path to the file (direct method) or the environment variable name that hold that path (indirect method)

MatthewRoche

Can you please elaborate on what you mean by "application server" in this context

If you're storing the DTSX package files on the file system, it probably makes sense to do the same with your config files. Store them as dtsConfig XML configuration files, ideally as "indirect" XML file configurations, where the fully qualified path to the files are stored in Windows environment variables. This should give you all of the flexibility you need.

To execute the packages from the file system, use the DTEXEC command line utility. The easiest way to do this is to use the DTEXECUI utility to build the command-line parameters for DTEXEC. It's much easier than doing everything yourself by hand.

Ramesh Chandran

Thanks for the reply.

I have creatred ait using XML file. But how to tell it to the package.

MatthewRoche

Ramesh Chandran wrote:

Thanks for the reply. I have creatred ait using XML file. But how to tell it to the package.

The best way to answer this is to point you to the SSIS tutorials in SQL Server Books Online:

http://msdn2.microsoft.com/en-us/library/ms169917.aspx

These are great starting points to get your hands dirty with many different aspects of SSIS, including adding configurations to your packages.

Another, somewhat shorter answer, is to right-click on the package's control flow design surface and select "package Configurations" from the pop-up menu. This dialog is how you "tell the package" about the configurations.

Ramesh Chandran

Two servers we have, one for database and in the other one all exe's,... resides. I am calling it as "MYSERVER"

I am storing the ssis package in MYSERVER. I thought of using dtexec \File option to run the dtsx package.

I will store the package configuration file in the "MYSERVER".

This is the config file generated.

< xml version="1.0" >
<DTSConfiguration>
<DTSConfigurationHeading>
<DTSConfigurationFileInfo GeneratedBy="User" GeneratedFromPackageName="Package" GeneratedFromPackageID="{0F0A1F60-3552-4BFA-891C-D60F72C28AA1}" GeneratedDate="5/29/2007 12:52:52 PM"/></DTSConfigurationHeading>
<Configuration ConfiguredType="Property" Path="\Package.Connections[testserver\I01.PUBLISHER_Database].Properties[InitialCatalog]" ValueType="String"><ConfiguredValue>PUBLISHER_Database</ConfiguredValue>
</Configuration>
<Configuration ConfiguredType="Property" Path="\Package.Connections[testserver,2048\I01.PUBLISHER_Database].Properties[ServerName]" ValueType="String"><ConfiguredValue>testserver,2048\I01</ConfiguredValue>
</Configuration>
<Configuration ConfiguredType="Property" Path="\Package.Connections[pmastrep.PSC].Properties[ConnectionString]" ValueType="String"><ConfiguredValue>Data Source=pmastrepTest;User ID=PSC;Provider=OraOLEDB.Oracle.1;</ConfiguredValue></Configuration>
</DTSConfiguration>

Now how I need to run the package

MatthewRoche

Ramesh Chandran wrote:

Now how I need to run the package

To execute the packages from the file system, use the DTEXEC command line utility. The easiest way to do this is to use the DTEXECUI utility to build the command-line parameters for DTEXEC. It's much easier than doing everything yourself by hand.

The syntax will look something like this:

DTEXEC /FILE "C:\Projects\ForumsWorkspace\ForumsWorkspace\Package.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW

Ramesh Chandran

If I am running using this command

DTEXEC /FILE "C:\Projects\ForumsWorkspace\ForumsWorkspace\Package.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW

Where I need to specify about the package configuration file. Will it takes automatically

or explicitly i need to specify the path.

MatthewRoche

Ramesh Chandran wrote:

If I am running using this command DTEXEC /FILE "C:\Projects\ForumsWorkspace\ForumsWorkspace\Package.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW Where I need to specify about the package configuration file. Will it takes automatically or explicitly i need to specify the path.

Please refer to my earlier reply from 12:53 PM UTC, above.

Rafael Salas

Ramesh Chandran wrote:

If I am running using this command DTEXEC /FILE "C:\Projects\ForumsWorkspace\ForumsWorkspace\Package.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW Where I need to specify about the package configuration file. Will it takes automatically or explicitly i need to specify the path.

That is when you are editing the package in BIDS. When you create a package configuration in the package; the PAckage configuration Organizer will prompt you for either a file location (direct) or an Env variable name (indirect)

jwelch

You can also specify the configuration file to use at runtime using the /CONFIG switch for DTEXEC. This is useful when your production configuration path is different than the one you use during development.