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.

Configuration file for dts package

Hi all,
I am building a DTS pakage to read from a text file and Populate a SQL
Server table. I am using the Enterprise manager to build the package.
I wanted to know if there is a way where I can specify information like the
path of the source text files, Login/pwd,server name of the detination
server in a separate "configuration file".
The reason I am looking for this is that I might need to move my packages to
different severs who may have different name and login/pwd. Also I dont want
to hadrcode the source file path in the package.
I think It is possible with UDL files but I dont know how.
any suggestions will be highly appreciated.
Cheers,
siajHi
Good stuff is here
www.sqldts.com
"siaj" <siaj@.discussions.microsoft.com> wrote in message
news:5FF4F79F-29D6-4B2E-99DD-9EB65724F768@.microsoft.com...
> Hi all,
> I am building a DTS pakage to read from a text file and Populate a SQL
> Server table. I am using the Enterprise manager to build the package.
> I wanted to know if there is a way where I can specify information like
the
> path of the source text files, Login/pwd,server name of the detination
> server in a separate "configuration file".
> The reason I am looking for this is that I might need to move my packages
to
> different severs who may have different name and login/pwd. Also I dont
want
> to hadrcode the source file path in the package.
>
> I think It is possible with UDL files but I dont know how.
> any suggestions will be highly appreciated.
> Cheers,
> siaj|||You can use a text file or an xml document to save this info. Open it from
your package a populate global variables.
AMB
"siaj" wrote:

> Hi all,
> I am building a DTS pakage to read from a text file and Populate a SQL
> Server table. I am using the Enterprise manager to build the package.
> I wanted to know if there is a way where I can specify information like th
e
> path of the source text files, Login/pwd,server name of the detination
> server in a separate "configuration file".
> The reason I am looking for this is that I might need to move my packages
to
> different severs who may have different name and login/pwd. Also I dont wa
nt
> to hadrcode the source file path in the package.
>
> I think It is possible with UDL files but I dont know how.
> any suggestions will be highly appreciated.
> Cheers,
> siaj|||I discussed some methods here in my article:
http://vyaskn.tripod.com/sql_server...t_practices.htm
You could make use of Dynamic Properties task, .ini files.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"siaj" <siaj@.discussions.microsoft.com> wrote in message
news:5FF4F79F-29D6-4B2E-99DD-9EB65724F768@.microsoft.com...
Hi all,
I am building a DTS pakage to read from a text file and Populate a SQL
Server table. I am using the Enterprise manager to build the package.
I wanted to know if there is a way where I can specify information like the
path of the source text files, Login/pwd,server name of the detination
server in a separate "configuration file".
The reason I am looking for this is that I might need to move my packages to
different severs who may have different name and login/pwd. Also I dont want
to hadrcode the source file path in the package.
I think It is possible with UDL files but I dont know how.
any suggestions will be highly appreciated.
Cheers,
siaj|||Thanks every body ...
These stff should help me.
siaj
"siaj" wrote:

> Hi all,
> I am building a DTS pakage to read from a text file and Populate a SQL
> Server table. I am using the Enterprise manager to build the package.
> I wanted to know if there is a way where I can specify information like th
e
> path of the source text files, Login/pwd,server name of the detination
> server in a separate "configuration file".
> The reason I am looking for this is that I might need to move my packages
to
> different severs who may have different name and login/pwd. Also I dont wa
nt
> to hadrcode the source file path in the package.
>
> I think It is possible with UDL files but I dont know how.
> any suggestions will be highly appreciated.
> Cheers,
> siaj

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

Conditionally extraction of data from a view

Hi all,
I am really new to SSIS, so may be this is a really simple question, but I couldnt find an answer yet.
I need to build a package that
1) counts the rows from a view
2) if rowcount >0 extracts the data into a file

I tryed to do this using a Row Count Transformation in the data flow, but after putting the count in a variable I am not able to perform the "conditional" phase two.
I mean that I want to check the value of the variable, but cannot figure out how to conditionally execute the flat file extraction.

Using Row Count, I have to build 2 data flow tasks.
Is there a way to do this in a single data flow?
May be using an Execute SQL Task instead of row count?

Any suggestions will ge greately appreciated
IgorBUse an execute SQL task on the control flow to perform your select count(*). Then hook that task up to your data flow with an expression (for the precedence constraint) that takes the count results from the Execute SQL task and checks it for > 0.|||

Phil Brammer wrote:

Use an execute SQL task on the control flow to perform your select count(*). Then hook that task up to your data flow with an expression (for the precedence constraint) that takes the count results from the Execute SQL task and checks it for > 0.

Can you please be more detailed?
I create a SQL Task with the select count(*).
how do i set the result set? as single row? and put the result into a variable?

Where do I check the result value?|||Single row result set. Set it to a user variable.

When you connect the Execute SQL task to the data flow, double click on the green connector line. In there set the Evaluation operation to "Expression and Constraint". Set the value to Success and the Expression to: @.Your_variable > 0|||

Phil Brammer wrote:

Single row result set. Set it to a user variable.

I cannot make this work... I get an Error 0xC002F309 when executing the step...

I create a new SQL Task, change the result set to "Single Value" and build the query in the SQL Statement (the query is simply SELECT COUNT(*) FROM RNFF_VIEW).
The view is on an Oracle DB and connect to it using OLE DB.
If I execute the query in query builder I get the correct result (rows = 10)

Then I go on the "Result set" pane of the SQL Task, Add a new line: in the "result name" I put a 0 and in the variable I select new variable, give the variable a name, leave the user namespace, and select a value type of Int32.

then I execute the task, and get an error:
Execute SQL Task: An error occurred while assigning a value to variable "Pippo": "Unsupported data type on result set binding 0.".

I tryed the same on a connection to SQL 2005 and there I have no errors... may be it has to do with the Oracle Provider?|||Finally I make it work!
On an Oracle Database, the result for a SELECT COUNT(*) FROM TABLE returns a data type of NUMERIC.
Looking at the "Integration Services Data Types" I found that I have to map it to a variable of a data type DT_NUMERIC.
Unfortunately this data type doesn't exist...

I make it work converting the COUNT(*) to a FLOAT and using a variable of DT_R8 (that maps to a variable of type Double).

I think that this is a bug of the SQL Server Business Intelligence Studio interface (the type Decimal doesn't appear in the interface).
May be assigning the type programmatically will work, but I am not able to do so...

Thanks Phil for your help!

Conditionally extraction of data from a view

Hi all,
I am really new to SSIS, so may be this is a really simple question, but I couldnt find an answer yet.
I need to build a package that
1) counts the rows from a view
2) if rowcount >0 extracts the data into a file

I tryed to do this using a Row Count Transformation in the data flow, but after putting the count in a variable I am not able to perform the "conditional" phase two.
I mean that I want to check the value of the variable, but cannot figure out how to conditionally execute the flat file extraction.

Using Row Count, I have to build 2 data flow tasks.
Is there a way to do this in a single data flow?
May be using an Execute SQL Task instead of row count?

Any suggestions will ge greately appreciated
IgorBUse an execute SQL task on the control flow to perform your select count(*). Then hook that task up to your data flow with an expression (for the precedence constraint) that takes the count results from the Execute SQL task and checks it for > 0.|||

Phil Brammer wrote:

Use an execute SQL task on the control flow to perform your select count(*). Then hook that task up to your data flow with an expression (for the precedence constraint) that takes the count results from the Execute SQL task and checks it for > 0.

Can you please be more detailed?
I create a SQL Task with the select count(*).
how do i set the result set? as single row? and put the result into a variable?

Where do I check the result value?|||Single row result set. Set it to a user variable.

When you connect the Execute SQL task to the data flow, double click on the green connector line. In there set the Evaluation operation to "Expression and Constraint". Set the value to Success and the Expression to: @.Your_variable > 0|||

Phil Brammer wrote:

Single row result set. Set it to a user variable.

I cannot make this work... I get an Error 0xC002F309 when executing the step...

I create a new SQL Task, change the result set to "Single Value" and build the query in the SQL Statement (the query is simply SELECT COUNT(*) FROM RNFF_VIEW).
The view is on an Oracle DB and connect to it using OLE DB.
If I execute the query in query builder I get the correct result (rows = 10)

Then I go on the "Result set" pane of the SQL Task, Add a new line: in the "result name" I put a 0 and in the variable I select new variable, give the variable a name, leave the user namespace, and select a value type of Int32.

then I execute the task, and get an error:
Execute SQL Task: An error occurred while assigning a value to variable "Pippo": "Unsupported data type on result set binding 0.".

I tryed the same on a connection to SQL 2005 and there I have no errors... may be it has to do with the Oracle Provider?|||Finally I make it work!
On an Oracle Database, the result for a SELECT COUNT(*) FROM TABLE returns a data type of NUMERIC.
Looking at the "Integration Services Data Types" I found that I have to map it to a variable of a data type DT_NUMERIC.
Unfortunately this data type doesn't exist...

I make it work converting the COUNT(*) to a FLOAT and using a variable of DT_R8 (that maps to a variable of type Double).

I think that this is a bug of the SQL Server Business Intelligence Studio interface (the type Decimal doesn't appear in the interface).
May be assigning the type programmatically will work, but I am not able to do so...

Thanks Phil for your help!

concatinating string values from multiple rows

I currently have some SQL code that is used to build a string that is a concatination of string values across multiple rows. The subqueries in the script sometimes return NULL values so I use the following statement to change the default behavior of the concatination operator which prevents my query from returning NULL:

SET CONCAT_NULL_YIELDS_NULL ON

Here's the code snippet:

select DISTINCT

(SELECT CASE WHEN (t1.MaskValue & HDR.TranTypeID)=1 THEN ' ' + t1.description ELSE '' END FROM transactiontypes t1 WHERE (t1.MaskValue & HDR.TranTypeID)=1) +

(SELECT CASE WHEN (t2.MaskValue & HDR.TranTypeID)=2 THEN ' ' + t2.description ELSE '' END FROM transactiontypes t2 WHERE (t2.MaskValue & HDR.TranTypeID)=2) +

(SELECT CASE WHEN (t3.MaskValue & HDR.TranTypeID)=4 THEN ' ' + t3.description ELSE '' END FROM transactiontypes t3 WHERE (t3.MaskValue & HDR.TranTypeID)=4) +

(SELECT CASE WHEN (t4.MaskValue & HDR.TranTypeID)=8 THEN ' ' + t4.description ELSE '' END FROM transactiontypes t4 WHERE (t4.MaskValue & HDR.TranTypeID)=8) +

(SELECT CASE WHEN (t5.MaskValue & HDR.TranTypeID)=16 THEN ' ' + t5.description ELSE '' END FROM transactiontypes t5 WHERE (t5.MaskValue & HDR.TranTypeID)=16)) as 'Transaction Type'

FROM HDResponse HDR

Here's the underlying table structure:

CREATE TABLE [dbo].[TransactionTypes](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Description] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [MaskValue] [int] NOT NULL) ON [PRIMARY]

CREATE TABLE [dbo].[HDResponse](
[ResponseID] [int] IDENTITY(1,1) NOT NULL,
[LoggedDateTime] [datetime] NULL,
[ResponseTypeTripID] [int] NULL,
[ResponseTypeID] [int] NULL,
[ResponseTypeObjectID] [int] NULL,
[ObjectID] [int] NULL,
[IDHolderID] [int] NULL,
[TransportCode] [int] NULL,
[CardID] [int] NULL,
[IssueCode] [smallint] NULL,
[EventDateTime] [datetime] NULL,
[Response] [bit] NULL,
[TranTypeID] [int] NULL)
ON [PRIMARY]

The problem I am having is I need to be able to use the query above in a view used for reporting. Unfortunately, you cannot use SET CONCAT_NULL_YIELDS_NULL ON in a view. This causes my query to return NULL if any of the subqueries return NULL. I could create a function to do something similar and reference the function in the query but I can't help but think there must be a way to get this done in a single query.

Any thoughts or ideas would be greatly appreciated.

Thanks!!!!!

What version of SQL Server are you using? SET CONCAT_NULL_YEILDS_NULL is ON by default unless you have changed the settings after connecting to SQL Server. This is true for any connections made via ODBC/OLEDB. You can use the PIVOT operator in SQL Server 2005 to solve this problem. You can also do this using a standard SQL technique like below:

select max(case p.trantype when 1 then p.description else '' end) +
max(case p.trantype when 2 then ' ' + p.description else '' end) +
max(case p.trantype when 4 then ' ' + p.description else '' end) +
max(case p.trantype when 8 then ' ' + p.description else '' end) +
max(case p.trantype when 16 then ' ' + p.description else '' end) as "Transaction Type"
from (
select t.description, (t.MaskValue & h.TranTypeID) as trantype
from HDResponse as h
join transactiontypes as t
on (t.MaskValue & h.TranTypeID) in (1, 2, 4, 8, 16)
) as p