Package Configurations from a Config database on a named instance

Question

Post reply

Package Configurations from a Config database on a named instance

David McKinney

SSChampion

Points: 10472
More actions
April 9, 2010 at 2:32 am

#143922

Hi,
I've been unable to get my package to pick up its config data from a database config table - despite having done this many times before.
To troubleshoot, I built a package consisting of a script task and one variable, whose value ("ValueFromIDE") I then linked to the config table using the wizard. (I also let SSIS create the table for me.)
When the wizard was complete, I verified that the table had been created and that there was a value for my variable. With sql I update this value to "ValueFromDB". When I run the package, the script task msgbox's the value of the variable.
I consistently got the value "ValueFromIDE" i.e. it didn't pick up the value from the DB.
If finally worked out that if my created my config table was on a different server it worked, and subsequently I observed that it appears only to work on default instances (or at least not to work on named instances.) i.e. Server01 works but Server01\inst01 does not work.
I was very surprised by this, and have not found any confirmation on the web.
Can any of you confirm or deny this? If you've got a named instance, does it work for you?
(I'm on SQL 2005 - more below)
Thanks for any help you can give!
David.
Microsoft SQL Server Management Studio9.00.3042.00
Microsoft Analysis Services Client Tools2005.090.3042.00
Microsoft Data Access Components (MDAC)2000.085.1117.00 (xpsp_sp2_rtm.040803-2158)
Microsoft MSXML2.6 3.0 4.0 5.0 6.0
Microsoft Internet Explorer6.0.2900.2180
Microsoft .NET Framework2.0.50727.3053
Operating System5.1.2600

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 1

I have written SSIS Packages using named instances and it worked.

In the connection Manager Server Dropdown are you specifying the Server Name and the Instance?

YourServerName\TheInstanceName?

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

David McKinney SSChampion Points: 10472 More actions · Answer 2

Thanks Welsh Corgi,

The connection is OK. SSIS uses the connection to create the config table and initial entries.

The named instance thing may in fact be a red herring, as my tests have currently found 2 named instances where it doesn't work but one where it does work (the SQLExpress instance.)

The apparent lack of any similar experiences on the web makes me think it may be something very specific to the environment I'm working in. (i.e. something dodgy)

As I also fortunately have the option of placing the config db on a server which seems to work OK, this is what I'll do.

And if someone happens to come along with an explanation, it's a bonus.

Thanks for taking the time to reply.

David.

ralf17x Newbie Points: 1 More actions · Answer 3

Hi David,

I had exactly the same problem. After changing my collation from Latin1_General_BIN to Latin1_General_CI_AS it was working fine!

ALTER TABLE [dbo].[CommonConfigurations] ALTER COLUMN [ConfigurationFilter] [nvarchar] (255) COLLATE Latin1_General_CI_AS NOT NULL

ALTER TABLE [dbo].[CommonConfigurations] ALTER COLUMN [ConfiguredValue] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL

ALTER TABLE [dbo].[CommonConfigurations] ALTER COLUMN [PackagePath] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL

ALTER TABLE [dbo].[CommonConfigurations] ALTER COLUMN [ConfiguredValueType] [nvarchar] (20) COLLATE Latin1_General_CI_AS NOT NULL

Best regards,

David McKinney SSChampion Points: 10472 More actions · Answer 4

Wow ralf,

Sounds like the place you work is as dodgy as where I'm at!

Thanks for the tip.