.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

SQL Server Package Configuration..driving me nuts.

Posted By:      Posted Date: April 14, 2011    Points: 0   Category :


I just started developing a package in SSIS 2005 and I have it working (sort of) but I am trying to get it to work using package configurations. Between errors encountered between ProtectionLevels and Configurations, I've been going around in circles. I would really like some detailed clarifications and hopefully solutions to the issues I'm having now.

I have a package with a single "Execute Sql" task which for the purposes of discussion uses a connection manager 'CONN_ADO' which happens to be an ADO.Net connection. The task has defined variables etc, but the only configurable value for this task is the ConnectionString and I opted to store it in my Sql Server DB called MAINDB via package configurations. In order to add this configuration to Sql Server, I had to first create an OLEDB connection to MAINDB called CONN_OLEDB. Using this connection, I was able to create an SSIS Configurations table in MAINDB and store the connectionstring property for CONN_ADO. I made sure the configured value for CONN_ADO in the SSIS configurations table has the password specified in it and the package works perfectly fine under this setup when the ProtectionLevel is at its default , EncryptSensitiveWithUserKey.

Of course, that was too easy..The default protectionlevel was less than ideal in an actual development environment so I changed the Protection

View Complete Post

More Related Resource Links

SSIS Package retains designer setting, not Using Configuration From SQL Server Table.


Hello all,

I am using package configurations to set SSIS package variables from the SQL server Database [SSIS Configurations] table.   I have an environment variable set as my first configuration, to hold my database connection, and then the subsequent configurations are SQL configuration entries and held in the [SSIS Configurations] table.

When testing in BIDs, the connection is OK and the designer shows the correct values have been picked up.  All good!

But, when we change the database value in the [SSIS Configurations] table, the old configuration is still being used in BIDs.

I have checked using Profiler that BIDS is calling and retrieving the correct value from the table but the modified value is not being used in BIDs, instead it is retaining the old value when the package was designed.

How do I force the SSIS package to use the configuration specified in the SQL table?

Thanks, Helen


Best way to manage SQL Server Package Configuration data

I inherited approximately 150 SSIS packages doing various ETL operations. They use a custom table that requires that we add a new column when a package requires a new type of config data storage.
As part of an upgrade from 2005 to 2008 and a partial rewrite effort I am exploring, among other changes, switching from storing config data in the flat/wide table I mentioned to using SQL Server Package Configurations to manage configuration data. What do you think of the following?
  1. Having two Package Configurations per SSIS package. Both will be pointed to the same database containing only the config table, but with different filters. One filter will be specific to the package that will contain package-specific configuration data and one will be called "global" which will contain configuration data that all packages share (things like "default alert email" should Armageddon ensue during package execution).
  2. How best to manage configuration data when moving a brand new package from DEV to STAGING to PRODUCTION?
  3. How best to manage properties for the connection object used to initially connect to the Package Configuration database/table?


Make everything as simple as possible, but not simpler.
-Albert Einstein

Test Run: Configuration Testing With Virtual Server, Part 2


Because Virtual Server is built upon a set of COM modules, you can automate the creation and testing of virtual machines. Here we use Windows PowerShell to run the tests.

Dr. James McCaffrey and Paul Despe

MSDN Magazine December 2008

Test Run: Configuration Testing With Virtual Server, Part 1


This time James McCaffrey sets up a virtual environment to use for configuration testing to introduce you to software configuration testing with Microsoft Virtual Server

Dr. James McCaffrey

MSDN Magazine September 2008

Error while executing a package through SQL Server Agent

