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.

Conditional CREATE SCHEMA

Hi,
I would like to write TO-SQL batches that call the CREATE SCHEMA...
statement. However, there is a reasonable likelihood that the schema I want
to create already exists. However, the CREATE SCHEMA statement has the
limitation that it be the first command in a batch. I found out the hard
way that this means that I can't run a batch like this...
IF (SCHEMA_ID('MySchema') IS NULL)
CREATE SCHEMA [MySchema] AUTHORIZATION dbo;
How do I conditionally create a schema based on whether it currently exists?
I came up with this, but I'd like to think there is something a little more
elegant.
DECLARE @.CS varchar(255);
SET @.CS = 'CREATE SCHEMA [MySchema] AUTHORIZATION dbo;';
IF (SCHEMA_ID('MySchema') IS NULL)
EXEC (@.CS);
--
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.orgHi Daniel
Although you may not think your solution elegant it is a solution to the
issue which can be useful, for instance if you want to take some action
outside the scope of the current transaction.
John
"Daniel Jameson" wrote:
> Hi,
> I would like to write TO-SQL batches that call the CREATE SCHEMA...
> statement. However, there is a reasonable likelihood that the schema I want
> to create already exists. However, the CREATE SCHEMA statement has the
> limitation that it be the first command in a batch. I found out the hard
> way that this means that I can't run a batch like this...
> IF (SCHEMA_ID('MySchema') IS NULL)
> CREATE SCHEMA [MySchema] AUTHORIZATION dbo;
> How do I conditionally create a schema based on whether it currently exists?
> I came up with this, but I'd like to think there is something a little more
> elegant.
> DECLARE @.CS varchar(255);
> SET @.CS = 'CREATE SCHEMA [MySchema] AUTHORIZATION dbo;';
> IF (SCHEMA_ID('MySchema') IS NULL)
> EXEC (@.CS);
> --
> Thank you,
> Daniel Jameson
> SQL Server DBA
> Children's Oncology Group
> www.childrensoncologygroup.org
>
>|||Hi Daniel,
I understand that you would like to find an elegant way to create a schema
if the schema does not exist.
If I have misunderstood, please let me know.
I think that your current workaround is elegant. If you use "Generate
Scripts... " to generate the script of schema objects in SQL Server 2005
Management Studio, you will find the following scripts:
USE [AdventureWorks]
GO
/****** Object: Schema [HumanResources] Script Date: 09/17/2007
13:41:51 ******/
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'HumanResources')
EXEC sys.sp_executesql N'CREATE SCHEMA [HumanResources] AUTHORIZATION [dbo]'
GO
As you can see, it is very similar to yours. Currently I do not think that
there is a better way due to the design limitation of using CREATE SCHEMA.
Please feel free to let us know if you have any other questions or
concerns. Have a good day!
Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||Hi Daniel,
What is this issue going on?
If there is any issue, please feel free to post back. We are very glad for
further assistance.
Have a good day!
Best regards,
Charles Wang
Microsoft Online Community Support
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================

Conditiional DTS package start

Let's imagine that we have "conditional" start of DTS package (for
this post I will call it DoIt)
DTS package DoIt must be started when particular event occur.
Event is: we have an empty folder on the disk. When an outside program
send a file (for example message.txt) in that folder - it is a signal
that DTS package DoIt can be started.
So, we must permanently check is the message.txt file exist in that
folder. Also, when DTS package is started we must exclude possibility
that DTS package DoIt start once or more again (because message.txt
file is there).
Can anybody help me with this problem. Is it possible to write a
program that:
- permanently check is the message.txt exist in a folder
- start DTS package DoIt when the message.txt exist
- rename a message.txt file -> message.old
... or someone have a better idea?
Thanks for your answers...> Can anybody help me with this problem. Is it possible to write a
> program that:
> - permanently check is the message.txt exist in a folder
> - start DTS package DoIt when the message.txt exist
> - rename a message.txt file -> message.old
You can create a scheduled job with ActiveX script task, and then in
VBScript use FileSystemObject to check the folder for the file. Then you run
the package with DTSRun and finally rename or move the file with
FileSystemObject again. I have an example how to use FileSystemObject in
VBScript at
http://solidqualitylearning.com/blo...12/19/225.aspx. You
can schedule this job on every hour, for example, if this is enough.
More advanced solution can be written in .NET using the FileSystemWatcher
Class.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com|||Dear Dejan,
I also have idea (with some others) that solution with VBS will
probably give me a solution. Unfortunately, my knowledge about VBS is
very limited.
The employers who know about VBS are leaving my company. No time for
learning for me. And now I need a quick solution.
I know that is unusual to ask you this, but when I saw your code I
hope that VBS that I need is not big problem for you. But it is for
me.
Is it possible to send me VBS that solve my problem?
I will understand if you have no time or think that my wish is unfair.
Thank you for your post and any new.
Best regards.

Concurrent call to a stored procedure

I have a multithread component which call to a stored procesure to do some
data processing. But the time for excuting the stored procedure increased
dramastly with the thread number. If the stored procesure is very simple
(only have insert statement ), it does not have this problem. Any
recommandations?Possibly blocking problems due to your transaction handling and the operatio
ns you do inside the
procedure. But we really need to know more about to code in your procedure t
o be able to comment
further.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"wdwedw" <wdwedw@.discussions.microsoft.com> wrote in message
news:07C3F51C-F994-4657-833F-9CA6C2676C4B@.microsoft.com...
>I have a multithread component which call to a stored procesure to do some
> data processing. But the time for excuting the stored procedure increased
> dramastly with the thread number. If the stored procesure is very simple
> (only have insert statement ), it does not have this problem. Any
> recommandations?