blogs.conchango.com

Hello,
Are you using the same configuration file for several packages? I am using one for all of my packages in one project, containing a AppRoot variable and two connectionstrings. This has been working fine when I debug however when I want to build a deployment utility I get an error stating that the file allready exist(in the bin catalogue), as if it is trying to create a new file each time...
Do you know of this problem or do you have an idea about it?

Sincerely,

Jamie,

Have had great success using indirect configurations while in BI Development Studio. Everything works as intended. However now that I've deployed my packages, SQL Server Management Studio has me stumped. When I call packages, or schedule them in a job, the package properties aren't being updated at execution-time by the configurations. One interesting note is that Management Studio appears to be using the environment variables of the local machine, not the server where the package is located. Putting environment variables aside though, I haven't found a way to make Management Studio cooperate with indirect configurations. Thoughts?

Brian

Jamie,

I've used the Deployment Utility in BI Studio to build the packages on a file server. From there, I've tried 2 methods:

1) Double-clicked on the Deployment Manifest file on the file server.
* SQL Server deployment
* ServerName = <SQL 2005 server name>
* Rely on server storage for encryption
* Accepted the default location for dependencies (a local drive; not sure the impact this is having)

2) In Management Studio, right-clicked on Stored Packages-File System, and selected "Import Package." Then chose the appropriate options to point to the package location.

In all cases, I can right-click on my package in Management Studio, choose "Run Package", and bring up the Execute Package Utility. What's interesting is that under Connection Managers, I can see all my package connection strings. Whatever values are present are what the package will use at runtime, despite the indirect configurations I've created. The package does run successfully. But with bad connection strings, values, etc.

If you're interested, can provide you with more detailed information off-forums.

Thanks,

Brian
pondbl@hotmail.com
-------------------

Configuring packages based on configuration files is great, whats more you can have the location of configuration...

We are using them in our solutions. It's everything you ever dreamed of in DTS :-)

Hi,

I m using a configuration file for running my SSIS package. Once I update the connection string for ODBC in the configuration file to use another server, and try to run the package it gives validation errors saying system.byte[] for each column did not match. Do you want to fix this automatically? Once I say Yes, the pacakge will run. Is it that we need to always fix the metadata when the configuration file is changed?

Thanks in advance
Srividya

Configuring packages based on configuration files is great, whats more you can have the location of configuration...

I have spoken previously about indirect configurations and tried to convince everyone of how useful they...

For reasons that I'll save until another post, I always deploy my packages as files rather than to SQL...

I've been searching around for info on SSIS and it is scarce! I've emailed quite a few...

I have spoken previously about indirect configurations and tried to convince everyone of how useful they

I haven't yet tried using environment variables to store config file paths, but sounds like a good idea.  Thus far, I've standardized paths across our DEV, staging & production environments and it makes deploying new packages pretty simple.

At the moment, I have one config file for most of my SQL connections for a given project.  I just created a new package that requires a new ADO.Net connection, so I stored that connections connect string in the same file.  So what I HATE (or haven't found a simple workaround for) is that when the dozen other packages that use this same config file attempt to load it, they throw an error b/c they don't contain this connection!  Arrrgg!!    (actually 2 warnings and an error)

It's starting to look like I'll need a seperate config file for each DB connection, then each package will selectively use only the ones it needs.  I was hoping to find a way to tell the package to "ignore extra objects", but I don't think such an option exists.

Of course, using environment variables might make this simpler if I start using 'em with this new package.

(oops, BTW... ignore that bit of confusion about storing file paths in the environment variables.  I figured out it was one variable per, umm, variable)

:)

Althought the new SSIS Configuration is of great help when promoting files across environments, it's extremely cumbersome. Like some people in this blog, I keep on experiencing the problem of the dozens of errors/warning being thrown if a package does not contain something specified in the config file.

There should be some some type of tool that will include these automatically when the config is first specified on a package.

I find it extrmely frustrating to have to baby-sit these settings so closely or else it turns out to be a complete mess. I think it's probably better just to have a config file for each package instead of the "ideal" of a common config.

