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.

Configuartion & Deployment

Please kindly recommend good links/tutorials on configuration & deployment of reporting services.
Thanks much.

I posted the script required to deploy reports and other dependant files here ...http://www.bassicsoftware.com/CodeLibrary.aspx?searchcategory=29
Click on"Publish Reports to SQL Reporting Services"
Sorry for not having place much comments in the code

|||Hey i wanna know if there is any way that you can specify in the deployment script, the data source that your reports will be using.
Thanks|||The datasource should be defined in your RDL file.
btw, you can edit the rdl file using Notepad.|||One more thing...Does the datasource have to be in the same folder as the .rdl file or not.
If not then how can i give that in my .rss file.|||In the publishReports.rss you only specify the datasource by name.
In order to setup your datasources on the server (Local or Remote) you can do this manualy or create an rss script to publish them similiar to the publishReports.rss.
If I have time I'll post a sample of the script.|||Thanks,
You were right, the data source has to be defined in the .rdl file. there is a <datasourcereference> tag in the .rdl file that you need to edit in order to specify a datasource that is not in the same folder as the .rdl file, because, by default it is configured for data sources that are in the same folder. Still i havent found any way of specifying data sources on the fly at the time of deployment, and i dont think there is any...
Thanks Anyways...|||Note that a data source doesn't always mean referencing an RDS file, you may use a shared datasource.

Concatenating SQL Scripts

This is a bit off topic for a SQL group but perhaps someone has some
experience with what I am trying to do.
I am going to deployment with dozens of individual object scripts (tables,
procs, etc) . I want to combine all the individual scripts into one large
one so the realease engineer doesn't have to worry about keeping track of
them all.
So basically what I want to do is concatenate all the individual files into
a larger file.
I borrowed some code and patched together a batch file that runs in the
command processor and looks like this:
echo off
chdir C:\Documents and Settings\dave\My Documents\SQL\Deploy
rem Create tables
echo Generate CREATE TABLE SQL script [CreateTables.sql] ...
echo PRINT ' Create tables ...' > CreateTables.sql
cat ./Tables/dbo.table1.tab ./Tables/dbo.table2.tab ./Tables/dbo.table3.tab[vbcol=seagreen]
But this fails on the command "cat" ("'cat' is not recognized as an internal
or external command, operable program or batch file").
Does anyone have any experience with something like this?
How can I concatenate the files through the command line? I can copy them
all to a file but then I have to worry about ordering (or I have to rename
my files).
Thanks for any ideas.
Dave
Try the type command is windows (type * > newfilename.sql...)
cat is on unix...
"Dave" <dave@.nospam.ru> wrote in message
news:e4xIXRYJFHA.1176@.TK2MSFTNGP12.phx.gbl...
> This is a bit off topic for a SQL group but perhaps someone has some
> experience with what I am trying to do.
> I am going to deployment with dozens of individual object scripts (tables,
> procs, etc) . I want to combine all the individual scripts into one large
> one so the realease engineer doesn't have to worry about keeping track of
> them all.
> So basically what I want to do is concatenate all the individual files
> into
> a larger file.
> I borrowed some code and patched together a batch file that runs in the
> command processor and looks like this:
> echo off
> chdir C:\Documents and Settings\dave\My Documents\SQL\Deploy
> rem Create tables
> echo Generate CREATE TABLE SQL script [CreateTables.sql] ...
> echo PRINT ' Create tables ...' > CreateTables.sql
> cat ./Tables/dbo.table1.tab ./Tables/dbo.table2.tab
> ./Tables/dbo.table3.tab
>
> But this fails on the command "cat" ("'cat' is not recognized as an
> internal
> or external command, operable program or batch file").
> Does anyone have any experience with something like this?
> How can I concatenate the files through the command line? I can copy them
> all to a file but then I have to worry about ordering (or I have to rename
> my files).
> Thanks for any ideas.
> Dave
>
>
|||Thanks but when I change to the followingm I get the error: "The syntax of
the command is incorrect."
type ./Tables/dbo.table1.tab ./Tables/dbo.table2.tab ./Tables/dbo.table3.tab
> CreateTables.sql
Can you be more specific on the syntax
"dfate" <dfate2001-rock@.yahoo.com> wrote in message
news:eqWuHUYJFHA.484@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> Try the type command is windows (type * > newfilename.sql...)
> cat is on unix...
> "Dave" <dave@.nospam.ru> wrote in message
> news:e4xIXRYJFHA.1176@.TK2MSFTNGP12.phx.gbl...
(tables,[vbcol=seagreen]
large[vbcol=seagreen]
of[vbcol=seagreen]
them[vbcol=seagreen]
rename
>

Concatenating SQL Scripts

This is a bit off topic for a SQL group but perhaps someone has some
experience with what I am trying to do.
I am going to deployment with dozens of individual object scripts (tables,
procs, etc) . I want to combine all the individual scripts into one large
one so the realease engineer doesn't have to worry about keeping track of
them all.
So basically what I want to do is concatenate all the individual files into
a larger file.
I borrowed some code and patched together a batch file that runs in the
command processor and looks like this:
echo off
chdir C:\Documents and Settings\dave\My Documents\SQL\Deploy
rem Create tables
echo Generate CREATE TABLE SQL script [CreateTables.sql] ...
echo PRINT ' Create tables ...' > CreateTables.sql
cat ./Tables/dbo.table1.tab ./Tables/dbo.table2.tab ./Tables/dbo.table3.tab[vbcol=seagreen]
But this fails on the command "cat" ("'cat' is not recognized as an internal
or external command, operable program or batch file").
Does anyone have any experience with something like this?
How can I concatenate the files through the command line? I can copy them
all to a file but then I have to worry about ordering (or I have to rename
my files).
Thanks for any ideas.
DaveTry the type command is windows (type * > newfilename.sql...)
cat is on unix...
"Dave" <dave@.nospam.ru> wrote in message
news:e4xIXRYJFHA.1176@.TK2MSFTNGP12.phx.gbl...
> This is a bit off topic for a SQL group but perhaps someone has some
> experience with what I am trying to do.
> I am going to deployment with dozens of individual object scripts (tables,
> procs, etc) . I want to combine all the individual scripts into one large
> one so the realease engineer doesn't have to worry about keeping track of
> them all.
> So basically what I want to do is concatenate all the individual files
> into
> a larger file.
> I borrowed some code and patched together a batch file that runs in the
> command processor and looks like this:
> echo off
> chdir C:\Documents and Settings\dave\My Documents\SQL\Deploy
> rem Create tables
> echo Generate CREATE TABLE SQL script [CreateTables.sql] ...
> echo PRINT ' Create tables ...' > CreateTables.sql
> cat ./Tables/dbo.table1.tab ./Tables/dbo.table2.tab
> ./Tables/dbo.table3.tab
>
> But this fails on the command "cat" ("'cat' is not recognized as an
> internal
> or external command, operable program or batch file").
> Does anyone have any experience with something like this?
> How can I concatenate the files through the command line? I can copy them
> all to a file but then I have to worry about ordering (or I have to rename
> my files).
> Thanks for any ideas.
> Dave
>
>|||Thanks but when I change to the followingm I get the error: "The syntax of
the command is incorrect."
type ./Tables/dbo.table1.tab ./Tables/dbo.table2.tab ./Tables/dbo.table3.tab
> CreateTables.sql
Can you be more specific on the syntax
"dfate" <dfate2001-rock@.yahoo.com> wrote in message
news:eqWuHUYJFHA.484@.TK2MSFTNGP15.phx.gbl...
> Try the type command is windows (type * > newfilename.sql...)
> cat is on unix...
> "Dave" <dave@.nospam.ru> wrote in message
> news:e4xIXRYJFHA.1176@.TK2MSFTNGP12.phx.gbl...
(tables,[vbcol=seagreen]
large[vbcol=seagreen]
of[vbcol=seagreen]
them[vbcol=seagreen]
rename[vbcol=seagreen]
>

Concatenating SQL Scripts

This is a bit off topic for a SQL group but perhaps someone has some
experience with what I am trying to do.
I am going to deployment with dozens of individual object scripts (tables,
procs, etc) . I want to combine all the individual scripts into one large
one so the realease engineer doesn't have to worry about keeping track of
them all.
So basically what I want to do is concatenate all the individual files into
a larger file.
I borrowed some code and patched together a batch file that runs in the
command processor and looks like this:
echo off
chdir C:\Documents and Settings\dave\My Documents\SQL\Deploy
rem Create tables
echo Generate CREATE TABLE SQL script [CreateTables.sql] ...
echo PRINT ' Create tables ...' > CreateTables.sql
cat ./Tables/dbo.table1.tab ./Tables/dbo.table2.tab ./Tables/dbo.table3.tab
>> CreateTables.sql
But this fails on the command "cat" ("'cat' is not recognized as an internal
or external command, operable program or batch file").
Does anyone have any experience with something like this?
How can I concatenate the files through the command line? I can copy them
all to a file but then I have to worry about ordering (or I have to rename
my files).
Thanks for any ideas.
DaveTry the type command is windows (type * > newfilename.sql...)
cat is on unix...
"Dave" <dave@.nospam.ru> wrote in message
news:e4xIXRYJFHA.1176@.TK2MSFTNGP12.phx.gbl...
> This is a bit off topic for a SQL group but perhaps someone has some
> experience with what I am trying to do.
> I am going to deployment with dozens of individual object scripts (tables,
> procs, etc) . I want to combine all the individual scripts into one large
> one so the realease engineer doesn't have to worry about keeping track of
> them all.
> So basically what I want to do is concatenate all the individual files
> into
> a larger file.
> I borrowed some code and patched together a batch file that runs in the
> command processor and looks like this:
> echo off
> chdir C:\Documents and Settings\dave\My Documents\SQL\Deploy
> rem Create tables
> echo Generate CREATE TABLE SQL script [CreateTables.sql] ...
> echo PRINT ' Create tables ...' > CreateTables.sql
> cat ./Tables/dbo.table1.tab ./Tables/dbo.table2.tab
> ./Tables/dbo.table3.tab
>> CreateTables.sql
>
> But this fails on the command "cat" ("'cat' is not recognized as an
> internal
> or external command, operable program or batch file").
> Does anyone have any experience with something like this?
> How can I concatenate the files through the command line? I can copy them
> all to a file but then I have to worry about ordering (or I have to rename
> my files).
> Thanks for any ideas.
> Dave
>
>|||Thanks but when I change to the followingm I get the error: "The syntax of
the command is incorrect."
type ./Tables/dbo.table1.tab ./Tables/dbo.table2.tab ./Tables/dbo.table3.tab
> CreateTables.sql
Can you be more specific on the syntax
"dfate" <dfate2001-rock@.yahoo.com> wrote in message
news:eqWuHUYJFHA.484@.TK2MSFTNGP15.phx.gbl...
> Try the type command is windows (type * > newfilename.sql...)
> cat is on unix...
> "Dave" <dave@.nospam.ru> wrote in message
> news:e4xIXRYJFHA.1176@.TK2MSFTNGP12.phx.gbl...
> > This is a bit off topic for a SQL group but perhaps someone has some
> > experience with what I am trying to do.
> >
> > I am going to deployment with dozens of individual object scripts
(tables,
> > procs, etc) . I want to combine all the individual scripts into one
large
> > one so the realease engineer doesn't have to worry about keeping track
of
> > them all.
> >
> > So basically what I want to do is concatenate all the individual files
> > into
> > a larger file.
> >
> > I borrowed some code and patched together a batch file that runs in the
> > command processor and looks like this:
> >
> > echo off
> > chdir C:\Documents and Settings\dave\My Documents\SQL\Deploy
> >
> > rem Create tables
> > echo Generate CREATE TABLE SQL script [CreateTables.sql] ...
> >
> > echo PRINT ' Create tables ...' > CreateTables.sql
> >
> > cat ./Tables/dbo.table1.tab ./Tables/dbo.table2.tab
> > ./Tables/dbo.table3.tab
> >> CreateTables.sql
> >
> >
> > But this fails on the command "cat" ("'cat' is not recognized as an
> > internal
> > or external command, operable program or batch file").
> >
> > Does anyone have any experience with something like this?
> >
> > How can I concatenate the files through the command line? I can copy
them
> > all to a file but then I have to worry about ordering (or I have to
rename
> > my files).
> >
> > Thanks for any ideas.
> > Dave
> >
> >
> >
> >
>