Great article as this is something i have a need for. Since i am copying tables from server/db A to server/db B, is there a way to dynamically select all tables that begin with the letter 't' for example.In other words, i want to copy all tables and the list of tables keeps getting increased because of constant development. Any ideas will be appreciated
Vey nice, I was really looking for something and was thinking that SQL server must be having this sort of functionality. Lack of time couldn't give time to explore the things.
This technique will be definitely helpful to me in future....-:)
Good article, for PB users the pipeline object is an alternate approach, especially for the person that wants to dynamiclly get the table names based on some query, also DTS is not very forgiving of data conversion errors, a nice followon article about handling conversion errors and overflow/underflow during DTS copy from .csv file would be helpful
We have hundreds of databases distributed all over the world. Anyone tried using DTS to update the schema of distributed databases like these? Is dtsrun.exe distributable?
Mr or Mrs. 500
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.
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.
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.
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.
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.
Excellent, thanks. No more resetting of transformations, YES!!
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
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.
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
Viewing 15 posts - 16 through 29 (of 29 total)