Does anyone have any tips that the can share on this?

To answer my own problem above, you have to set the SupressConfigurationWarnings property to True. This will eliminate all of the warnings/errors and let you get on with your life, but it doesn't really solve your configuration problems, just hides them for you.

So you must still be very careful when sharing the same config settings across packages and double-check, constantly, that your packages are utilizing the correct values, etc.

Someone recently left a comment on my blog about Package Template Locations asking if I could share my

Hi Jamie,

I'm currently using indirect configurations.  My set up is this - I use a registry key to point to the SQL Server(the connection string to the sql server) that has the connection strings in it for my connection managers.  I started out manually adding my connection managers to each package, but then realized I kept adding the same connections.  So I added the connections I kept adding as data sources to my solution so each time all I would have to do is add the connections from there.  What I have discovered is that when I go to open/run the packages where I use the data source connections, the configurations aren't being applied or the data source connection string is overriding the indirect configuration string I have saved in sql server.  Where as the packages where I manually added each connection seperatly, they pick up the configuration changes just fine. Should the connection string stored in the data source override the connection string applied from my indirect configuration?  Is there a way to override that if this is the expected behavior?

Thanks for reading.

where do u set the SupressConfigurationWarnings property  to true

I have a SSIS package with a Configuration value reading a config file from environment variable. In development environment it is able to find the environment variable. But Once i deploy on server machine. Manually create the Environment variable. Deploy the package and execute as a job in sql agent job account. The package fails stating cound not find the environment variable. When i went and saw, environment variable is still there. What is wrong with it.

I tried to debug using running dtexec

When I run the package using "dtexec /sql package /server server " sqldumper comes and i am not able to debug it.

do you need two configurations (one to tell it where the database is?) when you deploy from one server to the next? I'm using the ssis.Config table for the package configurations... However when I put the package on another server it doesn't even look at the connection info in the table.

Brian/lauriebgori,

I am encountering a similar problem to you.  

I have two data connections that i use throughout a number of packages.  Their connection strings are stored with a configuration table (SQL Server) and the config connection is derived from an environment variable.

The problem is, at runtime the environment variable is allocated fine but the subsequent connection strings are not and are left blank.

Variables also using the config store are assigned fine but the conn strings do not????  

Anyone got a solution for this problem?

Ta

flurk

Why would use use indirect configurations.

It's far simpler (and more managable) to set the configurations as parameters of SQL Agent step (as described here: http://ssis.trigonblue.com/IndirectConfigurations/tabid/526/Default.aspx)

Jamie,

 Thanks for pointing me to the dtexec.... How do I use that /conn option. I already have a connection in the connection manager that points to the configuartion database.

 Here's the problem:

   1) Create BIDS solution on Development machine. Test and works

   2) Create manifest on remote machine. Deploy..

   3) Check the DB where the configuration is.. SSIS.Config Table (Default table name)

  4) The package is in the msdb db.

  5) RUN package (through the SMSMS)

  6) It fails because it can't connect

  7) I can manually edit the Configuration string (just put password=whatever) then run and it works just fine. I just can't save it...

So am I just thinking about this wrong? do I have to run these packages using DTEXEC?

I ran this in a command prompt:

DTexecui C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Deployment\Deployment\ConnectionTest-DEV 5-24.dtxs

It pulls up the same thing as when you run a package in SSMS. "Execute Package Utility".

What else would I need to do for it to see the Configurations that are stored in a DB table?

You are very patient thanks for that. It seems this should be so much easier...I'm just missing something here...

By the way. The command-line tab is very helpful now. So that's what I'd use when I create a job for this right...

  Back to dtexecui.... I can go to the Configuration tab and load up a config file and run it.. Still get the same error. The only config that can go here is the one built from the XML?

 The only time this runs succesfully is when I edit the Connection Managers, Connection String.

Maybe it's just a question of the config file being wrong? I built it from BIDS (visual studio) maybe I have to edit it more?

I think you do understand the problem. .....

Is this the correct format to use /conn? Or am I way off base...