Hi All,   I have a ssis package. It has 3 tasks, first task updates a record in Oracle database to set an Indicator to “Y”, second task process SSAS Cube and the third again updates the same record and sets the indicator to “N”. This package has 2 data sources, one is Oracle and the other is Analysis Services. I gave the credentials for the Oracle and use NT Authority for Analysis services.   When I am executing from BIDS package is executing successfully. But when I am calling the job to execute this package its throwing me the error. Below is the error.     Message Executed as user: User\Username. ...ion 9.00.4035.00 for 64-bit  Copyright (C) Microsoft Corp 1984-2005. All rights reserved.    Started:  10:57:46 AM  Error: 2010-08-27 10:57:46.79     Code: 0xC0016016     Source:       Description: Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available.  End Error  Error: 2010-08-27 10:57:47.16     Code: 0xC0202009     Source: Ord_test Connection manager &q

SQL Server Configuration Manager Exception: 0x80010105

We're running SQL Server 2008 Standard edition version 10.0.2531.0 (SP1) 64-bit.  It is running on Windows Server 2008 R2. Whenever I connect to the SQL Server Configuration Manager, and look at the SQL Server Services, I get the message : The server threw an exception. [0x80010105]. Suggestions?Clint

SQL Server Configuration Manager: Connection to target machine could not be made in a timely fashion

I have a SQL Server 2005 box that was moved from a domain environment to a non-domain environment and was never dropped from its original domain before moving over.  Subsequently a new domain was created at the data center this SQL Server box now resides in.  The machine was dropped from its old domain and joined to the new one.   All the while the service accounts were set to the local admin account.  When I tried to start the Configuration Manager I got the message "Connection to target machine could not be made in a timely fashion.".  I read some blog entries and tried changing all the service accounts to Local System and the error message still came up.   All of the SQL Server services run fine.  It is just this management console that I cannot open.  I wasn't with this employer when this box was moved to the data center so I do not know if it worked prior and there isn't anyone here that could give me its history.   Any help would be appreciated.

SQL Server Configuration Manager - Protocol Name is blank

When I run SQL Server Configuration Manager (2008) and click on SQL Server Network Configuration and then Protocols for SQL, the right side of the screen shows the protocol icons and their status, but the names (TCP/IP, Shared Memory, Named Pipes, etc) are not there. Can someone tell me how to get the names to display again? They used to show up. A thought is that I installed XP SP3 and that made them go away, but I can't prove that.

SQL server memory / speed configuration

Let me preface this by saying I'm aware of SQL server memory architecture, i.e it takes as much as it can get it's hand's on ...I've read through all the post's on here and they have been very helpful ... However if someone could take a look at these these configuration values ... I'm trying to get a new server dialed in, I'm still having some user end speed issues, and application hangup's that seem sql related ... I'm by no means a dba so if someone who's knowledgeable could point me in the right direction I would appreciate it ... I feel like something is not configured properly ... The box has Microsoft SQL Server 2008 (RTM) 64-bit on Windows NT 6.0 <X64> Service Pack 2 Dell Power Edge R10 16 Logical CPU, 8 Hyperthread 12gig of memory (Right now SQL has 10/12 gig allocated in the task manager ( I know this is not accurate ) I'm adding another 12 gig tomorrow ... So the machine will be running 24 gig) There's two 9 gig databases running on the machine with several 4-6 million row tables .... Server is SQL Dedicated Configuration Values   Page Life Expectancy 18,038 under moderate load, tho I've seen it as low as 1,200 under heavy load .... Buffer Pool Usage CACHESTORE_SQLCP 2778616 OBJECTSTORE_LOCK_MANAGER 38816 MEMORYCLERK_SOSNODE 20936 CACHESTORE_OBJCP 18992 CACHESTORE_PHDR 16472 USERSTORE_SCHEMAMGR 12184 USERSTORE_DBMETADATA 8264 USE

Execute SSIS PAckage on Remote Server

I've seen several posts pertaining to this topic, but none of them covers what I'm having an issue with. I'm trying to create a "master job server". Essentially, I want to control all maintenance jobs from one central location. I've created SSIS packages that handle full backups, transaction log backups, index rebuilding, etc. on all of my production boxes. I've stored those packages in SQL Server. What I'd like to do now is create a separate job for each of those tasks on one central SQL Server and run those packages from there. I've been successful in creating a test job. The only step in the job is an SSIS PAckage execution step. The step successfully found the package I want to execute on the target server. When the job ran, I got the follwoing error: The job failed.  Unable to determine if the owner (ADMINSYS\arobinsf) of job SFO2PQE03 - Database Backup (FULL) has server access (reason: Could not obtain information about Windows NT group/user 'ADMINSYS\arobinsf', error code 0x2751. [SQLSTATE 42000] (Error 15404)). I want to make sure this job is executing on the remote server and not trying to execute locally. Is there something I'm missing in the set up of this job? I should also mention that the SSIS packages were created in SQL2K5, but I'm executing them from SSMS for SQL2K8. Any help would be appreciated! Thanks! A. M. Robinson

SSIS Package working on SQL Client Machine but when trying to run on SQL Server machine giving error

Hi We are trying to run SSIS package on sql server machine but sometimes it is giving error for data flow task containing Script Component. Error is - [SSIS.Pipeline] Error: component "SCR DimensionRelation" (1) failed the post-execute phase and returned error code 0x80004002. This error is not coming continuously. And also not for specific DFT. It comes for different DFTs as we rerun the package. Can anybody help me on this? We are using SQL server 2008. We are not getting any error on client machine :(:(

Single Server URL Name Configuration

I have created a 2 Server Farm (WFE/App and SQL) and created a Active Directory Domain (DEV.local) and DNS (DEV.Local) and set up my first site collection via the wizard after install and it works great as hhtp://servername and then I wanted to create a new site http://portal.dev.local and I am having no luck as I must be doing things out of order. I created a Managed Account, Web Application, Site Collection and I can not access it. I created an A Record and then a N Record in DNS and tried to set up Alternate Access Mapings and still no luck.  Is there some documentation on setting this up with a single IP/Machine? Thanks, ReevesDenver, CO

Loading Decimal Number From Excel into SQL Server 2005 using SSIS Package

If you have an excel spreadsheet that displays a number .02 (because it is formated) but internally it is stored as .2654329 and you want to load .2654329 how can you do that in the SSIS package?  When we load the spreadsheet it is loading .02.  Is there some kind of option in the SSIS package to tell the package to load the internal number?lcerni

Easy install package vs MsSQL server 2008

Is there anything i wont be able to do if i use SQL Server Express Database from the downlod install package offered on asp.net? Ive used MSSQL server 2008 and 2005, but to be honest i think it might be overkill cause im not very familiar with those applications. I just need the database for creating websites. Will i be able to remote connect to my webbhotel host and remotely modify the database on the remote server?Now i have a mess, i have installed both MsSQL 2005 and 2008 and im not even sure wich database is created with what. I would love to just have one simple setup, but im not sure if i can manage to migrate the databases to the new computer with this easy install package.The easy install package contains: ASP.NET and ASP.NET MVC Visual Studio 2010 Express Development Tool SQL Server Express DatabaseThanks for your time!

Client Configuration in ASP.NET development server

Hi I am using IIS hosting scenario to host my wcf services. I use filesystem for my web application and as you know client configuration needs an absolute URL and when I am debugging my application I get different port numbers on ASP.NET Development Server. That means when ever I get a different port number in this server I ahve to change port number for client address for all of my wcf services. Is there any solution for this? Thanks PS it doesnt accept any relative address.    

How to get configuration information in side server-side code

I'm relatively new to writing code for sharepoint 2010, and one thing I'm curious about is how/where can I store configuration information, so that if I code on 1 platform and push to another, how do I keep things like the weburl, sites, lists, etc in a config file?  Is there a standard config file to use, or would it always be the web.config for the entire system? Specifically, if i start up code, how can I find a) what is the current weburl I'm on, b) what site/sitecollection, and c) a list of all lists? thanks  Rocky Sanders

How to create a SSIS package to import records form SQl Server 2008 tables to text files

I am a newbie to SSIS and would like to create a package to accomplish the task referenced in the above title. Will appreciate any links and pointers in the right direction.   Thanksakoranteng
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend