Configure a Child Package to use the configuration file of a Parent Package?

Hi guys,

Here's the deal. I have a child package, (say, pack01.dtsx), which uses a dtsconfig file for its connection string, which can be called from other packages, but which also can be called by itself.

However I also have another package (say, pack02.dtsx) which uses the same dtsconfig file for its connection string. It calls on pack01.dtsx.

When I use DTEXECUI and run pack01.dtsx, specifying the proper .dtsconfig file, it goes well. But when I try and run pack02.dtsx, an error occurs saying pack01.dtsx connection cannot be established.

How do I pass the connectionstring being used by pack02 to pack01, without having to remove the configuration file setting of pack01? Can a Parent Package configuration and a configuration file try and map to the same property?

Hope someone could help. Thanks.Still no idea on this guys?

Essentially I just want the child package to use and consume the same .dtsConfig file as the parent package. How do I do that?|||You can use "Parent package variable" configuration type in the child, or just set the same configuration file as the option for a configuration in the child. You cannot pass a entire configuration definition between parent and child.|||

DarrenSQLIS wrote:

You can use "Parent package variable" configuration type in the child, or just set the same configuration file as the option for a configuration in the child. You cannot pass a entire configuration definition between parent and child.

This is exactly what I did, really. And besides, when you try to pass a connectionstring through a parent package variable to a child, for some reason or another the password property value gets lost.

Any other ideas?|||

Ok so connection strings are really built from properties, and the password property will be write only, so that kind of makes sense. If you could read a connection string for a configuration to get the password, it would be a security risk, regardless of if this is for a configiuration or not.

Use a variable instead, set the variable from the configuration in the parent. Use a property expression to assign the variable value to the connection. Cannot see anything wrong with that as a solution, apart from the obvious security concerns.

|||

DarrenSQLIS wrote:

Ok so connection strings are really built from properties, and the password property will be write only, so that kind of makes sense. If you could read a connection string for a configuration to get the password, it would be a security risk, regardless of if this is for a configiuration or not.

Use a variable instead, set the variable from the configuration in the parent. Use a property expression to assign the variable value to the connection. Cannot see anything wrong with that as a solution, apart from the obvious security concerns.

I guess I'm compelled to use Windows Authentication for this case, just so I don't need to mess with the password.

Anyway, thanks |||If there is a chance that you can use Windows Auth, then I would strongly recomend it, for this any many other reasons, it just makes life easier. Good luck.

Configurations

Hello, I had a question I thought someone might be able to answer before I test my theory.

If I run an outer package through SQL Server Agent and specify it to use a configuration file, will the children packages it call also use the configuration file? My instinct is no it will not.

If this is in fact the case, would it be considered best practice to just specify the configuration file for each package through an indirect environment variable? Is it possible to set an environment variable on the set values tab within sql server agent?

Then my only concern is how would developers test packages on their machines, should they also set up the same environment variable on their machine?

Any thoughts?

Okay, I think I'm answering my own question, but perhaps others can benefit.

I think it is best to configure configurations in SSIS indirectly through an environment variable and then set the environment variable through sql server agent - command file tab, which will use the SetX command to modify the environment variable. This way all packages outer and inner can use the same dynamically configured configuration file! Although I supposse if multiple jobs tried to configure the same environment variable there could be issues, you'd have name your environment variables appropriately.

Thoughts?

|||

Configuration are not inherited between packages as you suspected.

I think you can say using indirect config files is good practice, and making you devs have the same env variable is the way to go. It actually works very well I find. I like the fact that I use the same mechanism to control connections and such like all the way from local development through to production. Configurations just work so well like that.

The Set tab in SQL Agent is not for environment variables, Set in that case is the same type of set that you do in the config file, xpath style property set, but available on teh execution command rather than wrapped in a file.

BTW, use CmdExec steps and DTEXEC, much better as you can use the step log.

|||Thanks for the response. Could you please clarify what you mean by your last statement?|||

The SSIS job step type is not very good. The CmdExec / Operating System step type supports logging of all output of the step. If you use DTEXEC and turn on step logging you get all the console output. Why is this good? Well put simply the SSIS step log capture is pants. Some errors are impossible to diagnose with it. A quick examples are file permissions, or other errors that happen before a package starts executing, see the top bit of this page -http://wiki.sqlis.com/default.aspx/SQLISWiki/ScheduledPackages.html

|||Okay, I understand what your saying and completely agree. This is how things were done at a prior company, but here they prefer using ssis job steps for the ease of seeing what package is running. That is why I wanted to work around configuring environment variables through SQL Server Agent istead of within a batch file. Still, logging may be insufficient as you pointed out. Thanks.sqlsql

Configuration Package doesn′t work

Good morning....
I work with many packages in 3 environments...development, homologation and production.
I had in each enviroment the table WCOT_Config with your configurations for this enviroment. There's a connection called Config wich have a expression
ServerName = System::MachineName to pickup the right server for each enviroment.

My package catches the configuration that is in the machine which the expression of the Config points. This

was functioning perfectly, but now my new packages that i place in the

production server are importing for the development server.

Suddenly the configuration stopped to function, but only for the new packages.

I have to check anything else for the package uses the configuration ?

Sorry my poor english ! heeheh

Thanks to everybody

Hy people,
I found the problem....the configuration was using the wrong connection
Thanks to everybody...

Configuration for Configuration Package? Is this Possible?

Hi Guys,

Here's the current scenario:

I am creating a windows application to manage my ssis packages.

My SSIS packages use package configurations, and by default connect to the "SSIS Configurations" table in sql server.

However, I want it to connect to a view instead of this table, so i create a view "vwSSIS_Configurations" and point the package configuration to use the view in sql server.

The application SHOULD be capable of altering the package configuration's configuration and switch its connection between the default "ssis configurations" table, and the view.

Is this possible? I cannot think of a way which I can have my application switch the package configuration's connection.

Thanks,

kervy

There are a number of ways. I would store the connection string of the connection manager in an XML configuration file.

-Jamie

|||

it's not the connection string of the connection manager, but the connection to the package configuration table,

currently my package configurations are looking in the "SSIS Configurations" table.. but I want my application to let the dtsx switch to another configuration table named "SSIS Configurations2" both have the same schema but different values. is there a "configuration" property for the configuration table connection?

|||

Ah OK. Well I've just looked at the setup screen for SQL Server configs for the first time in my life and seen that you can store the config location in an environment variable. I don't know exactly what it does but have you explored that? BOL should contain details to help you.

-Jamie

Configuration files do not work in CmdExec mode

I have created an Integration Services package on my development machine. The package contains a configuration file witch let's say is stored in c:\projects\MyIntegrationServicesProject\myConfigfile.dtsConfig (on my dev machine).

Then I have another "Production" machine where I import the SSIS package into an SQL database. I then create an sql-job with only one step, to run my SSIS package. This works fine if I configure the step to be an "SQL Integration services package" and configure it to use my configurationfile.

However I would like to configure this package as a CmdExec step. In the commandline, I specify /CONFIGFILE "d:\....\myConfigfile.dtsConfig" (the correct path on the prod machine). But it seems to be ignored, because when I execute the package I get an error telling me that the configuretion file c:\projects\MyIntegrationServicesProject\myConfigfile.dtsConfig cannot be found.

What I try to say is, it seems like it ignors the config-file I specify on the command-line and tries to reach the config-file on a location that's probably stored somewhere in the SSIS package from the time it was created on my development machine.

Is there a way around this?

Does anyone know if this is the correct behaviour of a SSIS package? Shouldn't the config file in the command line overrule any command-line inside the package?|||Can't help with an answer, but curious as to why you need to use CmdExec instead of Integration Services Package?

Greg.|||

Hi GregAbd!

The reason to why I need CmdExec is that running it as an Integration Services Package doesn't give any good output if something goes wrong. And, I also have the problem that my package takes twice as long time if I run it inside a job as if I run it "manually". I'm curious to know if this changes if I run it in the job, but as a cmdexec task.

Regards Andreas

|||Can't help with the run time issue, but you can enable logging in your package - you can literally log everything that happens. Just right-click on the package (i.e. a blank area of the Control Flow) and choose Logging. You can add a Log Provider for Text Files, create a File Connection Manager, and choose which events you want logged.

You can then configure the Log file location from the Data Sources tab on the SQL Agent Job Step.

Greg.|||

You have set a config in your package, the command line allows you to add configurations in addition to the one in the package, it does not change the package structure.

There are similar issues covered in the answers here-

Re: Implementing IDTSLogging - MSDN Forums
(http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=816241&SiteID=1)

Configuration files and variables

Hi ..

I need some information on the package configuration files. i.e how to set the configuration parameters from outside the packages. Pls provide me some detailed information along with examples. I want to make my packages to read paramaters from the configuration files as we have in .NET(web.config)////

Secondly I want to know how to play well with variables.. i.e setting a variable.. reading a variable.. passing a variable in the SSIS package dataflows and datatasks.. respectively. Please provide some infomation and a good exam[ple on it I need

sam_idea wrote:

Hi ..

I need some information on the package configuration files. i.e how to set the configuration parameters from outside the packages. Pls provide me some detailed information along with examples. I want to make my packages to read paramaters from the configuration files as we have in .NET(web.config)////

Have you looked in BOL? There's plenty in there regarding SSIS configurations.

Example here: http://www.sqlis.com/?26

sam_idea wrote:

Secondly I want to know how to play well with variables.. i.e setting a variable.. reading a variable.. passing a variable in the SSIS package dataflows and datatasks.. respectively. Please provide some infomation and a good exam[ple on it I need

The 2 most common methods for setting the value of a variable are using an expression (when the variable has EvaluateAsExpression=TRUE, example here: http://blogs.conchango.com/jamiethomson/archive/2005/03/19/1163.aspx) or a script task. There are various tasks (e.g. Execute SQL Task, XML Task) and components (e.g. Rowcount transform, Recordset destination) that can set variable values also.

There is no concept of "passing variables between tasks". A variable is scoped to a container within the package and is accessible by that container and all its ancestral containers. You can pass values between tasks by storing them in variables (note the subtle difference here)

Here's some examples of how you can use variables:

http://blogs.conchango.com/jamiethomson/archive/2005/07/04/1748.aspx

http://blogs.conchango.com/jamiethomson/archive/2005/04/09/1265.aspx

http://blogs.conchango.com/jamiethomson/archive/2005/06/15/1693.aspx

http://blogs.conchango.com/jamiethomson/archive/2005/05/30/1489.aspx

http://blogs.conchango.com/jamiethomson/archive/2005/02/09/964.aspx

Here's a tip for you. Google is your friend and alot quicker than this forum: http://www.google.co.uk/search?hl=en&q=ssis+configurations&meta=

-Jamie

Configuration File Woes!!

I have a package for which I have set ProtectionLevel to DontSaveSenstive.

I connect to a SQL Database which supports mixed authentication.

To connect to this server in a flexible way, I have enabled SSIS package configuration.

I have edited my dtsconfig file with notepad and put the right password there (I am able to use management studio to connect to that database using same password).

Now when I come back to BIDS and double click on my connection manager, it doesn't read the configuration from the config file. (it just has a blank password). So it seems that BIDS doesn't read the config file.

What can I do to make BIDS read the config file.

Also, is there a way to encrypt the password (without keys) in the configuration file?

regards,
Abhishek.

Is the package actually failing to validate or run? I think the password box is blank as a security measure, not because the password isn't set.

On encrypting the configs, check out these posts:

http://blogs.conchango.com/jamiethomson/archive/2007/04/26/SSIS_3A00_-Storing-passwords.aspx

http://curionorg.blogspot.com/2007/05/encrypted-sql-server-ssis.html

|||I can understand why the SSIS team has left the password blank. My question is where should the password be entered.

I have entered the password in the package configuration file and BIDS does not read that password.

So it seems to be that once I have set the package security to DontSaveSenstive, I have to enter the password in config file AND in the empty text box in BIDS.

I don't understand why BIDS doesn't read the config file.

|||It does in my tests. Can you provide some more details about how the configuration file is set up and referenced from the package?

Configuration file reused by different packages

I have two packages share same configuration file. There are 4 connections in the first package and 2 connections in the second. Configuration file contains properties for 4 connections. First package opens in VS designer with no error. The problem with second package.

I get error that connection exist in the configuration but not in the package

and

"Could not copy file " Master_Configuration.dtsConfig" to the deployment utility output directory. Master_Configuration.dtsConfig' already exists."

Any help?

For the former error, I'd suggest just using one config per connection. It may seem annoying but over a large project it works quite well and offers flexibility. If you do have common connections in all packages, then you can group them, but even that is not necessary.

Not really used the deployment tool, so cannot comment on the second error.

|||

It is more convenient to use one (master) configuration file. I use dtexec utility to run packages in production. With many config files I would have to dynamically bild /ConfigFile parameter for each file for each package.

|||I would set the configurations at design-time. I think this is a much better option all round. It is easy to change development systems, and ensures you can use the ProtectionLevel DontSaveSensitive option even if you require passwords for connections without any problems. Using indirect configurations means you can handle changing paths for your config files as you move through environments.

Configuration File not always used

I have a simple SSIS package (stored in the file system) that gets a file path from a configuration file.The configuration type is an indirect XML configuration file that uses an environment variable to store the location of the configuration file.

When I run the package using dtexecui, or just dtexec from a command line, the package successfully picks up the file path from the configuration file (for verification I am writing out the variable containing the path to the log file).However when I run the package from a SQL Agent job it appears that the configuration file is not being used (the path is set to the same dummy path that I used during development).I have tried running the job as both a CmdExec and an Integration Services job and both fail on the same thing (invalid file path).

Both the SQL Server Agent service and the Integration Services service use a domain account as their start up account.This domain account has been included in the local administrators group on the server (in case it was having trouble accessing the environment variables).

What is the problem here – surely changing the way in which the package runs should not affect the configuration file settings!Any help would be appreciated…

Seems like it should work. Maybe your Security ProtectionLevel setting? It's not set to EncryptSensitiveWithUserKey, is it? And even so, I couldn't explain to you why that would cause the problem.|||

Are you using relative paths?

Sounds like you are.

Needs to be full path.

Can you show what the configuration values are?

|||The protection level on the package is set to DontSaveSensitive. I don't think that is the problem though because the configuration value is saved locally in an XML file not in a database...|||

The configuration value I am using is not a relative path, it is a full path i.e. "D:\LandingArea\Daily\". In my SSIS package I call a number of "child" SSIS packages which are all set as relative paths. I know that this causes trouble so I use the PackagePath that I get from the configuration file to set the relative path to a full path. As I mentioned I am writing out the PackagePath to the log file to verify its value, and when I run the package using dtexecui it writes the correct path to the log file, however when I run it from SQL agent I get the path that was hardcoded in the package during development.

|||

I did some more trouble-shooting for this issue. I created a small test package that has 1 variable and 1 Connection.The package has a script task in which it writes the values for both the variable value and connection string to a SQL log file – that is all that the package does (keeping it simple)...

What I discovered is that when using direct XML configuration files the package picks up the correct settings every time regardless of how the package gets executed.When using Indirect XML Configuration, the package picks up the correct configuration settings when running it using dtexecui or dtexec from the command line, however when running it from a SQL agent job (regardless of whether it is a CmdExec job or not) it ignores the configuration settings and uses whatever the last values were in development.The package does not seem to issue any warning message that it could not access the environment variable or configuration file!

Since I am logged on as local administrator and have system administrator rights on SQL I’m assuming that this is a bug and not by design!

|||

Flamin,

Just in case; Jamie has blogged about a special behavior (issue?) when uisng indirect configuration.

http://blogs.conchango.com/jamiethomson/archive/2005/10/31/2336.aspx

Rafael Salas

|||

I am sure that I logged off after creating the environment variables but just to be safe (and because I had the opportunity to) I rebooted the server - VOILA it all works now! Thanks Rafael!

Configuration File not always used

I have a simple SSIS package (stored in the file system) that gets a file path from a configuration file.The configuration type is an indirect XML configuration file that uses an environment variable to store the location of the configuration file.

When I run the package using dtexecui, or just dtexec from a command line, the package successfully picks up the file path from the configuration file (for verification I am writing out the variable containing the path to the log file).However when I run the package from a SQL Agent job it appears that the configuration file is not being used (the path is set to the same dummy path that I used during development).I have tried running the job as both a CmdExec and an Integration Services job and both fail on the same thing (invalid file path).

Both the SQL Server Agent service and the Integration Services service use a domain account as their start up account.This domain account has been included in the local administrators group on the server (in case it was having trouble accessing the environment variables).

What is the problem here – surely changing the way in which the package runs should not affect the configuration file settings!Any help would be appreciated…

Seems like it should work. Maybe your Security ProtectionLevel setting? It's not set to EncryptSensitiveWithUserKey, is it? And even so, I couldn't explain to you why that would cause the problem.|||

Are you using relative paths?

Sounds like you are.

Needs to be full path.

Can you show what the configuration values are?

|||The protection level on the package is set to DontSaveSensitive. I don't think that is the problem though because the configuration value is saved locally in an XML file not in a database...|||

The configuration value I am using is not a relative path, it is a full path i.e. "D:\LandingArea\Daily\". In my SSIS package I call a number of "child" SSIS packages which are all set as relative paths. I know that this causes trouble so I use the PackagePath that I get from the configuration file to set the relative path to a full path. As I mentioned I am writing out the PackagePath to the log file to verify its value, and when I run the package using dtexecui it writes the correct path to the log file, however when I run it from SQL agent I get the path that was hardcoded in the package during development.

|||

I did some more trouble-shooting for this issue. I created a small test package that has 1 variable and 1 Connection.The package has a script task in which it writes the values for both the variable value and connection string to a SQL log file – that is all that the package does (keeping it simple)...

What I discovered is that when using direct XML configuration files the package picks up the correct settings every time regardless of how the package gets executed.When using Indirect XML Configuration, the package picks up the correct configuration settings when running it using dtexecui or dtexec from the command line, however when running it from a SQL agent job (regardless of whether it is a CmdExec job or not) it ignores the configuration settings and uses whatever the last values were in development.The package does not seem to issue any warning message that it could not access the environment variable or configuration file!

Since I am logged on as local administrator and have system administrator rights on SQL I’m assuming that this is a bug and not by design!

|||

Flamin,

Just in case; Jamie has blogged about a special behavior (issue?) when uisng indirect configuration.

http://blogs.conchango.com/jamiethomson/archive/2005/10/31/2336.aspx

Rafael Salas

|||

I am sure that I logged off after creating the environment variables but just to be safe (and because I had the opportunity to) I rebooted the server - VOILA it all works now! Thanks Rafael!

sqlsql

Configuration File Locations - Likes to say it can't load them in designer

I have a package and added a configuration file to it to hold settings such as connection strings etc.

When I open the package up in the designer and check where the configuration file is from, it likes to specify it with a full path like this:

\\myserver\projects\packages\blah.xml

Where blah.xml is the configuration file and the package file is in the same exact directory.

This creates an issue when I use build and then try to install the package on a server. When installing, the server tries to read the config from \\myserver\projects\packages\blah.xml which it can not.

Conversely I've tried editing the location in the designer changing it to just be "blah.xml". Then after using the build option I am able to install the package successfully on the server (it can not find the xml file).

However when I then load the package later in the designer to try to edit or chagne it, the designer complains that the "configuration file could not be loaded" and it can't find it even though it is in the same directory that the package definition file is in.

What's going on with this and how do i solve it?

Unless you replicate your file and folder structure accross machines I think it is reasonable to expect a few moans when it cannot find stuff. One option is to use indirect configurations, which basically means you store the file location in an environment variable Plenty in Books Online, and Jamie talks about them here as well-

http://blogs.conchango.com/jamiethomson/archive/2005/11/02/2342.aspx

Configuration file location different in BIDS?

I've been working on package configurations here, mainly trying to figure out what works, and how. If I use an XML configuration, and and don't specify the path, ie just typing in test.dtsconfig, it creates the config file on my desktop. When I execute the packge in BIDS, it seems to be looking in that location. If however, I execute the package manually on the filesystem, test.dtsconfig must be in the same directory as the package (which is what I would expect). Why does this work differently in BIDS. Also, if the package is deployed to a SQL server, where would the configuration file need to be in this case? Thanks for the help.

Configuration file paths in SSIS are absolute, not relative. hence, use the full path when defining your file. i can't explain why behaviour is different between BIDS and whatever other method you are using to execute but to be honest that is irrelevant if you define the location with an absolute path.

If you don't like the thought of absolute paths then indirect configurations are what you want! http://blogs.conchango.com/jamiethomson/archive/2005/11/02/2342.aspx

Deployingn to SQL Server makes no differrence. you still need an absolute path.

-Jamie

Configuration file for dts package

Hi all,
I am building a DTS pakage to read from a text file and Populate a SQL
Server table. I am using the Enterprise manager to build the package.
I wanted to know if there is a way where I can specify information like the
path of the source text files, Login/pwd,server name of the detination
server in a separate "configuration file".
The reason I am looking for this is that I might need to move my packages to
different severs who may have different name and login/pwd. Also I dont want
to hadrcode the source file path in the package.
I think It is possible with UDL files but I dont know how.
any suggestions will be highly appreciated.
Cheers,
siajHi
Good stuff is here
www.sqldts.com
"siaj" <siaj@.discussions.microsoft.com> wrote in message
news:5FF4F79F-29D6-4B2E-99DD-9EB65724F768@.microsoft.com...
> Hi all,
> I am building a DTS pakage to read from a text file and Populate a SQL
> Server table. I am using the Enterprise manager to build the package.
> I wanted to know if there is a way where I can specify information like
the
> path of the source text files, Login/pwd,server name of the detination
> server in a separate "configuration file".
> The reason I am looking for this is that I might need to move my packages
to
> different severs who may have different name and login/pwd. Also I dont
want
> to hadrcode the source file path in the package.
>
> I think It is possible with UDL files but I dont know how.
> any suggestions will be highly appreciated.
> Cheers,
> siaj|||You can use a text file or an xml document to save this info. Open it from
your package a populate global variables.
AMB
"siaj" wrote:

> Hi all,
> I am building a DTS pakage to read from a text file and Populate a SQL
> Server table. I am using the Enterprise manager to build the package.
> I wanted to know if there is a way where I can specify information like th
e
> path of the source text files, Login/pwd,server name of the detination
> server in a separate "configuration file".
> The reason I am looking for this is that I might need to move my packages
to
> different severs who may have different name and login/pwd. Also I dont wa
nt
> to hadrcode the source file path in the package.
>
> I think It is possible with UDL files but I dont know how.
> any suggestions will be highly appreciated.
> Cheers,
> siaj|||I discussed some methods here in my article:
http://vyaskn.tripod.com/sql_server...t_practices.htm
You could make use of Dynamic Properties task, .ini files.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"siaj" <siaj@.discussions.microsoft.com> wrote in message
news:5FF4F79F-29D6-4B2E-99DD-9EB65724F768@.microsoft.com...
Hi all,
I am building a DTS pakage to read from a text file and Populate a SQL
Server table. I am using the Enterprise manager to build the package.
I wanted to know if there is a way where I can specify information like the
path of the source text files, Login/pwd,server name of the detination
server in a separate "configuration file".
The reason I am looking for this is that I might need to move my packages to
different severs who may have different name and login/pwd. Also I dont want
to hadrcode the source file path in the package.
I think It is possible with UDL files but I dont know how.
any suggestions will be highly appreciated.
Cheers,
siaj|||Thanks every body ...
These stff should help me.
siaj
"siaj" wrote:

> Hi all,
> I am building a DTS pakage to read from a text file and Populate a SQL
> Server table. I am using the Enterprise manager to build the package.
> I wanted to know if there is a way where I can specify information like th
e
> path of the source text files, Login/pwd,server name of the detination
> server in a separate "configuration file".
> The reason I am looking for this is that I might need to move my packages
to
> different severs who may have different name and login/pwd. Also I dont wa
nt
> to hadrcode the source file path in the package.
>
> I think It is possible with UDL files but I dont know how.
> any suggestions will be highly appreciated.
> Cheers,
> siaj

Configuration file for Connection Info (how do i set password)

Hi All,

I'm new to SSIS. I'm trying to build a simple package, run it on dev and migrate it to our stg and tst environments. It works on my dev environment great! It simply loads data from a flat file into a table. The trouble i'm having is exporting the connection string info to an xml file and runnign it on other environments. I am not storing the package on the db. I am saving to a file and running through SQL Server BI Development Studio.

I go to "Configuration File" and export InitialCatalog, ServerName, User, Password to the file. I can manually change the xml to the new environment but don't know how to set the password. It's not in the xml and i am constantly getting "Login failed for 'sa'" errors. Anyone know how to do this. I CAN get this whole process to work if i use Windows authentication security but this isn't an option. I need to use the db security.

Thanks,
Dave

check this out, http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=516292&SiteID=1

kushpaw

|||Thanks! I figured what i was doing wrong. I exported config to xml file. I select Username, Password, InitialCatalog, and Servername to export. Problem i was having was that i the actual password value was not being exported, only the xml tags. I had to manually go in and enter the password value to the xml. It works great now.|||

tenaciousd wrote:

Thanks! I figured what i was doing wrong. I exported config to xml file. I select Username, Password, InitialCatalog, and Servername to export. Problem i was having was that i the actual password value was not being exported, only the xml tags. I had to manually go in and enter the password value to the xml. It works great now.

I recommend you only store the ConnectionString property. That is an amalgamation of all the other properties that you listed there.

-Jamie

sqlsql

Configuration

Hi,

In my package I am using some 3 to 4 database connections. Hence I can say 3 to 4 connection managers I have. Other than that I have 2 Flat file connection manager.

What will be the best way to configure the same? and Please let me knowwhy that is the best choice.

Thanks

There is no "best" way. It depends on your environment and what works for you.

Personally I use XML configuration files. And I like to have a seperate configuration file for each connection manager. And I like to use indirect configurations.

but that's just me!

-Jamie

Config files get lost...

I created a project with multiple packages. I created a global

config file that all packages reference. I also created a config file

for each package that contains the package specific settings. I

checked it all into TFS. I went to a different machine and checked it

all out. Upon loading a package into the IDE I got the following 3

warnings.

Warning loading MT_LSE_PROD_StageLoad.dtsx: The

configuration file "MT_LSE_PROD_StageLoad.dtsConfig" cannot be found.

Check the directory and file name.

e:\contentloader\sprint1a\MT_LSE_PROD_StageLoad.dtsx

Warning

loading MT_LSE_PROD_StageLoad.dtsx: The configuration file

"Environment.dtsConfig" cannot be found. Check the directory and file

name. e:\contentloader\sprint1a\MT_LSE_PROD_StageLoad.dtsx

Warning

loading MT_LSE_PROD_StageLoad.dtsx: Failed to load at least one of the

configuration entries for the package. Check configurations entries and

previous warnings to see descriptions of which configuration failed.

e:\contentloader\sprint1a\MT_LSE_PROD_StageLoad.dtsx

Any

ideas? And yes, the files *ARE* there! I tried building the project

and it builds. I also tried doing a deployment build and that too

built. I am so confused!!!

StarPilot wrote:

I created a project with multiple packages. I created a global config file that all packages reference. I also created a config file for each package that contains the package specific settings. I checked it all into TFS. I went to a different machine and checked it all out. Upon loading a package into the IDE I got the following 3 warnings.

Warning loading MT_LSE_PROD_StageLoad.dtsx: The configuration file "MT_LSE_PROD_StageLoad.dtsConfig" cannot be found. Check the directory and file name. e:\contentloader\sprint1a\MT_LSE_PROD_StageLoad.dtsx

Warning loading MT_LSE_PROD_StageLoad.dtsx: The configuration file "Environment.dtsConfig" cannot be found. Check the directory and file name. e:\contentloader\sprint1a\MT_LSE_PROD_StageLoad.dtsx

Warning loading MT_LSE_PROD_StageLoad.dtsx: Failed to load at least one of the configuration entries for the package. Check configurations entries and previous warnings to see descriptions of which configuration failed. e:\contentloader\sprint1a\MT_LSE_PROD_StageLoad.dtsx

Any ideas? And yes, the files *ARE* there! I tried building the project and it builds. I also tried doing a deployment build and that too built. I am so confused!!!

Could it be a permissions issue? I assume the account running the packages has access to e:\contentloader\sprint1a\

-Jamie

|||Not a permission issue. I've granted every account access to the file system (one of the "joys" of being in a dev environment). Also, I see this behavior in VS.NET not SQL. I do a get latest or check out of the project on a brand new dev machine and it is VS.NET giving me the warnings. I compile (ignoring the warnings) and it works just fine. I turn deployment on and build and the deployment directory has the packages and config files. So, they are there and being used - it's VS.NET that's winpering over it...

Config file issue

I am using package configurations to store connectionstring for my database.

While moving the package from development to production, I used the same configuration files (without changing the connection string to point the database to production one). I ran the package in production and it updated the development database.

Then I changed the config files in production to point it to the production database.

However, the packages are still pointing to dev. database.

It seems that after running the package one in Production, it is not reading the updated config file for subsequent runs.

How to I make sure that the packages now read the latest config files.

Did you specify a full path to the config file?|||

Yes, generally with other packages I just update the database name and it works.

Here I forgot to update config file and ran the package in production. Now even if I correct the config file it still uses the old database connection.

|||In the package configurations dialog (wizard), did you specify a FULL PATH to the config file (eg., c:\dir\subdir\YourConfigFile.dtsConfig), or not?|||Yes Phil I have mentioned the full path.|||Do you have any errors when running the package (do you have package logging turned on? If not, do it) that claim the package cannot find the configuration file?

Does SSIS have access to the location that the config file is located?|||And how are you running the packages in production?|||I am not getting any errors. The package runs fine but updates the development database which was in the old config file instead of the production one specified in the config file now.|||Again, how are you executing the package in production?|||We run the package by double clicking on the package file and pushing the execute button in production.|||When you do that, you are executing the package from your machine, not production. What version of SSIS do you have installed on your desktop?

You might want to setup a SQL Server Agent job to run the package instead.|||

Hi We have also deployed the package and run it from SQL server management studio but it still points to the old development database....

Our SQL management studio version is :

Microsoft SQL Server Management Studio 9.00.1399.00
Microsoft Analysis Services Client Tools 2005.090.1399.00
Microsoft Data Access Components (MDAC) 2000.085.1117.00 (xpsp_sp2_rtm.040803-2158)
Microsoft MSXML 2.6 3.0 4.0 5.0 6.0
Microsoft Internet Explorer 6.0.2900.2180
Microsoft .NET Framework 2.0.50727.42
Operating System 5.1.2600

|||Right, but what version of SQL Server do you have installed on your (developer) desktop? Standard? Enterprise? Developer?

Setup the package under SQL Server Agent, and then try it.

Just because it is viewed inside SQL Server Management Studio doesn't mean that it is actually EXECUTING there and on the server you are connected to.|||

Hi,

This issue was resolved. The package had some bug but now its working fine.

Thanks you for your support.

Config Error (Oracle 10g Source)

Hello All

I was trying to configure my package. I have a Oracle 10g DB as source. I used "Oracle provider for OLE Db" as provider in connection manager. I entered user name and password.

When I set my package protection level to : DontSaveSensitive and executed the package

it gives me this error:

[Connection manager "WDEV.wuser1"] Error: An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "OraOLEDB" Hresult: 0x80004005 Description: "ORA-01005: null password given; logon denied".

When i go back to connection manager I see the blank password. I retype it but again when I execute I get the above error.

I configured the package with same setting and in the config file I mentioned the passwords and all details but again I when I execute the package i get the below error:

[Connection manager "WDEV.wuser1"] Error: An OLE DB error has occurred. Error code: 0x80040E21. An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".

Since it was not storing password OR not executing without password I set the Protection Level to : "EncryptAllWithPassword"

and excute it I works Great. and runs Awesome. Except I get this Warning

[OLE DB Source [1]] Warning: Cannot retrieve the column code page info from the OLE DB provider. If the component supports the "DefaultCodePage" property, the code page from that property will be used. Change the value of the property if the current string code page values are incorrect. If the component does not support the property, the code page from the component's locale ID will be used.

Question 1: "CAN I IGNORE THIS WARNING?'

Question2: "THE PACKAGE RUNS GOOD WHEN I SET THE PROTECTIONLEVEL =EncryptAllWithPassword. With this settings when i configure my package it gives me error. Why is it so Any Help please Below plz find the config file?"

[OLE DB Source [1]] Error: The AcquireConnection method call to the connection manager "WDEV.wuser1" failed with error code 0xC0202009.

[Connection manager "WDEV.wuser1"] Error: An OLE DB error has occurred. Error code: 0x80040E21. An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".

x<?xml version="1.0" ?>

- <DTSConfiguration>

- <DTSConfigurationHeading>

<DTSConfigurationFileInfo GeneratedBy="AWM" GeneratedFromPackageName="Package1" GeneratedFromPackageID="{289810A-ASD0-4698-BAC5-3270D3E5D17D}" GeneratedDate="9/9/2007 10:18:10 PM" />

</DTSConfigurationHeading>

- <Configuration ConfiguredType="Property" Path="\Package.Connections[WDEV.wuser1].Properties[ConnectionString]" ValueType="String">

<ConfiguredValue>Provider=OraOLEDB.Oracle.1;Data Source=WDEV;User ID=wuser;Password:XYZ;</ConfiguredValue>

</Configuration>

- <Configuration ConfiguredType="Property" Path="\Package.Connections[WDEV.ccuser1].Properties[InitialCatalog]" ValueType="String">

<ConfiguredValue />

</Configuration>

- <Configuration ConfiguredType="Property" Path="\Package.Connections[WDEV.wuser1].Properties[Name]" ValueType="String">

<ConfiguredValue>WDEV.wuser1</ConfiguredValue>

</Configuration>

- <Configuration ConfiguredType="Property" Path="\Package.Connections[WDEV.wuser1].Properties[Password]" ValueType="String">

XYZ<ConfiguredValue />

</Configuration>

- <Configuration ConfiguredType="Property" Path="\Package.Connections[WDEV.wuser1].Properties[ServerName]" ValueType="String">

<ConfiguredValue>WDEV</ConfiguredValue>

</Configuration>

- <Configuration ConfiguredType="Property" Path="\Package.Connections[WDEV.wuser1].Properties[UserName]" ValueType="String">

<ConfiguredValue>wuser</ConfiguredValue>

</Configuration>

- <Configuration ConfiguredType="Property" Path="\Package.Properties[PackagePassword]" ValueType="String">

<ConfiguredValue /> AWM

</Configuration>

</DTSConfiguration>

Thanks a lotsI had a similar problem with a pair of SSIS running with Oracle. I put "Rely on server storage and.."|||I can help with question number 2.

By default SSIS do not stored any sensitive information when the package is saved. That is given by the ProtectionLevel property you find at the package level. You can use a combination of 'package configurations' and/or protection level value to set connection strings and any other dynamic/sensitive data at execution time. This KB article shows 5 methods to get around the issue you are having; I personally used the #4:

http://support.microsoft.com/kb/918760

I cannot help with the #1 as I haven't worked with SSIS and Oracle at all.

config creating havoc

I have created two package configurations

Each identical apart from the name (dev/prod) [They appear in that order in the 'configurations..' dialog]

I then manually edited the prod config (in notepad) to the correct values (connection string etc)

I then opened the config file in the config edit tool (some time later) to note that my changes had somehow been overwritten. I changed them back to the correct values and saved the prod config.

I then tried building the package.

It returned with numerous validation errors despite 'delay validation' being set to true on each of the tasks that were causing problems (they create temp tables which it cannot validate against)

I then tried running the package.

It errored.

It was trying to connect to production!!

How on earth is this possible?
According to the configuration dialog, the 'dev' configuration is the first one loaded.
I haven't told it anywhere to use 'prod'.
I haven't even (within ssis) managed to even change the values (connection strings) to production.

What is going on here?It would appear that subsequent entries in the 'package configurations organizer' overwrite initials ones.

e.g.
config1 value1=abc
config2 value1=def

the package would have value1=def

so, what exactly is the point of being able to load multiple configurations if it is just going to pick up the last one regardless?|||

The point of superceding configured properties, so far as I use it, to go from generalization to specialization.

A given configuration might be shared by 50 packages. However, for one or two of those packages, the configured value is not appropriate, must be specifialized, and hence, an "subclassed" configuration is added, which overrides the base, or precediing configuration.

|||ok, I can see how that could work but...

If I have a package and want to store the connection string for dev/test/prod in the same package, how can I then test this in VS.net?

Other than manually adding and deleting them each time?

also am getting the following error when trying to build:
Creating deployment utility...
Error : System.ApplicationException: Could not copy file "xxxx.dtsConfig" to the deployment utility output directory "yyyyyyy". > System.IO.IOException: The file 'xxxxxx' already exists.

I cannot see any kind of 'overwrite' flag in the project - deploy properties (like you get in reporting services)|||do not add config files into the misc folder in the project - this was causing the 'already exists' error

It obviously decides to deploy all the misc files. How stupid of me to include config files in this folder. They are only completely 100% relevant to the project after all.|||

You don't need to build anything, though. For that matter, deploying is a bit of a stretch as well. The .dtsx and .dtsconfig files are all you need. You can simply copy them to the server via a mapped drive, or use the import (or for that matter, Save Copy As... in BIDS) in Management Studio when connected to Integration Services if you prefer to store packages in MSDB.

As to your question as to dev, test, and prod, simply create three config files, name them slightly differently than each other. Then point your package to an environment-neutral named config file. To test, simply copy one of your dev, test, or prod config files (probably starting with dev) to the name that you have the package pointing to. When you want to switch environments, repeat the process using a different config file this time (perhaps test's file)

Conditionally extraction of data from a view

Hi all,
I am really new to SSIS, so may be this is a really simple question, but I couldnt find an answer yet.
I need to build a package that
1) counts the rows from a view
2) if rowcount >0 extracts the data into a file

I tryed to do this using a Row Count Transformation in the data flow, but after putting the count in a variable I am not able to perform the "conditional" phase two.
I mean that I want to check the value of the variable, but cannot figure out how to conditionally execute the flat file extraction.

Using Row Count, I have to build 2 data flow tasks.
Is there a way to do this in a single data flow?
May be using an Execute SQL Task instead of row count?

Any suggestions will ge greately appreciated
IgorBUse an execute SQL task on the control flow to perform your select count(*). Then hook that task up to your data flow with an expression (for the precedence constraint) that takes the count results from the Execute SQL task and checks it for > 0.|||

Phil Brammer wrote:

Use an execute SQL task on the control flow to perform your select count(*). Then hook that task up to your data flow with an expression (for the precedence constraint) that takes the count results from the Execute SQL task and checks it for > 0.

Can you please be more detailed?
I create a SQL Task with the select count(*).
how do i set the result set? as single row? and put the result into a variable?

Where do I check the result value?|||Single row result set. Set it to a user variable.

When you connect the Execute SQL task to the data flow, double click on the green connector line. In there set the Evaluation operation to "Expression and Constraint". Set the value to Success and the Expression to: @.Your_variable > 0|||

Phil Brammer wrote:

Single row result set. Set it to a user variable.

I cannot make this work... I get an Error 0xC002F309 when executing the step...

I create a new SQL Task, change the result set to "Single Value" and build the query in the SQL Statement (the query is simply SELECT COUNT(*) FROM RNFF_VIEW).
The view is on an Oracle DB and connect to it using OLE DB.
If I execute the query in query builder I get the correct result (rows = 10)

Then I go on the "Result set" pane of the SQL Task, Add a new line: in the "result name" I put a 0 and in the variable I select new variable, give the variable a name, leave the user namespace, and select a value type of Int32.

then I execute the task, and get an error:
Execute SQL Task: An error occurred while assigning a value to variable "Pippo": "Unsupported data type on result set binding 0.".

I tryed the same on a connection to SQL 2005 and there I have no errors... may be it has to do with the Oracle Provider?|||Finally I make it work!
On an Oracle Database, the result for a SELECT COUNT(*) FROM TABLE returns a data type of NUMERIC.
Looking at the "Integration Services Data Types" I found that I have to map it to a variable of a data type DT_NUMERIC.
Unfortunately this data type doesn't exist...

I make it work converting the COUNT(*) to a FLOAT and using a variable of DT_R8 (that maps to a variable of type Double).

I think that this is a bug of the SQL Server Business Intelligence Studio interface (the type Decimal doesn't appear in the interface).
May be assigning the type programmatically will work, but I am not able to do so...

Thanks Phil for your help!