I ran this in SSMS...

exec master.dbo.xp_cmdshell 'dtexec /f "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Deployment\ConnectionTest-DEV 5-24.dtsx" /conn "Data Source=100.200.150.100; user id=sa; password=whatever;Initial Catalog=SQLAdmin;Provider=SQLNCLI.1; persist Security Info=True;"'

Why does it run so well on my local machine? It must save the configurations locally?

I think this may have something to do with the protection level? That's why when I export it, it doesn't work?

YES! that was it.. I changed the Protection level and ran it. Finally success.

Thanks again for your advice and more importantly your patience... BTW ...I got the Protection info from  you where you posted to someone else on a different site.

i've been trying to decide the best ssis package deployment strategy and really liked the idea of file system because I have dev/qa/prod boxes and would like to deploy package thru different environment w/o opening the packages and make changes. however, i wish someone can show me a tutorial with end to end example.  a couple of problems i ran into are:

1. when design a package, don't you need to specify connections for connection manager? (server name, db name and so on),  and the name was saved in connection manager, such as DevServer.db1 and so on, so how can i change that when it was promoted to qa and prod?

2. if developer only gave me the package file, a dtsx file,  (not a solution file) i can't use the Deployment Utility to create the configuration file for it. so what should I do with the package?

3. I read Kirk Haselden’s book but he didn’t provide an end to end example for deploying package either (he mentioned different techniques), same as most postings I saw so far.

4. my dev and qa are two instances on a box, but intergration service is not instance aware, so how should I promote a pakcage thru dev/qa path? Since there is only one place to put package as file system right?

Try editing the Config Files manually if you are using a file anyways. Open it up in XML notepad (or notepad if you like to be dizzy). I found that even after setting the protection level on the packages to encrypt sensitive dat using a password (now it prompts me for pass when I open in BI studio), It still does not save passwords in the config file. Security..great, but now the package won't run. Anyways, I hacked away at the config files and hard coded all the settings after I extracted them in the SSIS config manager. I will theoretically just set folder permissions to only allow access to the config folder to the accutn that runs packages.

I also noticed that even though Biz Intel Studio recognizes wierd connection string variables in ODBC drivers, such as PWD (password in a datadirect 400 driver) it will throw password=xxx; into the connection string instead of password. So hard code the whole DSN and it seems to work better.

I am going on round 3 with configs and have gotten a bit further from these helpful posts. Helpful, not configs are easy and you are a tard posts.

Oh yeah, I have found that after exporting configs from the config manager, you will need to close and open the project one or 2 times, and possibly set DelayValidation for the connections to true.

Moving on to using 2 config files in 1 package. Wish me luck.

Also, is there any good ways to see what server your connection strings are pointed to as the package runs? Right now I am just looking for table activity (0-1.2 mil rows)

This is a big thread started ALONG time ago... I'm hoping you still read this one.

I don't know if what I'm doing falls into "Indirect Configurations" but I'll explain what I am doing and the problem I'm experiencing all in the hopes that you have the answer that I can't seem to find anywhere.

I've build my package, set package configurations and added my SMTPServer. Now I've tried it a few different ways but the gist of what I'm tryng to do is be able to (based on package configurations) set the SMTPServer.

Seems to work in the development environment, kindof. I actually have to close and re-open the package in order to see the configuration change. But my question more relates to my production environment and Schedule Jobs against the IS package. It only seems to remember the last time I compiled it. Now if I look in the data sources section of my job's step, I see that the configuration value has been applied. Set that checkbox, click ok, ok. Run the job and execution is appropriate (in the first instance I set it to an invalid IP address). Then I change the configuration file to a valid IP address, and then try to run the job. It still fails, obviously not picking up the Config file change.

I know, some of this is a bit of a ramble...

Please Help.

Scotty

Indirect hmm... I shyed away from it in the end for a central SQL Server option.

I say be careful when using environment variables especially on a clustered environment as IT or DBA's will often throw it back at you. Having to create environment variables on each node of a cluster is a pain in the harass if you are using multiple files, and often will not comply with technical policies.

Direct gives you a wonderful chance to save the file in a conventionised location and update the values during deployment, far simpler than copying the file to set locations and updating the file with XML editors.

SQL server option allows for you to create a fabulous Data Integrator style web form to keep them updated.

If you are tired of the complicated configuration model in SSIS, you may try an easier alternative: http://cozyroc.wordpress.com/2007/11/05/even-easier-ssis-package-configuration/

Steve's item 4 Multiple dts package stores on one machine is easy thanks to:

http://bloggingabout.net/blogs/mglaser/archive/2007/03/01/multiple-sql-server-integration-services-ssis-database-instances-on-one-machine.aspx

I'm struggling with the package config files though. Dev and Test instances are on same machine so I have two package stores set up Ok and I want to put config files there (or in msdb for each instance) but how to point to them correctly other than through job configuration? I tried leaving out the file path in a direct (so same place as package) but it just made a hash of creating the config.

Environment vars are no good as there's only one for the two instances and I need seperate for dev and test. Likewise hard coded file paths or pointing to msdb as you have to say which server and again I need two!

We have packages which need to be run on an ad-hoc basis (when a data import arrives for example) and this currently gets done from Enterprise Manager for 2000 so would be from Management Studio when converted. Other than setting them all up as jobs, I'm stumped.

Can someone please post a example of a package XML configuration file with an Oracle connection?  No matter where I edit the password, my connection still fails to the Oracle database with the package configuration file attached to the package.

Thanks,

Jason Nance

Error I am receiving in job history:

12/19/2007 12:59:35,Glfin Test,Error,1,MCHSHYP02,Glfin Test,Glfin Test SSIS package with configuration file,,Executed as user: MCHSNT\hypsql01. ...on 9.00.3042.00 for 32-bit  Copyright (C) Microsoft Corp 1984-2005. All rights reserved.    Started:  12:59:35 PM  Error: 2007-12-19 12:59:38.03     Code: 0xC0202009     Source: Glfin Test Connection manager "Lawson"     Description: SSIS Error Code DTS_E_OLEDBERROR.  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<c/> if available. No work was done.".  End Error  Error: 2007-12-19 12:59:38.03     Code: 0xC0202009     Source: Glfin Test Connection manager "Lawson"     Description: SSIS Error Code DTS_E_OLEDBERROR.  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 error...  The package execution fa...  The step failed.,00:00:03,0,0,,,,0

My XML File

<?xml version="1.0"?><DTSConfiguration><DTSConfigurationHeading><DTSConfigurationFileInfo GeneratedBy="MCHSHYP02\lanadmin" GeneratedFromPackageName="Glfin Test" GeneratedFromPackageID="{E9DA628B-116D-4C55-9F30-3B56D4E6EBAC}" GeneratedDate="12/19/2007 12:49:22 PM"/></DTSConfigurationHeading><Configuration ConfiguredType="Property" Path="\Package.Connections[Glfin Stage #1].Properties[ConnectionString]" ValueType="String"><ConfiguredValue>Data Source=Mchshyp02;User ID=;Initial Catalog=Glfin Stage;Provider=SQLOLEDB.1;Integrated Security=SSPI;</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Connections[Glfin Stage #1].Properties[Description]" ValueType="String"><ConfiguredValue></ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Connections[Glfin Stage #1].Properties[InitialCatalog]" ValueType="String"><ConfiguredValue>Glfin Stage</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Connections[Glfin Stage #1].Properties[Name]" ValueType="String"><ConfiguredValue>Glfin Stage #1</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Connections[Glfin Stage #1].Properties[Password]" ValueType="String"><ConfiguredValue></ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Connections[Glfin Stage #1].Properties[ProtectionLevel]" ValueType="Int32"><ConfiguredValue>1</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Connections[Glfin Stage #1].Properties[RetainSameConnection]" ValueType="Boolean"><ConfiguredValue>0</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Connections[Glfin Stage #1].Properties[ServerName]" ValueType="String"><ConfiguredValue>Mchshyp02</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Connections[Glfin Stage #1].Properties[UserName]" ValueType="String"><ConfiguredValue></ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Connections[Lawson].Properties[ConnectionString]" ValueType="String"><ConfiguredValue>Data Source=lawfnc;User ID=rptdev;Provider=MSDAORA.1;Persist Security Info=True;</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Connections[Lawson].Properties[Description]" ValueType="String"><ConfiguredValue></ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Connections[Lawson].Properties[InitialCatalog]" ValueType="String"><ConfiguredValue></ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Connections[Lawson].Properties[Name]" ValueType="String"><ConfiguredValue>Lawson</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Connections[Lawson].Properties[Password]" ValueType="String"><ConfiguredValue>mmartin</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Connections[Lawson].Properties[ProtectionLevel]" ValueType="Int32"><ConfiguredValue>1</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Connections[Lawson].Properties[RetainSameConnection]" ValueType="Boolean"><ConfiguredValue>0</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Connections[Lawson].Properties[ServerName]" ValueType="String"><ConfiguredValue>lawfnc</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Connections[Lawson].Properties[UserName]" ValueType="String"><ConfiguredValue>rptdev</ConfiguredValue></Configuration></DTSConfiguration>

PingBack from http://blogs.conchango.com/jamiethomson/archive/2006/01/05/SSIS_3A00_-Common-folder-structure.aspx

We have been using environment variables for the xml config files for the past 12 months. However, it looks like we will be switching to using file names. File names will let us specify relative file names. This allows us to have more than one copy of the solution in use at the same time.

Our goal is to be able to run tests on the packages while still be able to work on other packages. An environtment varible would limit us to running one package (or set of packages) at a time. By using relative file names, we can start a test load with dtexec and continue to develop in BIDS.

Well for some strange reason relative paths to the configuration files work sometimes (which is why I thought it would work at all), but most of the time they don't work.

So here is how I ended up forcing SSIS to use relative paths. The first thing to note is that the packages are all stored on the filesystem, not SQL Server. Then I execute the packages with a BAT file like this:

SET MY_CONFIG=%~dp0config\my.dtsConfig

dtexec /FILE "dw\Master\Master.dtsx" <insert other dtexec options here>

Then I use the environment variable MY_CONFIG as my ssis package configuration.

This will make the running package look at the config subfolder in whatever folder the BAT file is in.

It doesn't work in BIDS because the BAT file isn't run. However, I can just set the same environment variable to whatever values I want to use in development.

Hi Jamie ,

       I am trying to implement indirect configurations in the packages. I have my configurations stored in a table. I need to add the path to the config table in a config file (xml) instead of an environment variable. At runtime i need my package to read the config file to get the path to the table where the configurations are stored.

   I need to know how can i store the config table information in another config file.

Iam sorry ,I had posted that one earlier , but thought this was a better place .. but was not able to delete the previous one ....  anyways thanks for your help .. you ROCK !!!

Hello jamie,

         Is is possible to add a template project under business intelligence in visual studio 2005?

Regards,

Ruby Agarwal

Hope you're still monitoring this popular post Jamie!

My issue is with using configurations to set logging properties - particularly for logging to SQL. I'm aware of the problems with doing this described here http://weblogs.sqlteam.com/dmauri/archive/2006/04/02/9489.aspx amongst other places, but cannot get any type of configuration to work. This is despite the fact that it is suggested that environment variables should work. This is the sort of error I get when running it from BIDS, even when using environment variables:

Error at Master [Connection manager "<Connection Manager Name>"]: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.

An OLE DB record is available.  Source: "Microsoft SQL Native Client"  Hresult: 0x80004005  Description: "Login timeout expired".

Since it blows up before reading_any_configurations I can't see what good using environment variables would be. Usually I end up using server aliases in the sql client network utility and hardcoding the connection. Which at least gives some flexibility.

Any thoughts on this?

Just answered my own question. You have to set the connection string property directly from the environment variable. I usually set properties using package variables and set the package variables from configurations, because this prevents changes in the package breaking the configuration mapping. However in the case of logging config I guess this isn't an issue.