January 6, 2006 at 6:49 am
So here I've build a DTSpackage with almost a 100 Data Transformation Tasks. This was build on a DevServer. Now we need to transfer this to an AcceptServer, and finally to a ProdServer. However, I thought with the MS Datalink I could easily change the source and destination of the Data Transformation Tasks, this seems not to be the case...
I am not very savvy with VBscript. Can someone give me an example, how I can change the servername in a Datapump task? Possibly the databasename should be changed, but i need be I can see to it, that databasenames are the same on all servers.
Greetz,
Hans Brouwer
January 7, 2006 at 2:20 pm
Hans,
I recommend the following:
1) Create a Package Level Global variable for both the server AND the database.
2) At the beginning of your process, add a "Dynamic Properties Task"
3) In the DP task, locate the connection or connections.
4) With each connection located, find the references to the server and database.
5) Change them so they point to the GVs in step 1.
6) After you transfer the DTS to the new server, change the GVs to the new names.
7) Save the DTS to the server (or where ever you plan to execute it from).
WROX has a good book that describes this process. It is titled "Professional SQL-Server 2000 DTS)" by Chaffin, Knight, and Robinson. The part that would help you is in Chapter 3 "Advanced Tasks".
Hope this helps!
January 9, 2006 at 7:51 am
As an extension of the idea above, keep those variable items in a .ini file. As long as the .ini file is in the same physical location on each server, you will only have to update the data in the .ini file to move your package. When you add the dynamic properties task, first thing to do is populate the GVs from the .ini file.
------------
Buy the ticket, take the ride. -- Hunter S. Thompson
January 9, 2006 at 8:31 am
Anyone have any experience on how this situation might now apply w/ SQL 2005 and SSIS?
January 9, 2006 at 10:23 am
Enough to say that while some things are similar (creating a global variable or reading from a file), there are enough differences that you'll really want to do a lot of testing. SSIS is a completely different method of working with ETL and SQL Server. You may want to check out the Integration Services forum over here or even check out the MS Newsgroups for some examples. Of courses, there's always http://www.sqlis.com and Jamie's excellent blog on SSIS @ http://blogs.conchango.com/jamiethomson/rss.aspx?CategoryID=71
Lots of good resources and they're well worth reading up on.
In a nutshell it would be something like assigning the Connection Strings to an Expression, basing that Expression on a global variable or similar, and setting the Delayed Validation to True (something like that). There's more to it than that, but that covers a lot of it.
-Pete
January 9, 2006 at 12:49 pm
Great, thanks for the suggested reading and links (sometimes there's TOO much you can find on the web, so it's good to get referrals).
I knew DTS pretty much inside and out, and now I know need to learn something completely new! But that's a good thing, because while I was able to do a LOT w/ DTS, it was a pain... lots of "basics" were missing. Reusable code/functions, for example, and certainly what started this thread... moving a pkg from a dev server to a production server... I mean how fundamental is that!
I wrote my own COM object/DLL that would change the server name dynamically for use in our web-based applications (that was before global vars were an option!). I'll happy ditch it when we can re-architect for SSIS, that just may take awhile.
January 9, 2006 at 1:28 pm
Well on that note, there are some parameters that can be set for different deployment scenarios. I don't typically have that much to change so this isn't a huge problem at the moment. However MS realized that a lot of people use a different set of parameters for test/dev/prod and ensured that we could set those accordingly.
Those sites should have some reading on the feature along with some examples.
-Pete
January 9, 2006 at 11:43 pm
Tnx for all the input,ppl. I have found a peice of VBscript, which does what I need and which I can easily adapt to my circumstances.
Much obliged.
Greetz,
Hans Brouwer
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply