Results 1 to 15 of 15
  1. #1
    Join Date
    Jul 2004
    Posts
    306

    Unanswered: Exporting DTS jobs?

    SQL Server 2000.

    Hey there,

    I'm trying to set up a test environment. My projuction environemtn has a bunch of Scheduled Jobs which run either stored procedures or DTS jobs.

    I've exported the SP's and the Scheduled jobs but I'm wondering if there is any way to migrate the DTS jobs?

    Cheers...

  2. #2
    Join Date
    Feb 2003
    Location
    India
    Posts
    216
    open DTS, select "save as" from "Package" menu. Select "structured storage file" in Location drop down. provide name etc. save.

    in the destination machine, right-click on Data-Transformation-Services of EM select "open package". save it with Location = SQL Server.

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Me being the lazy bum that I am, I'd use DTSBackup. I've got develoeprs that really like DTS though, so I get an infernal number of packages on some of my servers.

    -PatP

  4. #4
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by Pat Phelan
    Me being the lazy bum that I am, ...
    -PatP
    I think I'm maybe lazier. I created a script that saves my DTS packages every night to a remote (off-server) location:

    Code:
    ' **********************************************************************
    ' * SaveDTS.vbs
    ' * Hugh Scott
    ' * 2005/06/28
    ' *
    ' * This script logs into a SQL Server, locates all DTS packages and Then
    ' * saves each pacakge as a file to a specified location
    ' * 
    ' * Parameters:
    ' *	IN:	None
    ' * 	OUT:
    ' *		None
    ' *
    ' * Usage:
    ' *
    ' *
    ' * Modification History:
    ' * 	Date		Initials	Modification
    ' *	2005/06/28	HMS	Original
    ' * 
    ' **********************************************************************
    Dim oDTS
    Dim oConn
    Dim oRS
    Dim sSQL
    Dim iPackages
    Dim sServer
    Dim sUser
    Dim sPassword
    Dim bTrusted
    Dim sTarget
    Const DTSSQLStgFlag_UseTrustedConnection = 256
    
    bTrusted = True
    sServer = <set server name here>
    sTarget = <set backup destination here>
    
    Set oConn = CreateObject("ADODB.Connection")
    Set oRS = CreateObject("ADODB.Recordset")
    
    If bTrusted Then
    	oConn.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=msdb;Data Source=" & sServer
    Else
    	oConn.ConnectionString = "Provider=SQLOLEDB.1;UID=" & sUser & ";Password=" & sPassword & ";Persist Security Info=False;Initial Catalog=msdb;Data Source=" & sServer
    End if
    oConn.Open
    
    sSQL = "SELECT DISTINCT name FROM sysdtspackages"
    
    Set oRS = oConn.Execute(sSQL, iPackages)
    
    While Not oRS.EOF
    
    	Set oDTS = CreateObject("DTS.Package")
    	sName = oRS("name")
    	oDTS.LoadFromSQLServer sServer,,,DTSSQLStgFlag_UseTrustedConnection,,,,sName
    	sFileName = Replace(sName, ":", "")
    	oDTS.SaveToStorageFile sTarget & sFileName & ".dts"
    	Set oDTS = Nothing
    	oRS.MoveNext
    Wend
    
    Set oRS = Nothing
    oConn.Close
    Set oConn = Nothing
    Schedule it as a job (set the task type to VB Script). It ensures that you always have a backup of your DTS packages when it comes time to restore the db server.

    Regards,

    hmscott
    Have you hugged your backup today?

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Nice idea. I may implement this.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Jul 2004
    Posts
    306
    Awesome help guys thankyou all so much!

  7. #7
    Join Date
    Jun 2004
    Location
    Philly
    Posts
    51
    Quote Originally Posted by upalsen
    open DTS, select "save as" from "Package" menu. Select "structured storage file" in Location drop down. provide name etc. save.

    in the destination machine, right-click on Data-Transformation-Services of EM select "open package". save it with Location = SQL Server.
    I tried HMSCOTT's code, and it yielded the same results as everything else

    it is unable to backup my packages correctly, they get restored with too many incorrect settings


    the only option I have gotten to work is doing a manual "save as" which is explained above

  8. #8
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by xarfox
    ... they get restored with too many incorrect settings ...
    Can you explain/elaborate? What settings are incorrect?

    Be aware that DTS is very environment dependent. You would have to make certain that:

    1. Any drivers that a DTS package would use would be the same on the new server as the old server.

    2. Any custom DTS Tasks would have to be the same on the new server as the old server.

    3. External paths, links and certain files must be identical (for example, an Oracle TNSNames.ora file, or a UNC path)

    4. In some cases security settings will have an impact.

    Regards,

    hmscott
    Have you hugged your backup today?

  9. #9
    Join Date
    Jun 2004
    Location
    Philly
    Posts
    51
    here is a description of the problem:

    if i open this particular dts package and save it to file it is 511B, but if i use any backup program or script i.e. your script, DTS backup, a script i wrote, etc... it is 472B.

    when i open both files in enterprise manager, the 511B file yields the correct package (obviously for migration i would have to change settings but no big deal as long as its the same structure)

    the 472B package is compeltley different, I don't even know how it thinks the translation even comes close. it messes up some success/failure criterias and creates additional connection ??

  10. #10
    Join Date
    Dec 2002
    Posts
    1,245
    hmm, okay, I can't address most of your specific issues. I can only comment on my own experiences and environment.

    I just opened up all of the package "structured" files that I have. I opened them from a different server than the one on which they normally reside.

    I found the following:
    1. In all but one case, the packages restored just fine with no errors.
    2. The comments that I had created using the textbox feature were all missing (ugh). Also, in some cases, the objects were shifted on the designer window (moving from the left side of the screen to the right and vice versa).
    3. In one case, only two VBScript tasks showed in the designer window (out of maybe 40 tasks). I checked the disconnected edit properties, though, and found everything to be present.
    4. When I executed the package (the one with the clear designer window), everything executed in the order in which I would expect it.

    I am running SQL Server 2000 with SP4 (patched with hotfix 2040).

    I will do some more research at work tomorrow. I'm glad you pointed this out; I excercised the recovery process once last year, but I haven't looked at this in a while.

    Regards,

    hmscott
    Have you hugged your backup today?

  11. #11
    Join Date
    Jun 2004
    Location
    Philly
    Posts
    51
    one thing to check would be to look at the size of your structured files for your backup program versus a file that you saved manually

    maybe do this for the most complicated packages to see if there is an issue

    it might just be that my packages are too complicated for a backup program to account for

  12. #12
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    See, 1 night, long, long ago, all of the SQL Server developers fell asleep, and the Access developers snuck in...viola...DTS

    Here's a pleasant thought...there is no DTS in 2k5
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  13. #13
    Join Date
    Jun 2004
    Location
    Philly
    Posts
    51
    hahaha... that is hilarious

    what does 05 use to automate external data flows?

  14. #14
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    http://www.microsoft.com/sql/prodinf...erver2005.mspx

    Look for Intergration Services...they don't even mention DTS, nor do they mention how (or if) it's possible to migrate existing packages.
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  15. #15
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by Brett Kaiser
    See, 1 night, long, long ago, all of the SQL Server developers fell asleep, and the Access developers snuck in...viola...DTS

    Here's a pleasant thought...there is no DTS in 2k5
    Yep, with 2k5, they shot all of the Access developers and brought in the BizTalk developers. Arrrrgh!!!

    On the serious side, I figured what I had done wrong in the earlier example (the one package that would not restore properly): I had a custom task in the package and had not registered the custom task on the server to which I restored the package. Once, I took care of that little task, then the package opened up just fine. My file sizes are also different between the programmatic save versus the manual save. I think that is because the programmatic method is saving version information in addition to the structure. When I go to open the package, I see a list of possible entries to open, starting with the most recently saved version.

    Again, I can't comment or help on your specific scenario; I'm glad for the opportunity to have checked out the script and verified the restore process. It seems to work fine for me.

    :shrug:

    Regards,

    hmscott
    Have you hugged your backup today?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •