February 11, 2008 at 2:00 pm
Greetings folks,
SSIS novice here.
On my laptop I have the BI Development Studio pointed at a sample database.
My client created me a client on their network with the BI Dev Studio pointed at a database on their environment.
I've created an SSIS project with 4 packages in it. My code has a data source and each package has a connection manager.
When I copy the code from my laptop to the client's client (I never get tired of saying that 😉 ), it comes up with errors about encrypted passwords:
Error1Error loading ProcessNumberofStores.dtsx: 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. f:\Myssisfolder\ProcessNumberofStores.dtsx11
And then I not only have to go to the data source and fix the login info there, I have to go to every single connection manager and fix the connection information THERE, and THEN i have to go double-click on about 75% of the control flow and data flow items because their metadata's been corrupted!
There has GOT to be a quicker way to run code against multiple environments.
Suggestions? (Please?)
Thanks buckets
Joe
February 12, 2008 at 6:24 am
Hi,
There have been discussions on this forum and other places about configuration and package encryption levels. Ultimately it is down to you and your client how you chose to implement security, however the easiest way is probably to set PackageEncryption to DontSaveSensitive. Then at runtime, use package configurations to populate all the sensitive properties.
This page has a discussion and links to several articles on the subject.
Hope it helps
Kindest Regards,
Frank Bazan
February 13, 2008 at 2:10 pm
Thanks Frank. I read that thread and several of the links on other sites including MSDN.
I found where to set the package to DontSaveSensitive and I stop getting the error about encryption.
I experimented with using XML configuration files and it seems like it blanks out the password whenever the BIDS touches it.
From everything I've been reading (and now my experience with XML configuration files as well), it seems like if I want to save the PASSWORD as part of the configuration (and I definitely do as I'm working with a client who will change their password every 3 months), I have no choice but to use a SQL Server table to store my configurations.
Is this true?
Thanks
Joe
February 13, 2008 at 2:13 pm
By the way I think I figured out the connection between Data Sources and connection managers - please tell me if I'm right:
You create Data Sources at project level. You create Connection Managers at the package level. A package-level connection manager can use a project-level data source, and any number of packages can share a project-level data source.
It seems like Data Sources are optional and Connection Managers are required (if you want to connect to data that is).
Do I have it?
February 13, 2008 at 5:21 pm
Hi,
You will find that when you create the dtsconfig file, you will have a config entry for the password (provided you've selected it as one of the properties), however the config value will be blank. You can enter this manually using an xml or text editor afterwards.
But if you are going to do this you have to make sure that the folder the dtsconfig files live in is well secured with only the SQLAgent account and the DBA having access (for production at least).
My preference for deployment is to create all my SQL Server environments with a control database and use values in these tables to configure my packages. The advantage to this is that you have more control over who does or doesn't have access... and changes to passwords is much simpler.
Obviously if you are using Windows authentication (and you should nearly always use this with SQL Server unless you're having connection pooling problems, or trying to authenticate from a non trusted domain), then this becomes irrelevant because the username won't be sensitive.
Lastly, the datasource. Its a simple connection definition that can be created outside the package and referenced just like a connection manager. Advantage to using one is that the connection manager isn't created until runtime and that if you have multiple packages using the same connection then you only need to define it once, and following from this maintenance is easier. A change in the definition would cascade down to all the packages that reference it.
Hope this helps
Kindest Regards,
Frank Bazan
February 14, 2008 at 10:25 am
Thanks again Frank.
I'm really not trying to be a pain here but I'm having some serious learning-curve issues.
My project has 4 packages in it. Aren't I supposed to be able to re-use the same XML configuration file across all 4 packages? I assigned it to the first one and set the four properties, then for the second it explicitly asked me if I wanted to Overwrite or Reuse Existing and I chose Reuse, ditto for the 3rd and 4th.
When I went to build the project, it complained that the XML file "already existed" in the Deployment directory.
When I made 4 copies of the XML file and assigned each to its own package, the problem went away.
Second, as for editing the XML file in the text editor and re-loading the project, I swear to you I tried it 5 times and it never worked, but I tried it one last time before responding to your post and now it worked. I must have been editing the files in the wrong directory. Who knows.
I was also going to post that when I updated and re-deployed my packages the installation wizard didn't prompt me for the settings in the configuration files. I figured out that it was because after enabling package configuration I was clicking "Build" not "Rebuild" so it wasn't updating the deployment manifest.
The good news - I'm making progress.
I'm lucky - the client I'm working for says that they don't require password changes for database users whose sole purpose in life is to run unattended batch jobs, so I don't have to worry right now about tackling the problem of them being able to update its password without having to recompile and/or redeploy the package. Yet!
The last hurdle I have to jump is figuring out how to run it from a command line - this client wants to use Autosys, not SQL Agent. I'm sure I'll find it if I google it.
Thanks again,
Joe
February 19, 2008 at 2:51 pm
I have been reading this problem and thought it would have ended but unfortunately because of some confusion, nothing is moving I guess. I think the first thing I would do is make the package configurations and save it as an XML file. This will ease me later on to modify the connection and even password if you would. Let us know where you stand at this point. Make some bullet points of your progress so that it could be easily understood and I am sure your problem can be solved!
[font="Verdana"]Imagination is more important than knowledge-Albert Einstein[/font]
March 12, 2008 at 4:28 am
Hi,
I'm a new user of IS.
I have the same problem of TennisManJoe2:
I don't understand how I can use a single XML configuration for multiple packages.
I assigned a XML file to the first package and set a connectionString property, then for the second it explicitly asked me if I wanted to Overwrite or Reuse Existing and I chose Reuse,
but When I went to build the project, it complained that the XML file "already existed" in the Deployment directory and the deploy go to error.
Thanks!!
Lorenzo
April 14, 2008 at 10:32 am
I don't think you *can* share a config file across multiple packages. My client's SQL DBA is a guru and he didn't complain when I gave him 4 copies of the same config file for each of my packages, so I'd assume he saw no problem with doing it that way.
April 14, 2008 at 2:51 pm
I don't agree with above. the whole purpose of XMl config file is to ease during deployment. If i have to make config file for each package, then why u wanna make it? U can just go ahead in the package itself and hardcode the connection.
Rt now i am in a project to migrate 70 DTS packages to SSIS. i am thinking of building one XML config file and use same config file for rest of the packages. though my packges points to different databases , i can create their connections in the first package and use same information for rest.
there is a good article in http://www.sqlis.com about comfiguration. also i came across another one when i googled it few days back.
Finally, the whole purpose of config file is to change connection string and others at one place ( In XML file) rather than all packages.
Thanks, KUmar
April 14, 2008 at 5:14 pm
I will certainly appreciate it if you decide to share your findings. I'll keep looking myself on my end and do the same!
April 15, 2008 at 7:43 am
TennisManJoe2 (4/14/2008)
I will certainly appreciate it if you decide to share your findings. I'll keep looking myself on my end and do the same!
I am using single XML config file for almost 30+ packages.
The trick is you have to define all the connection using VARIABLES... if you dont use variable then it will start giving you error saying the Connection does not exists in the package and so on..
If you define all the database connections as Variable then you will get Warning Messages but you can avoid them..
The packages will run fine...
April 15, 2008 at 9:13 am
Hi again folks,
Just wanted to confirm that you definately can use one config file for more than one package.
There is no secret to it, and there is no necessity to use variables either although, it is good practice.
If you are getting messages telling you that the dtsConfig file already exists in the deployment folder, then either a) that file already existed before. (Try removing it and rebuilding your project) or b) Not all your packages are set to reuse existing config file. (Go through the project and make sure that every file is set to reuse existing).
The last thing I wanted to point out is that you don't have to create a deployment utility to deploy the package. If it is causing you problems, try importing or copying each file individually until you find the one that is causing the problem.
HTH
Kindest Regards,
Frank Bazan
April 15, 2008 at 9:24 am
Frank is Right, It is not necessary to use variables, although it is good prtaice. for me, i always try to avoid it for 2 reasons. first i am new to ssis, so i find it hard to use it and second sometimes it is unnecassary for simple packges.
I am using XML file for my projects. can some help me to find informstions or briefly describe how to use variables in XMl file for connections. and what ar ethe benefits of using variables for connections?
Thanks, Kumar
April 15, 2008 at 10:36 am
The main advantage to using variables is it allows more transparency/visibility of how the package works and it allows you to run the package both in development studio and through dtexec.
Picture it this way, if you set the ConnectionString property of your connection managers directly through an xml file (or any other configuration for that matter), you have no visibility of this through the connection manager properties. The configurations occur only once when the package is loaded, and in your logs you will see no mention of them (unless they failed to set of course). So when it comes to debugging, you will not how the ConnectionString property was set.
If on the other hand you set the ConnectionString property via a variable, you can see in the expressions property of the connectionmanager what variable is being used. And even if you set your variable with a configuration, it doesn't make debugging any harder, because you can simply change the variable and run the package again without having to reset dtsConfig file and loading the package each time.
One further advantage is this... in a variable you can perform logic. So in an instance say where you want to use the same file but change the extended properties of a connection string based on some indicator within your package (an example is setting IMEX=1 in an excel connection string, its fine for importing your data but if you want to drop or create worksheets you will get a failure) then with a variable you can manipulate the string. By setting the connectionstring via a configuration you can't.
HTH
Kindest Regards,
Frank Bazan
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply