Sunday, December 30, 2012 2:13 AM
(1) Rename a SSIS folder. (2) Remove configuration file from package.
I want to be able to reuse a SSIS solution/package, so I copied it to a folders and rename it to something else. Afterward, I tried to change the folder path and the file names in the sln and the package.dtsx file using a text editor. But I am still running into various of issues (such as package file is corrupt and the path is not found issues.
How can I effectively rename a SSIS folder? Also, how can I remove the configuration files that was created in the package? I am getting the following message. This is probably because the folder is in different name now and SSIS is trying to load it into the Package. Since I do not need myconfig3.dtsConfig anymore, I like to remove it."Information while loading Package.dtsx: The package is attempting to configure from the XML file "C:\SSIS\Subfolder\myconfig3.dtsConfig."
Sunday, December 30, 2012 1:19 PM
With the package open (should show Control Flow tab), right click in the white space (work area). Locate the Package Configurations. Make sure you are using the copied file and not the original. Untick the "Enable package configurations" option. Close the Package Configurations Organizer.
If you have connections, they each have a GUID. The GUID in these connections is the one referenced from each Data Flow Task - possibly in the Data Flow tab, there is a process moving data from one to the other. Make sure that each sub-task referencing these Connection Managers is actually using the one you have in your package. If not, you'll see the guid rather than the name of the connection manager when you edit the processes. Make sure they are put together properly.
If you have parameters (and I am going to ask this one because there are issues when you go from 2005 to 2008 and from 2008 to 2012 and much of it has to do with changes to the Net Framework (2005 - Net 2, 2008 - Net 3.5, 2012, Net 4.5 and so on), you may need to tweak them as well.
As for the file itself, if it was ever connected to Team Foundation Server or to SourceSafe or another source control package, then the file will be write protected. Find the file (not the original, but the copy), right click, and remove the tick that says "Read only".
Monday, December 31, 2012 6:18 AM
Thank you so much, RJ! I used the Package Configurations and change the dtconfig location. Then I changed all the flat file locations and ran the package and it works - although not 100%, but basically your method work for my question in the title.
I have some additional questions hoping you or someone can answer them.
1. If I keep all the configuration files, in the Package Configurations Organizer, would I be able to run some of them but not all?
2. If I unchecked the Enable package configurations box, would I be able to run my package? If yes, does the SSIS use the setting on the Package Designer (Control Flow, Data Flow....) to run?
- Edited by BIAnalyst Monday, December 31, 2012 6:35 AM
Tuesday, January 01, 2013 12:53 PM
Good questions - I don't think I can answer properly.
The configurations are designed to allow you to be AGILE and run through the configurations you have in your environment. In mine, for example, (in reference to the 3. you listed earlier), I generally begin an SSIS package at the DEV level. On the DEV server, there is a shared folder for the BI developers to place their configurations (called CONFIG) and this is mimicked on QA, Staging, Demo, and Production. Similarly, there is a folder called SQLPackages for each step.
What is normal for me, is to develop the package locally. I am the DBA also so I have access to all the databases. Generally all I ever need is DEV. But if I import a file for an SSIS job, it will get pulled into the CONFIG folder for simplicity, and the package always looks for that folder (CONFIG). I also have one on my local machine. So regardless of where the SQL Package runs from, it will always reference the CONFIG folder. I suspect I do this because I'm not very adept at using the configuration files.
What they are supposed to do is to allow you to account for different environments and instead of "DontUseSensitive", it should also allow you to change passwords for the various levels of development - for example, no one should have the Production passwords if the org is large except perhaps the DBA. Technically you should also be able to embed the package to read variables in the CONFIG that would say what server to use (name of server and so on) and use those variables when you create your connection strings.
I know they are there - in my case, (we are an extremely small team), they were more of a hindrance than a help. I suspect that as time goes on, it will become increasingly more important as the packages get checked in and the get attached to the builds to have good configuration files. The builds contain the process with the web-config files where the variables take into account the differences in the platforms and it means, in a nutshell, that our QA people have full control of the PUSH procedures (on the build currently but they could technically at some point, own the package process as well). QA can retrieve a build from DEV, push it to QA, test it, and if it passes, push the same build to Staging. Generally the last step of the push is run by the system administrator (or the architect in his absence).
The point is that it is really easy to become bogged down in the process so if you decide to go that route, it may be best to get an architectural plan together first before you start throwing it together or you will find the package configurations can be more of a hindrance (enable package). Done correctly, they are incredible tools.
Thursday, January 03, 2013 1:14 PMThank again, RJ! Thank you for your valuable information, although I do admit that I only understand some of what you are saying, but they should be proved useful when I need them.
Have a great new year!