November 24, 2003 at 6:45 am
Hi,
I've posted this topic once already in the DTS section of the forum, but did not recieve any replies, so I've posted it here in the hope of reaching a wider audience.
Does anyone have an effective solution to Version controlling DTS packages outside of the msdb Db ?.
Currently our DTS packages are version controlled by SqlServer. We have been requested to version control our packages outside of the Db ( so that all application code, stored procs, DTS packages etc etc are kept in one central location dictated by our project office ). Can anyone suggest how this is best achieved?, or what is considered the best practice for DTS packages?, i.e do we generate VB versions of our DTS packages & put these in our source control tool or is there another more efficient/secure way of achieving this??
Thanks in advance for your ideas/solutions.
j
November 24, 2003 at 6:58 am
I required our group, from the beginning, to not store them in the database. That's very contrary to what most people do, but from my perspective it made them more "normal" in terms of source control. We leave them in the structured file format and store them in source safe, and deploy them much as we would any other executable. Then we invoke them with loading from file and/or dtsrun.
An issue we have not crossed but need to is how to search for dependencies, for example if we want to change column XYZ in table ABC how do we find where it is used, e.g. in TSQL steps in DTS. If you haven't tried, notice that the Unicode format (I think it is that) prevents a simple SEARCH find finding it.
What we think we might do is script a routine that will extract the equivilent VB code to a file in a parallel directory tree. This would not be checked in, but would simply be there for searching. Though arguably if you checked it in, you would have easy differences of DTS packages. My concern is that it would NOT get checked in all the time since it would not be automated (i.e. the steps to script and also check in would often not both occur simulataneously with checking in the DTS).
So far it has all worked well for us. It made the DTS packages more "normal" with regard to other pieces of code programmers were writing (all DTS packages for us are done by VB/C++/etc programmers so this fits their paradigm)
November 24, 2003 at 10:07 am
Ferguson,
Thanks for the reply, have you had any complications ( other than not being able to show dependencies/impact analysis ) from storing the DTS packages as structured storage files in your versioning tool ?
Also I think it might be possible to do something with the Meta Data Services Packages for your dependencies/impact analys - I just don't know how?, perhaps someone else in this forum might explain how that all works????
Thanks in advance.
j
November 24, 2003 at 10:22 am
We use Microsoft Source Safe and have had no issues in that rergard.
I looked at the meta data stuff briefly, and am embarassed (or Microsoft should be!) that I have no idea what all that stuff really does.
November 25, 2003 at 7:30 am
Where I work, we came up with almost the exact same methodology as Ferguson, with one minor extra tweak. (I have no DTS programming skills, so please pardon me if I get some of the terms wrong here.)
In every DTS package we added a global variable named "Version". This variable is not used anywhere, it just sits there as a label. Developers work over their DTS packages on the Development environment, and when they are satisfied they alert the Deployment team. The following steps are then followed:
-- Check the (prior) strucutred storage file out of VSS
-- Increment the DTS package version number by one (open, change, save)
-- Delete the checked-out file from the folder VSS copied it into
-- Recreate it with the new version (thus, only one DTS version in a file)
-- Check the (new) file back into VSS
-- Load/deploy the file into the QC environment (and later to Production)
-- Mark a log (chart on the wall) with date and version number deployed (quick reference)
I came up with this because how else can you tell what version of your DTS package(s) have been installed on what server? The built-in versioning system uses a combination of guids and internal versioning (DTS package contains X versions orderd by saved date/time), but there's no way I wanted to maintain dozens if not hundreds of DTS package versions on any server outside of Development (we burn through saved versions like you wouldn't believe--msdb size management gets to be an issue), and trying to track earlier/later through reading guids is just nuts.
Lastly, I came up with a VB script routine that would read this version number from all our packages and save it in a text file. Run on all environments, load into an Excel spreadsheet, tweak formatting and you have an instant record of which versions of which packages are on which environments.
We have 29 DTS packages across five environments. I don't know if this is lots or little, but I'd hate to have to "debug" a messed up deployment by hand...
Philip
P.S. Metadataservices is that node in Enterprise Manager which, if you accidentally click on, you have to wait a few minutes for it to determine you haven't done anything with it before it lets you click on what you really wanted to click on. If it does anything more than that, well, I too have never found out what it is.
November 25, 2003 at 8:40 am
I just did a quick search and our source safe contains an even 200 DTS packages. Fortunately I have only two databases to deploy these two.
However, I found just recently one ex employee (not ex because of this, but it is symptomatic) has been storing his on the server. He APPEARS to have also checked them into source safe, but now we are scambling to see if the LATEST is in source safe.
That's another reason I hate having them stored on the server, it's so easy for someone (and in a small staff all too many people have privileges that allow them to) to edit and resave a DTS package on the server and never update VSS.
At least as a file, it's marked read only and that extra step of having to either check it out or manually mark it to overwrite reminds people!
November 26, 2003 at 8:11 am
Philip,
Your approach sounds like a very good solution to our problems, I think we'll attempt to do the something similar here.
Thanks.
j
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply