September 9, 2008 at 7:54 pm
Hi,
I have researched the creation of configuration files for the past few days and made certain that I created them exactly the way the author said (Source: http://www.mssqltips.com/tip.asp?tip=1405).
I have changed the connection string to point to another database on the same server (for a simple Execute SQL Statement task). When I execute the package, the connection to the Execute SQL Statement task does not change to the connection on my configuration file.
Am I missing something in my properties or something?
Any help would be greatly appreciated. Thank you for taking the time to read my problem.
September 10, 2008 at 6:57 am
Have you enabled Package Configurations in the actual package? if so, have you actually added the ConnectionString property of the Connection Manager to the config file?
Just entering the string on the config file does you no good if you haven't told the package to look for that connection manager detail in the config file yet.
September 10, 2008 at 8:39 pm
Thank you so much! I just didn't use my brain when I attempted this. I did not set the variable to the connection I wanted so that's why it didn't do anything before. Thank you for responding to my post and cry for help. I really appreciate you taking time to help me out!
I do have a question still. Do you think it's better to use XML configuration files or using environmental variables and a configurations database? Which is easier to use (maintain) if someone that doesn't know how to use SSIS had to inherit SSIS packages?
September 11, 2008 at 3:52 am
My team has had better luck with the XML Configuration files. But that's because we have clustered servers and the environment config variable got confused.
It's a toss up. Either way, whoever inherits the packages better have some experience with SSIS or things could go south fast. @=)
September 12, 2008 at 4:11 am
Always glad to help. @=)
September 18, 2008 at 9:13 am
Guys,
Isn't using Configuration Type "SQL Server"
any better than XML?
It's so easy to query,update, delete,insert
Configuration values with SQL Server.
The trouble happens only when you
rename the existing SSIS Task which Properties are
specified in SQL Server (I am using SSISConfig database)
But with XML I guess you'll face the same
problems.
The difference is that, in my case for example,
it's much easier to read SQL Server table rather than XML Configuration file.
Correct me if I'm wrong please.
September 18, 2008 at 10:38 am
Riga,
In every and any instance, the correct answer is "It Depends". @=)
Which configuration file is easier for you depends on how your environment is set up.
For my workplace, our environment didn't lend itself easily to SQL Server config files so we used XML files instead. The benefit to this is that we can code on our personal boxes with the XML file and then move the package & file to the actual servers for Unit Testing, User Testing, QC and finally Prod. We can package the files & script them out in one mass move script for every stage of the environment without having to worry about how using a SQL Server config will cause problems with our clusters.
There were a lot of little interrelated issues that led to us adopting this particular series of steps, some of which had little to do with our SQL Server and more to do with our Web / App / Network setup. Hence the reason we chose to go with XML files.
September 18, 2008 at 10:54 am
We're working in a similar environment.
We design packages on our personal PC's,
use SSISConfig database on DEV SQL Server.
Then we move packages to PROD file location (\\TORD\PROD\SSIS\...)
and update SSISConfig database on PROD SQL Server
to reflect PROD ServerName, InitialCatalog,file path.
With XML I guess you have to go through the similar
process?...
September 18, 2008 at 11:00 am
Pretty similar, yes. @=)
September 18, 2008 at 11:08 am
Hi,
We also have similar situation...we develop our packages on DEV and move on to UAT and then to production. It is not clear for me what are the advantages of xml config over sql server. You said it depends..
Here is our situation...Config file involves password. So, which one is the best in this situation.
If have choosen XML config file...How can I protect/encrypt this between servers?
Thank you,
VG
September 18, 2008 at 11:20 am
Sensitive information should NEVER be included in a configuration if you can avoid it. If you can't avoid it, don't use XML or any other feature that leaves the password in a vulnerable place. The more people who have access to something (Server admin, network admin, SAN / NAS admin, etc) the easier it is for someone to hack into the file and get the password, no matter how protected you make it. And I don't think you can even encrypt an XML file.
Look up more information in BOL and google for articles on SSIS Package Configuration Types. Additionally, look up package security. You should know it all before making your decision.
We tend to use Windows authentication for our Servers with dedicated domain accounts as our SQL Service accounts. This way, we can include connection strings in our config file without having to worry about passwords. And since only a select few know the domain account passwords, it keeps our security high.
September 18, 2008 at 11:50 am
Thanks Brandie..your points really highlighted our security protection, which we don't have. Hmm let me read more on BOL, may be will go for SQL server, since our connection involves sql server authentication....
VG
September 18, 2008 at 12:55 pm
NP. Glad I could be of help.
September 18, 2008 at 1:32 pm
Check out Microsoft's Project REAL solution. This gives you a step by step description of how they think a SSIS implementation should be setup.
The most common configuration setup is:
-Environmental Variable with the path to the XML config file.
-XML config file with the connection string to the SQL Server configuration table.
-SQL Server configuration table with all other config values.
http://www.microsoft.com/technet/prodtechnol/sql/2005/realetldp.mspx
Hope that helps.
Tom
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply