Direct vs. Indirect Package Configurations

If your XML configuration files will be in the same location on your Development, UAT and PROD servers, is there any merit to making your configurations indirect?

I am modifying the connection string with the XML. My strategy is to set up an XML configuration for each database that we have. The Dev XML config will point to Development connection, UAT to UAT etc..

My thought is that by using the direct configuration it will eliminate the need for environment variables and also allow me to add configs without having to reboot the servers, which you would need to do in order to get server to recongize the EV.

Thanks

There isn't in real advantage to using indirect XML configurations if you're 100% sure that the location of your configuration file is never going to change and be identical in development, staging, and production environments. However, indirect configuration are a huge advantage if the location of your configuration files should change. Our project has over 50 SSIS packages (and growing) and I would hate to be the poor sap that would have to go change and test each and every package should the configuration file location ever was changed.|||

By the way, I wrote a batch configuration changer that you can use to modify the configuration settings in a group of packages. It's available as part of the downloadable samples for my book. A few have used it and saved some time with it.

It's called ConfigBatch.exe.

You can select a set of packages and bulk add, delete, modify configurations within those packages.

K

|||

Dear Kirk,

I got your book, and downloaded the samples. For the Config utility you included an msi, but the batch utility was a c# project. I don't have c# in my visual studio, could you perhaps mail an msi or .exe?

btw, the book is very readable so far (I'm at chapter 4).

thanks,

John.

Direct vs. Indirect Package Configurations

If your XML configuration files will be in the same location on your Development, UAT and PROD servers, is there any merit to making your configurations indirect?

I am modifying the connection string with the XML. My strategy is to set up an XML configuration for each database that we have. The Dev XML config will point to Development connection, UAT to UAT etc..

My thought is that by using the direct configuration it will eliminate the need for environment variables and also allow me to add configs without having to reboot the servers, which you would need to do in order to get server to recongize the EV.

Thanks

There isn't in real advantage to using indirect XML configurations if you're 100% sure that the location of your configuration file is never going to change and be identical in development, staging, and production environments. However, indirect configuration are a huge advantage if the location of your configuration files should change. Our project has over 50 SSIS packages (and growing) and I would hate to be the poor sap that would have to go change and test each and every package should the configuration file location ever was changed.|||

By the way, I wrote a batch configuration changer that you can use to modify the configuration settings in a group of packages. It's available as part of the downloadable samples for my book. A few have used it and saved some time with it.

It's called ConfigBatch.exe.

You can select a set of packages and bulk add, delete, modify configurations within those packages.

K

|||

Dear Kirk,

I got your book, and downloaded the samples. For the Config utility you included an msi, but the batch utility was a c# project. I don't have c# in my visual studio, could you perhaps mail an msi or .exe?

btw, the book is very readable so far (I'm at chapter 4).

thanks,

John.

Direct vs. Indirect Package Configurations

If your XML configuration files will be in the same location on your Development, UAT and PROD servers, is there any merit to making your configurations indirect?

I am modifying the connection string with the XML. My strategy is to set up an XML configuration for each database that we have. The Dev XML config will point to Development connection, UAT to UAT etc..

My thought is that by using the direct configuration it will eliminate the need for environment variables and also allow me to add configs without having to reboot the servers, which you would need to do in order to get server to recongize the EV.

Thanks

There isn't in real advantage to using indirect XML configurations if you're 100% sure that the location of your configuration file is never going to change and be identical in development, staging, and production environments. However, indirect configuration are a huge advantage if the location of your configuration files should change. Our project has over 50 SSIS packages (and growing) and I would hate to be the poor sap that would have to go change and test each and every package should the configuration file location ever was changed.|||

By the way, I wrote a batch configuration changer that you can use to modify the configuration settings in a group of packages. It's available as part of the downloadable samples for my book. A few have used it and saved some time with it.

It's called ConfigBatch.exe.

You can select a set of packages and bulk add, delete, modify configurations within those packages.

K

|||

Dear Kirk,

I got your book, and downloaded the samples. For the Config utility you included an msi, but the batch utility was a c# project. I don't have c# in my visual studio, could you perhaps mail an msi or .exe?

btw, the book is very readable so far (I'm at chapter 4).

thanks,

John.

Direct deployment issues

Hi

I have found article by Jamie T. (www.sqlis.com/26.aspx) where he explains how to use direct configuration.

I followed every step.

Before building project I made sure that CreateDeploymentUtility = True.

Once project was built I have copied files from Deployment folder to other PC to c:\PackageConfiguration (I have 3 files there: environment.dtsconfig, PersonAge.dtsx and jamie.SSISDeploymentManifest)

dtsConfig file is;

<?xml version="1.0" ?>

- <DTSConfiguration>

- <DTSConfigurationHeading>

<DTSConfigurationFileInfo GeneratedBy="me" GeneratedFromPackageName="PersonAge" GeneratedFromPackageID="{2612ABDA-E4FB-43DF-A1C1-44066426A798}" GeneratedDate="7/24/2007 9:46:41 AM" />

</DTSConfigurationHeading>

- <Configuration ConfiguredType="Property" Path="\Package.Connections[Destination].Properties[InitialCatalog]" ValueType="String">

<ConfiguredValue>DataStore</ConfiguredValue>

</Configuration>

- <Configuration ConfiguredType="Property" Path="\Package.Connections[Destination].Properties[ServerName]" ValueType="String">

<ConfiguredValue>MyPC\sql2k5local</ConfiguredValue>

</Configuration>

</DTSConfiguration>

However, I am doing something wrong as I was not able to deploy this to another PC.

I double click Manifest file and in Configuration Package - Property there are two entries: InitailCatalog and ServerName. Initial Catalog has Value=DataStore and ServerName = MyPC\sql2k5local. I change ServerName to be AlexPc\SQL2k5Local.

I have Validate Package after instalation so I get error saying that connection to Destination failed.

If I go to File System to installed package and say Run, package will run OK if I change all ConnectionManagers to point to AlexPc\SQL2k5Local

What is it that I am doing wrong?

I would recomend you to make the deplyment without the deployment utility. If you are using file based packages; you just have to copy the .dtsx and configuration files to the target location. At this point I am not clear wheter you are having problems with the deploymnet or with the package configurations.|||

Thanks Rafael!

it really is simple as that, no need to use utility

ok, next step for me is to try to use indirect configuration

|||

Glad you got it to work!

Here is thread you may find interesting about indirect configurations:

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

|||

This is funny!
I was just looking at this. Issue is that link you had as solution does not work. I was just going to come here and ask you how can I get to that article.

So, please let me know if there is a link that works.

Also, I have found one yesterday on your blog (SSIS package configs using sql server table ...an alternative to Indirect method) but my comment on it is that screen shots were way too small. I was not able to see anything on those.

|||

Also, i have an issue with MSDB; your input would be greatly appreciated.

I am trying to deploy a package to SQL. However, when I open Integration services I can not expand MSDB folder. Error is; SQL Server specified in SSIS service configuration is not present or available.... Login time expired .....(MsDTSSrvr)

I have found post here that says MsDtsSrvr.ini.xml file needs to be changed from

<Name>MSDB</Name>
<ServerName>.</ServerName>

to

<Name>MSDB</Name>
<ServerName>MyServerName</ServerName>

I tried MyServerName - does not work. I also tried value from connecting to Database Engine ( MyServerName\SQL2K5LOCAL) - does not help either.

What am I doing wrong?

this is what I am using:

Microsoft Visual Studio 2005
Version 8.0.50727.42 (RTM.050727-4200)
Microsoft .NET Framework
Version 2.0.50727

Installed Edition: IDE Standard

SQL Server Analysis Services
Microsoft SQL Server Analysis Services Designer
Version 9.00.2047.00

SQL Server Integration Services
Microsoft SQL Server Integration Services Designer
Version 9.00.2047.00

|||

I have updated the link:

http://rafael-salas.blogspot.com/2007/01/ssis-package-configurations-using-sql.html

I never have deployed the packages to the DB; but I know this forums has some threads that talks about not being able to expand the MSDB folder. USe the search capability of the forum

sql

Dimension Processing Error

I am using following configuration

Server:-SQL Server 2000

Database :-Oracle

MSOLAP :-Cubes

But while processing dimension We are getting follwing error

Analysis Server Error: Processing error [Attempt to delete member that is in use] '27-08-2005' ; Time:3/17/2006 1:04:01 PM

The mentioned date is already there in database ,Pl let me know what causing this error

Thx and regards

Pankaj

Are you using Process Update for you dimension?

The situation could be that dimension table ( tables ) dont have a particular member anylonger. Analysis Server compares new copy of the dimension to the old one. It sees it needs to delete some members. This particular member has some data associated with it and it cannot delete it.

You need to make sure if you are using Process Update for your dimension, you have full copy of the data in the underlying tables.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Hi

Thx for your reply.

There are no changes in structure and underlying data.The strange thing is i get this error only sometimes.If i make a copy of this dimension and if i try to process the dimension then sometimes it will not give issue,Pl advise

Thx and regards

Pankaj

|||I don't see any issue in it...coz we usually delete data from datawarehouse and process that particular dimension which is being affected by the DML operation....use Service Pack 4 of Analysis Services..it might remove this bug....|||

What could be happening is:

You fully process your dimensions. The default Error Configuration settings on the processing command are set to ignore processing errors like duplicate keys.

This is the main reason for your situaion. Once duplicate key error is ignored, next time you run Process update for your dimension, the records could come in the different order and Analysis Server is going to see another memeber coming in first. The member used to be first at the peviouse processing, coming and getting ignored. As a result stucture of your dimension might apper different.

You should look at the configuration options and change them to catch all errors.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Hello,

I have the same issue as mentioned above; [attempt to delete member that is in use] on a changing dimension when performing an incremental update. Strange is, the mentioned member is in the dimension table. So it should not be deleted.

The value for member keys unique is has always been true.

I use AS 2000 SP4

Someone any idea why AS gives this error mesage?

Dimension Processing Error

I am using following configuration

Server:-SQL Server 2000

Database :-Oracle

MSOLAP :-Cubes

But while processing dimension We are getting follwing error

Analysis Server Error: Processing error [Attempt to delete member that is in use] '27-08-2005' ; Time:3/17/2006 1:04:01 PM

The mentioned date is already there in database ,Pl let me know what causing this error

Thx and regards

Pankaj

Are you using Process Update for you dimension?

The situation could be that dimension table ( tables ) dont have a particular member anylonger. Analysis Server compares new copy of the dimension to the old one. It sees it needs to delete some members. This particular member has some data associated with it and it cannot delete it.

You need to make sure if you are using Process Update for your dimension, you have full copy of the data in the underlying tables.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Hi

Thx for your reply.

There are no changes in structure and underlying data.The strange thing is i get this error only sometimes.If i make a copy of this dimension and if i try to process the dimension then sometimes it will not give issue,Pl advise

Thx and regards

Pankaj

|||I don't see any issue in it...coz we usually delete data from datawarehouse and process that particular dimension which is being affected by the DML operation....use Service Pack 4 of Analysis Services..it might remove this bug....|||

What could be happening is:

You fully process your dimensions. The default Error Configuration settings on the processing command are set to ignore processing errors like duplicate keys.

This is the main reason for your situaion. Once duplicate key error is ignored, next time you run Process update for your dimension, the records could come in the different order and Analysis Server is going to see another memeber coming in first. The member used to be first at the peviouse processing, coming and getting ignored. As a result stucture of your dimension might apper different.

You should look at the configuration options and change them to catch all errors.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Hello,

I have the same issue as mentioned above; [attempt to delete member that is in use] on a changing dimension when performing an incremental update. Strange is, the mentioned member is in the dimension table. So it should not be deleted.

The value for member keys unique is has always been true.

I use AS 2000 SP4

Someone any idea why AS gives this error mesage?

Dimension Processing Error

I am using following configuration

Server:-SQL Server 2000

Database :-Oracle

MSOLAP :-Cubes

But while processing dimension We are getting follwing error

Analysis Server Error: Processing error [Attempt to delete member that is in use] '27-08-2005' ; Time:3/17/2006 1:04:01 PM

The mentioned date is already there in database ,Pl let me know what causing this error

Thx and regards

Pankaj

Are you using Process Update for you dimension?

The situation could be that dimension table ( tables ) dont have a particular member anylonger. Analysis Server compares new copy of the dimension to the old one. It sees it needs to delete some members. This particular member has some data associated with it and it cannot delete it.

You need to make sure if you are using Process Update for your dimension, you have full copy of the data in the underlying tables.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Hi

Thx for your reply.

There are no changes in structure and underlying data.The strange thing is i get this error only sometimes.If i make a copy of this dimension and if i try to process the dimension then sometimes it will not give issue,Pl advise

Thx and regards

Pankaj

|||I don't see any issue in it...coz we usually delete data from datawarehouse and process that particular dimension which is being affected by the DML operation....use Service Pack 4 of Analysis Services..it might remove this bug....|||

What could be happening is:

You fully process your dimensions. The default Error Configuration settings on the processing command are set to ignore processing errors like duplicate keys.

This is the main reason for your situaion. Once duplicate key error is ignored, next time you run Process update for your dimension, the records could come in the different order and Analysis Server is going to see another memeber coming in first. The member used to be first at the peviouse processing, coming and getting ignored. As a result stucture of your dimension might apper different.

You should look at the configuration options and change them to catch all errors.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Hello,

I have the same issue as mentioned above; [attempt to delete member that is in use] on a changing dimension when performing an incremental update. Strange is, the mentioned member is in the dimension table. So it should not be deleted.

The value for member keys unique is has always been true.

I use AS 2000 SP4

Someone any idea why AS gives this error mesage?