Configure connection strings for deployment to different servers

Hi,

We currently have a test and development environment for our data warehouse which is using SSIS packages to build (SSIS packages call stored procedures). We have multiple connections accross the different packages. The problem that we are having is when we deploy the packages to the development environment, the connection strings in the packages are still pointing to the live server! Hence, when we run these packages in the test environment, they actually execute the stored procedures in our live server.

I have tried to use package configurations and the deployment utility to assist us in this regard but have still come up with a few problems:

- When running the deployment utility, I still have to go and manually type in the data source the connection string must use - Is there maybe a way to use some sort of a variable that will automatically set this dependant on the name of the server that you are deploying to?

-It seems, even when I deploy to my live server, if I change the data source to point to my live server, it goes and changes the data source for the packages on my test server to point to the live servesr as well/

Basically, what I would like at the end of the day is to just be able to select the server the packages must be deployed to, and the connesction strings must be set automatically to either the test or live server dependong on which one I deploy to. Anyone know if this is possible?

Thanks in advance

Hi, it almost sounds like you have 1 configuration file...that all pacakges look at regardless what server they were deployed to. Thats the only way I could explain why making a change for the live server deployment affects packages running in test environment. If you look at the where things are deployed in each environment, do you see a config file in each location and does it contain the appropriate information? I think you can accoplish the dynamic connection string. Are you using property expressions to dynamically build/set your connections. There is a system variable available in SSIS named 'MachineName'. I must admit I have not played with it in a while and you need to be aware load order of 'stuff' is important. So, as a package spins up, it loads configurations and then applies property expressions as things are executed. So, I cannot recall if the system variable is set in time a property expression to use it. Another route is use an environment variable configuration. so the package loads and pulls in the servername frrom an environment variable on the machine and then that can be use in a property expression.

Hope that helps

|||

Yep, system variables are populated first and then expressions evaluated.

K

|||Will give it a try. Thanks for your help.

Configure connection strings for deployment to different servers

Hi,

We currently have a test and development environment for our data warehouse which is using SSIS packages to build (SSIS packages call stored procedures). We have multiple connections accross the different packages. The problem that we are having is when we deploy the packages to the development environment, the connection strings in the packages are still pointing to the live server! Hence, when we run these packages in the test environment, they actually execute the stored procedures in our live server.

I have tried to use package configurations and the deployment utility to assist us in this regard but have still come up with a few problems:

- When running the deployment utility, I still have to go and manually type in the data source the connection string must use - Is there maybe a way to use some sort of a variable that will automatically set this dependant on the name of the server that you are deploying to?

-It seems, even when I deploy to my live server, if I change the data source to point to my live server, it goes and changes the data source for the packages on my test server to point to the live servesr as well/

Basically, what I would like at the end of the day is to just be able to select the server the packages must be deployed to, and the connesction strings must be set automatically to either the test or live server dependong on which one I deploy to. Anyone know if this is possible?

Thanks in advance

Hi, it almost sounds like you have 1 configuration file...that all pacakges look at regardless what server they were deployed to. Thats the only way I could explain why making a change for the live server deployment affects packages running in test environment. If you look at the where things are deployed in each environment, do you see a config file in each location and does it contain the appropriate information? I think you can accoplish the dynamic connection string. Are you using property expressions to dynamically build/set your connections. There is a system variable available in SSIS named 'MachineName'. I must admit I have not played with it in a while and you need to be aware load order of 'stuff' is important. So, as a package spins up, it loads configurations and then applies property expressions as things are executed. So, I cannot recall if the system variable is set in time a property expression to use it. Another route is use an environment variable configuration. so the package loads and pulls in the servername frrom an environment variable on the machine and then that can be use in a property expression.

Hope that helps

|||

Yep, system variables are populated first and then expressions evaluated.

K

|||Will give it a try. Thanks for your help.

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

I have read many posts about configurations. However, in practice, I cannot solve a problem that is bothering me. I have connection managers (that manage SQL Server 2005 connections) configured in a local package. I have ProtectionLevel = 1 (By the way, where do you state that you want it "DontSaveSensitive", etc?). When I deploy the package to another computer (using the deployment utility) though, I get the error "The AcquireConnection method call to the connection manager ... failed". Could someone tell me, very explicitly, how can I use configurations to solve this problem? Or are there other ways to solve the problem? The problem, of course, is that the connection managers' passwords aren't being migrated from a computer o another. Thanks a lot.

Pedro Martins

<quote>

By the way, where do you state that you want it "DontSaveSensitive", etc?

</quote>

Right-click in any empty area of the control flow tab, and select properties.

Under Security section, you will see "ProtectionLevel" property.

STEP 1:

If you want to use DontSaveSensitive level, then define a variable (say DBConnection) and sets its value to this entire string: Data Source=dbserver\dbinstance;User ID=dbuser;Password=dbpassword;Initial Catalog=YourDBName;Provider=SQLNCLI.1;Auto Translate=False

STEP 2:

Now, in the "Connection Managers" tab (usually in a thin window at the bottom), select your database connection manager, right-click and select properties. You will see "Expressions", select it - hit the ... (three dots) that appear next to it. A property expression editor window appears. In the "Property" dropdown, select "ConnectionString". Hit the ... for the expression next to it. From the list of variables in the left hand side pane, drag and drop the above variable (DBConnection) in the "Expressions:" area. Hit OK.

STEP 3: Right click in an empty area in the Control Flow tab, and select "Package Configurations...". Add a new configuration using the wizard and make sure that you select the above variable (DBConnection) as part of the configuration.

HTH,

Nitesh

|||

Can you be a little bit more specific about step 3? Must I create an XML file? What must I do specifically?

Regards,

Pedro Martins

|||

yes, you need to create an XML configuration file!

-Jamie

|||

i was trying to build my project when I got the following error:

Error 8 System.ApplicationException: Could not copy file "\\Bi4all002\cpm@.bi4all\Configura??es\BIALL2006Staging.dtsConfig" to the deployment utility output directory "D:\Visual Studio 2005\Projects\BI4ALL IS\BI4ALL IS\bin\Deployment". > System.IO.IOException: The file 'D:\Visual Studio 2005\Projects\BI4ALL IS\BI4ALL IS\bin\Deployment\BIALL2006Staging.dtsConfig' already exists. at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath) at System.IO.File.InternalCopy(String sourceFileName, String destFileName, Boolean overwrite) at Microsoft.DataTransformationServices.Project.DataTransformationsProjectBuilder.CopyFiles(ICollection fileNames, String outputPath) End of inner exception stack trace at Microsoft.DataTransformationServices.Project.DataTransformationsProjectBuilder.CopyFiles(ICollection fileNames, String outputPath) at Microsoft.DataTransformationServices.Project.DataTransformationsProjectBuilder.CreateDeploymentUtility(IOutputWindow outputWindow) 0 0

As my project consists of various packages, I created configurations for all of them. Is that correct or not? Does it have something to do the problem?

Regards,

Pedro Martins

|||

Pedro Martins wrote:

As my project consists of various packages, I created configurations for all of them. Is that correct or not?

There is no right or wrong answer. Generally speaking if you are connecting to the same "thing" from multiple packages then you should share a single configuration file between them.

-Jamie

|||

Yes, I did just that. I just don't understand the error I am receiving. Do you have any hints?

Pedro Martins

Configuration Order

I am trying to use the SQL Agent to specify the connection properties of the connection manager that supports all of the other SQL Configurations in my packages.

It would appear that configuration files specified in the SQL agent job are applied after the other configurations. I have the same package I want to use under different configurations on the same machine. This package is also portable across to other machines. so cannot have any location information embedded in the package.

Does anyone have a solution for this?

Philip Coupar wrote:

I am trying to use the SQL Agent to specify the connection properties of the connection manager that supports all of the other SQL Configurations in my packages.

It would appear that configuration files specified in the SQL agent job are applied after the other configurations. I have the same package I want to use under different configurations on the same machine. This package is also portable across to other machines. so cannot have any location information embedded in the package.

Does anyone have a solution for this?

Are indirect configs an option? I have never used sql configs but I believe you can store the location of the configs in an environment variable. In this situation all you need to do is update an environment variable.

-Jamie

|||

I would still need to be able to set a modifier to say which configuration location I wanted to use.

I have the same package that I can use to load the same file from different customers. Obviously I want the data to end up in different databases, possibly even onto different servers. So far I have a seperate SQL configuration database for each customer, however I can only tell the package to look at one configuration database, even if I use indirect configuration, per server. And their lies the only answer I have at the moment have one configuration per server, instead of managing multiple customers in different services on the same hardware.

It would have been nice to be able to set the connection string for the configuration database from outside the SSIS package and have it applied before the other configurations kicked in. The effect I get at the moment is even though I use SQL Agent to set the connection string of the connection maanger I am using to access the SQL configuration, it seems to be applied after the other configurations are set and subsequently the other SQL configurations do not know where to get their properties from.

I hope I am wrong about this, and I have just missed something simple.

|||

I understand your problem; but not sure about the solution. Have you tried the 'SET' option available in the DTEXEC utility to override the connection string that points to your configuration table? For this you would have to use a CmdExec step in your SQL Server agent (which BTW gives you better logging information)

|||I have tried DTEXEC and using the SET options in SQL Agent, however it would also appear that these are applied after the internally specified configurations.|||

Philip Coupar wrote:

I have tried DTEXEC and using the SET options in SQL Agent, however it would also appear that these are applied after the internally specified configurations.

At some point, are you using in your packages 'Parent Package variable' for setting any of the configurations? If so, that may be the problem. There is a bug with configurations using 'parent package variables'.

I am currently using XML file to set up the connection string of my SQL Server configuration table with no problem; I have done the same using Environment variables; if this is what you are doing the problem must be something else.

|||

I had started out using Parent Package configuration until I discovered that small issue.

I now have an XML configuration file, which is working fine. However I would like to have the same packages used in multiple configurations on the same machine even using indirect configuration I can only point my packages to one xml configuration file, and even if I try to override the xml configuration file by using configurations in SQL Agent that is only apllied after the original configurations have been applied and subsequently my internal SQL configurations are missed.

It would be nice if configurations applied in SQL Agent were applied first or you could specify the order in which ALL of the configurations both internal and external were applied.

It would also be nice if parent package configurations applied in the order they were presented and not last as they do at the moment, in fact I think they are applied after validation.

|||

Phil,

There are valid reasons for the order in whch configs are applied and I read a blog psot on it once except that I can't remember where. I think parent package configs are deliberately applied at a different time to all the others - but I can't remember why. Sorry.

Defining the order in which configurations are applied sounds like a good idea to me. Perhaps you could suggest it at Connect?

-Jamie

|||

Jamie Thomson wrote:

There are valid reasons for the order in whch configs are applied and I read a blog psot on it once except that I can't remember where. I think parent package configs are deliberately applied at a different time to all the others - but I can't remember why. Sorry.

Jamie, it would be interesting to read that explanation. Actually there is an active bug open at connect:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=126146

No matter where you place it in the package configuration organizer; it would be set last... May be it is a difference in opinions

Configuration Order

I am trying to use the SQL Agent to specify the connection properties of the connection manager that supports all of the other SQL Configurations in my packages.

It would appear that configuration files specified in the SQL agent job are applied after the other configurations. I have the same package I want to use under different configurations on the same machine. This package is also portable across to other machines. so cannot have any location information embedded in the package.

Does anyone have a solution for this?

Philip Coupar wrote:

I am trying to use the SQL Agent to specify the connection properties of the connection manager that supports all of the other SQL Configurations in my packages.

It would appear that configuration files specified in the SQL agent job are applied after the other configurations. I have the same package I want to use under different configurations on the same machine. This package is also portable across to other machines. so cannot have any location information embedded in the package.

Does anyone have a solution for this?

Are indirect configs an option? I have never used sql configs but I believe you can store the location of the configs in an environment variable. In this situation all you need to do is update an environment variable.

-Jamie

|||

I would still need to be able to set a modifier to say which configuration location I wanted to use.

I have the same package that I can use to load the same file from different customers. Obviously I want the data to end up in different databases, possibly even onto different servers. So far I have a seperate SQL configuration database for each customer, however I can only tell the package to look at one configuration database, even if I use indirect configuration, per server. And their lies the only answer I have at the moment have one configuration per server, instead of managing multiple customers in different services on the same hardware.

It would have been nice to be able to set the connection string for the configuration database from outside the SSIS package and have it applied before the other configurations kicked in. The effect I get at the moment is even though I use SQL Agent to set the connection string of the connection maanger I am using to access the SQL configuration, it seems to be applied after the other configurations are set and subsequently the other SQL configurations do not know where to get their properties from.

I hope I am wrong about this, and I have just missed something simple.

|||

I understand your problem; but not sure about the solution. Have you tried the 'SET' option available in the DTEXEC utility to override the connection string that points to your configuration table? For this you would have to use a CmdExec step in your SQL Server agent (which BTW gives you better logging information)

|||I have tried DTEXEC and using the SET options in SQL Agent, however it would also appear that these are applied after the internally specified configurations.|||

Philip Coupar wrote:

I have tried DTEXEC and using the SET options in SQL Agent, however it would also appear that these are applied after the internally specified configurations.

At some point, are you using in your packages 'Parent Package variable' for setting any of the configurations? If so, that may be the problem. There is a bug with configurations using 'parent package variables'.

I am currently using XML file to set up the connection string of my SQL Server configuration table with no problem; I have done the same using Environment variables; if this is what you are doing the problem must be something else.

|||

I had started out using Parent Package configuration until I discovered that small issue.

I now have an XML configuration file, which is working fine. However I would like to have the same packages used in multiple configurations on the same machine even using indirect configuration I can only point my packages to one xml configuration file, and even if I try to override the xml configuration file by using configurations in SQL Agent that is only apllied after the original configurations have been applied and subsequently my internal SQL configurations are missed.

It would be nice if configurations applied in SQL Agent were applied first or you could specify the order in which ALL of the configurations both internal and external were applied.

It would also be nice if parent package configurations applied in the order they were presented and not last as they do at the moment, in fact I think they are applied after validation.

|||

Phil,

There are valid reasons for the order in whch configs are applied and I read a blog psot on it once except that I can't remember where. I think parent package configs are deliberately applied at a different time to all the others - but I can't remember why. Sorry.

Defining the order in which configurations are applied sounds like a good idea to me. Perhaps you could suggest it at Connect?

-Jamie

|||

Jamie Thomson wrote:

There are valid reasons for the order in whch configs are applied and I read a blog psot on it once except that I can't remember where. I think parent package configs are deliberately applied at a different time to all the others - but I can't remember why. Sorry.

Jamie, it would be interesting to read that explanation. Actually there is an active bug open at connect:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=126146

No matter where you place it in the package configuration organizer; it would be set last... May be it is a difference in opinions

|||And another open bug for the same issue :

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=126797

Scott.

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 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 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.

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 file question

In my SSIS packages I'm adding a config file that is storing only the connection strings to the database. Though most of my packages are reading or writing out flat files as well as connecting to a db. Do I need to put both db connection string and flat file connection strings in the config file? I'm currently adding only the db connection due to that will change depending which environment its ran in, unlike the flat file location will always remain the same.

I just want to be sure I don't need both for my package to run successfully.

You can add only 1 or as many configuration values as you want in a single file. As a good practice you should have both values set via configurations; just to cover a possible change in the flat file location. You could also create a separate config file for each property.

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

coneverting oledb code to connect to remote sqlserver

i have some oledb code made in c#(vs 2005) it is for local msaccess file. i want to conevert the code for sql server where connection string placed in web.config file seperately. please help me.
here is code

private void buildGrid(){string conStr ="Provider=Microsoft.Jet.OLEDB.4.0;Data Source="; conStr += Server.MapPath("~/common/db/demo.mdb"); System.Data.OleDb.OleDbConnection dbCon =new System.Data.OleDb.OleDbConnection(conStr); dbCon.Open();string sql ="SELECT * FROM Posts ORDER BY LastPostDate DESC"; System.Data.OleDb.OleDbDataAdapter dbAdapter =new System.Data.OleDb.OleDbDataAdapter(sql, dbCon); DataSet ds =new DataSet(); dbAdapter.Fill(ds); ds.Tables[0].TableName ="Inbox"; Grid1.DataSource = ds;}

please healp me..

Try this:

private void buildGrid(){string conStr ="Server=MyServer; Database=MyDatabase; User Id = MyUser; Password=MyPassword";System.Data.SqlClient.SqlConnection dbCon =new System.Data.SqlClient.SqlConnection(conStr); dbCon.Open();string sql ="SELECT * FROM Posts ORDER BY LastPostDate DESC"; System.Data.SqlClient.SqlDataAdapter dbAdapter =new System.Data.SqlClient.SqlDataAdapter(sql, dbCon); DataSet ds =new DataSet(); dbAdapter.Fill(ds); ds.Tables[0].TableName ="Inbox"; Grid1.DataSource = ds;}

I hope this will help.

Good luck.

|||

thank you,

i got you but i placed my connection string in to the web.config file how to use connection string placed in the web.config file with code you posted

|||

Try this:

private void buildGrid(){string conStr =ConfigurationSettings.AppSettings("MyConnectionStringInWebConfig"); System.Data.SqlClient.SqlConnection dbCon =new System.Data.SqlClient.SqlConnection(conStr); dbCon.Open();string sql ="SELECT * FROM Posts ORDER BY LastPostDate DESC"; System.Data.SqlClient.SqlDataAdapter dbAdapter =new System.Data.SqlClient.SqlDataAdapter(sql, dbCon); DataSet ds =new DataSet(); dbAdapter.Fill(ds); ds.Tables[0].TableName ="Inbox"; Grid1.DataSource = ds;}

Here is a good link:
How to: Read Connection Strings from the Web.config File
http://msdn2.microsoft.com/en-us/library/ms178411.aspx

Good luck.

Conection Error

Whenever I try and connection to SQL Server, I keep
getting the following error: The procedure entry point
SetfilesecurityI could not be located in the dynamic link
library Msdart.dll. I have just installed MDAC 2.8 and re-
installed SQL Server, but I'm still encountering the same
problem. I am running SQL Server Personal edition SP3a on
Windows XP. Thanksmsdart.dll is an MDAC file. Try reinstalling MDAC, maybe something you
installed later wrote over that file.
Use the MDAC component checker (from microsoft.com/downloads) to see if you
have a file mismatch with msdart.dll.
Cindy Gross, MCDBA, MCSE
http://cindygross.tripod.com
This posting is provided "AS IS" with no warranties, and confers no rights.|||I had (have) the same thing happening to me-2.8 hosed Delphi's ado
connection component. I always get this error when I try to create an
ado connection object.
I was able to fix it by manually replacing the files and changing
registry settings. MDAC will not rollback, the files are under
protection so I had to get a program to turn off Windows File
Protection, it was a huge hassle.
You need to get the 2.7 installer, extract the files, run the comparison
utility to identify what to change, get the utility to turn off file
protection, copy the files (and to dllcache), change the registry.
It worked for a day and now it's broken again. aargh!
the rule: DON'T UPGRADE TO MDAC 2.8.
I'm contemplating reformatting my hard disk and reinstalling
Win2K...WITHOUT MDAC 2.8.
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!|||Hi,
Did you ever figure out how to get past this error? I'm getting the
same error message, although the actual situation is somewhat different.
I just posted the following message at
http://forums.aspfree.com/t35176/shtml:
This error just started occuring today on an application I've been
working on for two years. The error message I receive is this:
The procedure entry point SetFileSecurityI could not be located in the
dynamic link library MSDART.dll.
The error occurs when I call:
System.Web.Mail.SmptMail.Send(message).
The error only occurs if I previously set the following property:
System.Web.Mail.SmtpMail.SmtpServer = "serverName"
I can't quite place my finger on the source of the problem. Here are two
potential causes of the problem:
A couple weeks ago, I had installed the 2005 Express verions of SQL
Server, c#, and Web Developer, but didn't notice any major problems
until now. Installing SQL Server 2005 caused a few minor issues with
Enterprise Manager on the Sql Server 2000 instance, but I was able to
solve those. I uinstalled those products, and repaired the installation
of VS2003 to no avail.
I almost ended up reformatting my hard drive over the weekend, after I
attempted to install a personal instance of Oracle 10g. After completing
the install, I was unable to reboot my computer. The system would boot
up, give about 150 warnings about delayed writes failing, and then crash
with STOP 0x00000027. The system was even hanging on reboot to safe
mode. It took me several hours to actually get my system to a bootable
state...After rebuilding the Master Boot Record, uninstalling oracle
from safe mode, about 25 reboots, a bios reset, yada, yada, yada...I
was back in.
I can't find much information on this error. One person here reported
the same problem when accessing SQL Server:
http://www.codecomments.com/sql/mes...C8d1e01c40513%2
4864b0f00%24a001280a%40phx.gbl %3E
They suggested it may have to do with installing MDAC 2.8, but I'm
pretty sure I've had that installed for a while. Anyways, the server is
running MDAC 2.8 without a problem, so I'm pretty sure it is something
else. Does anybody have any ideas?
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

Conditional compile the connection string in datasets

I need to find a way to useconditional compilation option to change the connection string in ado.net datasets 2.0.

Debug > connection 1 : test server
Release > connection 2 : real server

How is it possible ?This is often accomplished by storing the connection string in the web.config file, and using different web.config files on your test and real servers.|||

This is what I do in global.asax.vb:

Sub Application_Start(ByVal senderAsObject,ByVal eAs EventArgs)' Fires when the application is startedIf System.Environment.MachineName ="{Your production machine name here}"Then

Application(

"IsProduction") =TrueElse

Application(

"IsProduction") =FalseEndIfDim configAs Configuration

config = System.Web.Configuration.WebConfigurationManager.OpenWebConfiguration(

"~")Dim cssAs ConnectionStringsSection

css = config.ConnectionStrings

Dim settingAs ConnectionStringSettingsDim fUpdateAsBoolean =FalseDim cAsNew ConnectionStringSettingsCollectionForEach settingIn css.ConnectionStrings

c.Add(setting)

NextForEach settingIn cIf (Left(setting.Name, 5).ToUpper ="TEST-"AndNot Application("IsProduction"))Or _

(Left(setting.Name, 5).ToUpper =

"PROD-"And Application("IsProduction"))ThenIf css.ConnectionStrings(setting.Name.Substring(5))IsNothingThen

css.ConnectionStrings.Add(

New ConnectionStringSettings(setting.Name.Substring(5), setting.ConnectionString, setting.ProviderName))

fUpdate =

TrueElseIf css.ConnectionStrings(setting.Name.Substring(5)).ConnectionString <> setting.ConnectionStringThen

css.ConnectionStrings(setting.Name.Substring(5)).ConnectionString = setting.ConnectionString

fUpdate =

TrueEndIfEndIfEndIfNext' Make sure debug is turned off when running on the production serverDim xAs System.Web.Configuration.CompilationSection = config.GetSection("system.web/compilation")If x.DebugAnd Application("IsProduction")Then

x.Debug =

False

fUpdate =

TrueEndIfIf fUpdateThen

config.Save()

EndIfEndSub

Then I'll create connection strings like "TEST-MyConnection" and "PROD-MyConnection", etc. In code (or from data controls), I'll reference them as "MyConnection". When the application starts up, it'll update the web.config with the correct connection string to use on the machine it's running (and possibly restarting the application).

|||thanks a lot

Concurrent queries in SQL Server 2005 Express

If applicable, what's the limit on concurrent queries supported by SQL Server 2005 Express? Also, if there's a limit, does next connection after the max errors out or merely queues up?

Thanks,

Phillip

hi Pjhillip,

PhillipM wrote:

If applicable, what's the limit on concurrent queries supported by SQL Server 2005 Express? Also, if there's a limit, does next connection after the max errors out or merely queues up?

Thanks,

Phillip

as in MSDE 2000 (and full blown SQL Server's editions), there's a "tecnical" limit of 32767 connections... and this can not be workarounded..

MSDE has another "limit", a built in Governer, but it does not prevent futher connections when the magic number of 8 concurrent workloads is reached.. it simply linearly slows down every I/O activity for all active connections...

SQLExpress does not include this query governor, but addresses the edition limitation reducing memory the process can address to 1gb (instead of 2gb for MSDE)...

this 1gb of memory includes all memory segments used by the server, so that data (and pages) cahed as long as query plans, memory structures for locks and additional providers are held within this "container"...
connections cost as well in term of memory strucures, so they are held within the very same limited "container"...

so, when no additional resources are available for the "next connection", the connecting process will hang up waiting for released resources... as soon as this task times out (connection timeout property), an exception will be raised...

regards

|||

Andrea,

Thanks a lot! I should extend my query timeout property then to be on the safe side.

Phillip

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