Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««123

Portable DTS Packages Expand / Collapse
Author
Message
Posted Monday, May 10, 2004 9:37 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, June 1, 2009 7:06 PM
Points: 172, Visits: 11
This article came along at an opportune time as I was working on a DTS package that will be moved to production.  My only problem was with the diagram that showed the dynamic properties box directly connected to a connection object.  My version of DTS would not allow this.  I got an "Invalid precedence" error message.  I was able to make it work when I connected the task to the the first "Execute SQL" task.  I belive this has the same affect as the steps outlined in the article.
Post #115183
Posted Friday, May 20, 2005 5:53 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, July 21, 2014 5:17 AM
Points: 129, Visits: 179
Great article!

Very easy to follow. I was looking for something like this several months ago and could not find it on the web. I end up writing a VB app to run it. Now I am going to change my DTS.

Thanks
Post #184057
Posted Friday, May 20, 2005 6:03 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, October 17, 2012 7:25 AM
Points: 30, Visits: 208

Good Article. I wish there was something like it when I first started doing portable DTS work.

Chalk me up as another fan of UDL and INI files.

To add to andyj93's comments, you can write a failirly simple vbscript to chop out the 1st and 2nd parts of the three part name and stick it into an ActiveX script in your package (runs early). That way you do not need to do the editting.




Best Regards,

Mark Tierney

Post #184060
Posted Friday, May 20, 2005 7:35 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, July 27, 2010 7:51 PM
Points: 1,244, Visits: 3
This is exactly what we do where I work. For those that don't like the idea of running DTS packages from the command line, try scheduling a DTS package as a job. You'll find that the job itself uses DTSRun.exe, so there isn't much difference.

We wrap a substantial but not complicated DOS command scripts around the DTSRun call too. We get a lot of flexibility and our staff who don't know SQL can make script adjustments and updates without much problem.



Post #184106
Posted Friday, May 20, 2005 7:42 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 18, 2014 6:10 AM
Points: 137, Visits: 131

Hey andyj93

How did you get rid of the three part naming in the tranformation?

I have the source from ODBC AS400 and destination is the SQL server. I am able to set the global varible for the connections but not the transformation. Any inmput is appreciated.

THanks.




rb
Post #184109
Posted Friday, May 20, 2005 8:20 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, January 25, 2012 8:14 AM
Points: 567, Visits: 512
Excellent, thanks.  No more resetting of transformations, YES!!
Post #184127
Posted Friday, May 20, 2005 8:41 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, June 9, 2009 8:13 AM
Points: 75, Visits: 3

Once again, the submission is SPOT ON. Good job and thanks for the very well written and easy to understand style!

 




Cheers,

Alex


Rogue DBA
Post #184136
Posted Friday, May 20, 2005 11:27 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, October 17, 2012 7:25 AM
Points: 30, Visits: 208

Hi BankaR,

 

I added this code in an ActiveX script at the top of my package:

 

For i = 1 to oPackage.Tasks.Count                                                  
                                                                                   
' Verify that this task is a transformation task by interrogating for default name 
                                                                                   
 If Left(oPackage.Tasks(i).Name, 19) = "DTSTask_DTSDataPump" Then                  
  PumpID = i                                                                       
  set oTrans = oPackage.Tasks(PumpID)                                              
                                                                                   
' Get the Destination tablename and find the 2nd qualifier location                
                                                                                   
  oTask = oTrans.CustomTask.DestinationObjectName                                  
  tst = InStr(1 ,oTask,"dbo")                                                      
  tst2 = tst - 2                                                                   
                                                                                   
' Use calculated starting point to strip off the table creator and name            
' from the server name                                                             
                                                                                   
tempname = Right(oTask,Len(oTask) - tst2)                                          
                                                                                   
' Apply stripped name back to destination name                                     
                                                                                   
oTrans.CustomTask.DestinationObjectName = tempname                                 
                                                                                   
  set oTrans = nothing                                                             
                                                                                   
 End If                                                                            
Next

I use this loop to also change other aspects of the Tasks (just add new code to loop) when I have many of them in a single package. It allows you to avoid opening each Task.




Best Regards,

Mark Tierney

Post #184226
Posted Friday, May 20, 2005 2:06 PM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 12:10 PM
Points: 49, Visits: 279
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=99&messageid=184282

BI Guy
Post #184287
Posted Monday, December 3, 2007 5:46 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, December 3, 2007 5:48 AM
Points: 1, Visits: 1
Very useful article.

Initially I had problems with saving the dts package as the article quotes

dtsrun /Fmydtspkg /Nmydtspkg

but /F is actually the full filename e.g c:\dtspackages\mypkg.dts
and /N is the name of the package e.g. mypkg

I also initially had problems realising that 'structured storage' simply means a disk file and that is what we are refering to.

Note that your structured storage holds a new version of the package each time it is saved so you need to delete the file before each save or dtsrun will tell you there are multiple packages to choose from
Post #428679
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse