Configuration Changes

We store our configurations in a SQL Server configuration table. We have SSIS packages that can be used in different senarios. Each of these senarios requires different configuration values.

Is it possible to change the SSIS configuration key used for a package at run time? i.e. we'd like to be able to use a job that says run this SSIS package but use configuration values with that name.

I think this is possible, yeah. If your packages need to be executed in different scenarios then you effectively need to "parameterise" those packages. Is that correct?

If so, you should lookat using the /SET option of dtexec.exe. It is provided for just this situation.

Configurations aren't really what you after. They are more about making your packages location independent, whereas it seems to me that you want to parameterise your packages.

Good luck with it.

-Jamie

|||

Thanks. We'll have to think through this a bit. *If* we could change the filter at package invocation then configurations would fit our needs perfectly. We'll probabbly look for a way to set several SSIS variables from a SQL table and use a /SET to specify which set of values to populate variables from.

I think we could pull it off with configurations using multiple tables, but I'm not fond of that either. I guess it's rough to be picky. :)

|||That's the point of configuration tables in SQL Server though -- to make things flexible...

Perhaps you could store your multiple configurations in a table and then with a stored procedure or something, update the SSIS Configuration table with the appropriate values.|||

We actually do that for sub packages, but it doesn't seem appropriate here. If I update the single set of SSIS values from two different jobs at close to the same time, it's likely one of them will run with the wrong values. Tracking that down seems like more effort than I'd like to spend if it occured.

My colleague had an idea we're investigating. Using environment values. We're thinking that perhaps by creating a batch file that sets a value on the fly and then issues a dtexec that we might achieve the result we want without putting values permanently into the evironment or exposing sensitive data.

|||

Ok, I think we have it working. We created a batch file called SSISWithConfiguration.bat:
SET env="Configuration";"[dbo].[SSIS Configurations]";%2
dtexec /DTS %1

In the job we just pass the path to the SSIS package and the Configuration filter values we want to use. The environment variables will be separate for each invocation and we only have one batch file to deploy ever. (We always call a configuration connection called Configuration). Execution looks like:
C:\MSSQL\SSISWithConfiguration.bat "\MSDB\folder\packageName" "New filter name"

In the package we just create an indirect SQL Server configuration with the environment variable as env.

During development we can use configuration settings directly and then when ready to deploy a reusable package switch to indirect (that way we don't have to have permanent environment variables on the developers desktops and the configuration table in development will get updated).

Finally, in the package, we have an XML file configuration listed first to move the Configuration connection between development, test, and production. This gets deployed once on each developers box and once on each server.

The last piece we wrote was a small utility that will let us pick a configuration setting and will write the SQL script to insert the configuration settings after deleting any existing values. We save that three times and change the values for development, test, and production.

Thanks for all the ideas and support. Sometimes it's just looking at stuff a bit different :)

Configuration Approach

Background

I use four database connections strings.

I have about 30 packages that will use one or more of the connection stings.

I store the connection strings in XML configuration files.

I know that I can share configuration files across packages. Should I ?

Have one configuration file with all 4 connection strings? If I use this approach will I get errors in the packages that only use 1 of the connection strings OR

Create four separate configuration files (CnnString1.dtsconfig, CnnString2.dtsconfig, CnnString3.dtsconfig, CnnString4.dtsconfig) and use the appropriate ones for each package

Take another approach

Check this thread, It discusses the similar kind of issue.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1853477&SiteID=1

Thanks

|||For me, I just create all of the connection managers that are in the config file across all packages for a given project. It's a pain to setup, but it works for me.|||

That is the pain I am striving to avoid by using a modular approach. I know that having a configuration file for each package works but it is not reusable. That can cause a maintenance nightmare when moving 30+ packages between Dev, QA & Prod environments.

I have a requirement to use XML Configuration files. Returning to the original question, should I use 1 file with all database connections or should I use 4 files with a connection in each?

|||

Danny Crowell wrote:

That is the pain I am striving to avoid by using a modular approach. I know that having a configuration file for each package works but it is not reusable. That can cause a maintenance nightmare when moving 30+ packages between Dev, QA & Prod environments.

I have a requirement to use XML Configuration files. Returning to the original question, should I use 1 file with all database connections or should I use 4 files with a connection in each?

If it's such a pain, then use a configuration file for each connection manager object. Then each package only uses what it needs.

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 file - read connection string

I have Custom.dll for retriving data from database. I don't want to store
connection string in class. Is it possible to store in some RS config file or
i have to use XML file and read from it?
Thx.Yes, create an appSettings element in the RSReportDesigner.config (design
time), and in the Report Server web.config (runtime). Your custom dll will
be loaded in the host process which will allow you to use
ConfigurationSettings.AppSettings to read its configuration settings.
--
Hope this helps.
---
Teo Lachev, MCSD, MCT
Author: "Microsoft Reporting Services in Action"
http://www.prologika.com
<AG>; "NLB d.d." <AGNLBdd@.discussions.microsoft.com> wrote in message
news:F67BB6A4-1ABA-40B0-93FC-0ED1BF6C7BCE@.microsoft.com...
> I have Custom.dll for retriving data from database. I don't want to store
> connection string in class. Is it possible to store in some RS config file
or
> i have to use XML file and read from it?
> Thx.|||I apologize for misleading you about the RSReportDesigner.comfit. It's been
a while since I tried this...so, here is the true story:
1. During runtime the report is generated under the Report Server host
process so the <appSetting> section in web.comfig file should work. Try
rendering a report from the Report which has a textbox with the following
expression:
= System.Configuration.Configuration.Upsetting(<your comfit value>)
. Please note that the web.config file already has a configuration section
so you need to add only the <appSettings> element.
2. During design time it is a bit trickier. Unfortunately, the current
configuration handler of the Report Designer doesn't seem to recognize
<appSettings>. However, you can render the report in debug mode by hitting
F5. This renders the report under ReportHost.exe. To get the config settings
working, create a ReportHost.exe.config in C:\Program Files\Microsoft SQL
Server\80\Tools\Report Designer and place your configuration section there
(the <configuration> element should be spelled with small "c"), e.g.:
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<appSettings>
<add key="serverUrl" value="http://localhost/reportserver" />
</appSettings>
</configuration>
b) To get the Preview tab working you could either check for Nothing and
replace that with a default value, or wrap the ConfigSettings.AppSettings
call to default to some default constant values. Once again, this is only
needed during design time. Your runtime report generation shouldn't need
this hack.
--
Hope this helps.
---
Teo Lachev, MCSD, MCT
Author: "Microsoft Reporting Services in Action"
http://www.prologika.com
<AG>; "NLB d.d." <AGNLBdd@.discussions.microsoft.com> wrote in message
news:3F040589-87F9-44FF-A9A8-06DDD8A65B31@.microsoft.com...
> Doesn't work..
> In put this
> <Configuration>
> <appSettings>
> <add key="KipConnectionString"
>
value="UHJvdmlkZXI9SUJNREFEQjIuMTtNb2RlPVJlYWRXcml0ZTtVc2VyIElEPWFwbGtpcDtEY
XRhIFNvdXJjZT1EQjJIT1NUUjtFeHRlbmRlZCBQcm9wZXJ0aWVzPTtQYXNzd29yZD1hcGxraXA="
/>
> </appSettings>
> <Add Key="SecureConnectionLevel" Value="0" />
> <..
> in RSReportDesigner.config and web.config and i still get an error
> The key 'KipConnectionString' does not exist in the appSettings
> configuration section.
> "Teo Lachev" wrote:
> > Yes, create an appSettings element in the RSReportDesigner.config
(design
> > time), and in the Report Server web.config (runtime). Your custom dll
will
> > be loaded in the host process which will allow you to use
> > ConfigurationSettings.AppSettings to read its configuration settings.
> >
> > --
> > Hope this helps.
> >
> > ---
> > Teo Lachev, MCSD, MCT
> > Author: "Microsoft Reporting Services in Action"
> > http://www.prologika.com
> >
> >
> > <AG>; "NLB d.d." <AGNLBdd@.discussions.microsoft.com> wrote in message
> > news:F67BB6A4-1ABA-40B0-93FC-0ED1BF6C7BCE@.microsoft.com...
> > > I have Custom.dll for retriving data from database. I don't want to
store
> > > connection string in class. Is it possible to store in some RS config
file
> > or
> > > i have to use XML file and read from it?
> > >
> > > Thx.
> >
> >
> >|||Of course the my spell check has made a mess :-) Please replace comfit with
config and Upsetting with AppSettings.
--
Hope this helps.
---
Teo Lachev, MCSD, MCT
Author: "Microsoft Reporting Services in Action"
http://www.prologika.com
"Teo Lachev" <teo@.nospam.prologika.com> wrote in message
news:eAKIjUWgEHA.1644@.tk2msftngp13.phx.gbl...
> I apologize for misleading you about the RSReportDesigner.comfit. It's
been
> a while since I tried this...so, here is the true story:
> 1. During runtime the report is generated under the Report Server host
> process so the <appSetting> section in web.comfig file should work. Try
> rendering a report from the Report which has a textbox with the following
> expression:
> = System.Configuration.Configuration.Upsetting(<your comfit value>)
> . Please note that the web.config file already has a configuration section
> so you need to add only the <appSettings> element.
> 2. During design time it is a bit trickier. Unfortunately, the current
> configuration handler of the Report Designer doesn't seem to recognize
> <appSettings>. However, you can render the report in debug mode by
hitting
> F5. This renders the report under ReportHost.exe. To get the config
settings
> working, create a ReportHost.exe.config in C:\Program Files\Microsoft SQL
> Server\80\Tools\Report Designer and place your configuration section there
> (the <configuration> element should be spelled with small "c"), e.g.:
> <?xml version="1.0" encoding="utf-8" ?>
> <configuration>
> <appSettings>
> <add key="serverUrl" value="http://localhost/reportserver" />
> </appSettings>
> </configuration>
> b) To get the Preview tab working you could either check for Nothing and
> replace that with a default value, or wrap the ConfigSettings.AppSettings
> call to default to some default constant values. Once again, this is only
> needed during design time. Your runtime report generation shouldn't need
> this hack.
> --
> Hope this helps.
> ---
> Teo Lachev, MCSD, MCT
> Author: "Microsoft Reporting Services in Action"
> http://www.prologika.com
>
> <AG>; "NLB d.d." <AGNLBdd@.discussions.microsoft.com> wrote in message
> news:3F040589-87F9-44FF-A9A8-06DDD8A65B31@.microsoft.com...
> > Doesn't work..
> >
> > In put this
> >
> > <Configuration>
> > <appSettings>
> > <add key="KipConnectionString"
> >
>
value="UHJvdmlkZXI9SUJNREFEQjIuMTtNb2RlPVJlYWRXcml0ZTtVc2VyIElEPWFwbGtpcDtEY
>
XRhIFNvdXJjZT1EQjJIT1NUUjtFeHRlbmRlZCBQcm9wZXJ0aWVzPTtQYXNzd29yZD1hcGxraXA="
> />
> > </appSettings>
> > <Add Key="SecureConnectionLevel" Value="0" />
> > <..
> >
> > in RSReportDesigner.config and web.config and i still get an error
> >
> > The key 'KipConnectionString' does not exist in the appSettings
> > configuration section.
> >
> > "Teo Lachev" wrote:
> >
> > > Yes, create an appSettings element in the RSReportDesigner.config
> (design
> > > time), and in the Report Server web.config (runtime). Your custom dll
> will
> > > be loaded in the host process which will allow you to use
> > > ConfigurationSettings.AppSettings to read its configuration settings.
> > >
> > > --
> > > Hope this helps.
> > >
> > > ---
> > > Teo Lachev, MCSD, MCT
> > > Author: "Microsoft Reporting Services in Action"
> > > http://www.prologika.com
> > >
> > >
> > > <AG>; "NLB d.d." <AGNLBdd@.discussions.microsoft.com> wrote in message
> > > news:F67BB6A4-1ABA-40B0-93FC-0ED1BF6C7BCE@.microsoft.com...
> > > > I have Custom.dll for retriving data from database. I don't want to
> store
> > > > connection string in class. Is it possible to store in some RS
config
> file
> > > or
> > > > i have to use XML file and read from it?
> > > >
> > > > Thx.
> > >
> > >
> > >
>|||Thank you. It's working.
"Teo Lachev" wrote:
> Of course the my spell check has made a mess :-) Please replace comfit with
> config and Upsetting with AppSettings.
> --
> Hope this helps.
> ---
> Teo Lachev, MCSD, MCT
> Author: "Microsoft Reporting Services in Action"
> http://www.prologika.com
>
> "Teo Lachev" <teo@.nospam.prologika.com> wrote in message
> news:eAKIjUWgEHA.1644@.tk2msftngp13.phx.gbl...
> > I apologize for misleading you about the RSReportDesigner.comfit. It's
> been
> > a while since I tried this...so, here is the true story:
> >
> > 1. During runtime the report is generated under the Report Server host
> > process so the <appSetting> section in web.comfig file should work. Try
> > rendering a report from the Report which has a textbox with the following
> > expression:
> > = System.Configuration.Configuration.Upsetting(<your comfit value>)
> >
> > . Please note that the web.config file already has a configuration section
> > so you need to add only the <appSettings> element.
> >
> > 2. During design time it is a bit trickier. Unfortunately, the current
> > configuration handler of the Report Designer doesn't seem to recognize
> > <appSettings>. However, you can render the report in debug mode by
> hitting
> > F5. This renders the report under ReportHost.exe. To get the config
> settings
> > working, create a ReportHost.exe.config in C:\Program Files\Microsoft SQL
> > Server\80\Tools\Report Designer and place your configuration section there
> > (the <configuration> element should be spelled with small "c"), e.g.:
> > <?xml version="1.0" encoding="utf-8" ?>
> > <configuration>
> > <appSettings>
> > <add key="serverUrl" value="http://localhost/reportserver" />
> > </appSettings>
> > </configuration>
> >
> > b) To get the Preview tab working you could either check for Nothing and
> > replace that with a default value, or wrap the ConfigSettings.AppSettings
> > call to default to some default constant values. Once again, this is only
> > needed during design time. Your runtime report generation shouldn't need
> > this hack.
> >
> > --
> > Hope this helps.
> >
> > ---
> > Teo Lachev, MCSD, MCT
> > Author: "Microsoft Reporting Services in Action"
> > http://www.prologika.com
> >
> >
> > <AG>; "NLB d.d." <AGNLBdd@.discussions.microsoft.com> wrote in message
> > news:3F040589-87F9-44FF-A9A8-06DDD8A65B31@.microsoft.com...
> > > Doesn't work..
> > >
> > > In put this
> > >
> > > <Configuration>
> > > <appSettings>
> > > <add key="KipConnectionString"
> > >
> >
> value="UHJvdmlkZXI9SUJNREFEQjIuMTtNb2RlPVJlYWRXcml0ZTtVc2VyIElEPWFwbGtpcDtEY
> >
> XRhIFNvdXJjZT1EQjJIT1NUUjtFeHRlbmRlZCBQcm9wZXJ0aWVzPTtQYXNzd29yZD1hcGxraXA="
> > />
> > > </appSettings>
> > > <Add Key="SecureConnectionLevel" Value="0" />
> > > <..
> > >
> > > in RSReportDesigner.config and web.config and i still get an error
> > >
> > > The key 'KipConnectionString' does not exist in the appSettings
> > > configuration section.
> > >
> > > "Teo Lachev" wrote:
> > >
> > > > Yes, create an appSettings element in the RSReportDesigner.config
> > (design
> > > > time), and in the Report Server web.config (runtime). Your custom dll
> > will
> > > > be loaded in the host process which will allow you to use
> > > > ConfigurationSettings.AppSettings to read its configuration settings.
> > > >
> > > > --
> > > > Hope this helps.
> > > >
> > > > ---
> > > > Teo Lachev, MCSD, MCT
> > > > Author: "Microsoft Reporting Services in Action"
> > > > http://www.prologika.com
> > > >
> > > >
> > > > <AG>; "NLB d.d." <AGNLBdd@.discussions.microsoft.com> wrote in message
> > > > news:F67BB6A4-1ABA-40B0-93FC-0ED1BF6C7BCE@.microsoft.com...
> > > > > I have Custom.dll for retriving data from database. I don't want to
> > store
> > > > > connection string in class. Is it possible to store in some RS
> config
> > file
> > > > or
> > > > > i have to use XML file and read from it?
> > > > >
> > > > > Thx.
> > > >
> > > >
> > > >
> >
> >
>
>

Config and Connection String

I want to store my connection string in a configuration file, however when I create the config file the connection string password is not stored. I can manually edit my config file and add the password to my connection string but I am sure that BIDS has a better solution. What is the best practice for storing connection string passwords in my SSIS Configuration files?

That behavior is by design; BIDS will not store the password for you; you have to add it manually. Hopefully you will not need to do it very often

|||

dgrm44 wrote:

I want to store my connection string in a configuration file, however when I create the config file the connection string password is not stored. I can manually edit my config file and add the password to my connection string but I am sure that BIDS has a better solution. What is the best practice for storing connection string passwords in my SSIS Configuration files?

Nope. You've got it figured out. BIDS doesn't store passwords.|||

The other guys are quite right but it may help to explain why.

As you may or may not be aware, a few years ago Microsoft introduced a programme called the 'Trustworthy Computing Initiative'. An effort to try and make their products more secure. This is one of the main reasons it took them so long to get Vista out of the door.

Anyway, you can see the effect of this here. If you are going to store passwords in a text file (which is inherently a security risk) then Microsoft want YOU to be responsible for doing that - they will not do it for you.

-Jamie

|||

The what is the best practice for storing my ssis connection string passwords if not in the config file?

|||

dgrm44 wrote:

The what is the best practice for storing my ssis connection string passwords if not in the config file?

That is the best practice. You have to do it manually as we have said. It's just that BIDS will not store it for you; you have to add it yourself. BIDS will do everything else for you in the config file, just not the password (technically any sensitive information)|||

Phil Brammer wrote:

dgrm44 wrote:

The what is the best practice for storing my ssis connection string passwords if not in the config file?

That is the best practice. You have to do it manually as we have said. It's just that BIDS will not store it for you; you have to add it yourself. BIDS will do everything else for you in the config file, just not the password (technically any sensitive information)

The OP raises a good point though. How can this be best practice if it is a security violation? The answer is that, until SSIS provides encrypted config files, you have to restrict access to the config file. This can be done using familiar operating system folder permissions.

-Jamie

|||

Jamie Thomson wrote:

The OP raises a good point though. How can this be best practice if it is a security violation? The answer is that, until SSIS provides encrypted config files, you have to restrict access to the config file. This can be done using familiar operating system folder permissions.

-Jamie

Well at present, it is the best practice as it's really the only practice that works, correct? |||YES! That was my point. If Microsoft is saying this is security violation then they must have another method in mind? What about the protection level of EncryptSensitiveWithPassword? Will this store the password in the package as an encrypted value? Within the config wizard when you select the properties that you want stored in the config file there is a value called password. What is this property?|||

dgrm44 wrote:

YES! That was my point. If Microsoft is saying this is security violation then they must have another method in mind? What about the protection level of EncryptSensitiveWithPassword? Will this store the password in the package as an encrypted value? Within the config wizard when you select the properties that you want stored in the config file there is a value called password. What is this property?

You enter the package password into that property. Then all sensitive data (passwords!) are encrypted with that password entered, using EncryptSensitiveWithPassword. Then, to open/execute the package, that password will be required to be passed into DTEXEC upon execution in order to decrypt the sensitive information. This is a good alternative.|||

More info here:

Storing Passwords

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

-Jamie

concurrent connection limits

I'm new using SQL 2005 Express

I'm planning to use SQL Server Express 2005 for a asp net web application for a video rental store.

Is there a concurrent user conection limit that I should care?

TIA

Gerardo

You can find more information here.

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

concatnate values for different rows

Hi ,

I have a situation where i need to concatnate values from different rows and store it a one string.

sample

dealid date

1 1/5/2007

1 2/4/2009

2 5/5/2004

2 8/5/2006

2 4/8/2006

so for one particular deal how many ever dates there are , i need to concatnate them all separated by a comma(,) and return and one string.

Is ther any way i could do it, Any suggestions appreaciated

Thanks

Ashsih

Maybe something like:

select distinct dealId,
reverse(substring(reverse(
( select convert(varchar(10), date, 101) + ', ' as [text()]
from theTable b
where a.dealId = b.dealId

order by date desc
for xml path('')
)), 3, 300))
from theTable a

|||

For a particular DealD:

declare @.targetDeal int set @.targetDeal = 1

select reverse(substring(reverse

( select convert(varchar(10), date, 101) + ', ' as [text()]
from theTable

where dealId = @.targetDeal

order by date desc
for xml path('')

), 3, 300)) as DealDates