March 3, 2009 at 7:59 am
A DTS package defines 60 tasks to transfer data from an Access Database to an SQL Server 2K Database.
The GUI shows:
"Source Connection" -> Taks -> Destination Connection".
To change the source table names (a different prefix in the name) and to adapt the package to a seocnd, identical (except for name, of course) destination database, for each and every task is turning out to be a major pain.
Is there anyway to get at the "script" of the DTS package and make a massive search and reply, instead of having to reopen each connection, task and destination object in the GUI ?
March 3, 2009 at 2:44 pm
You can save the package as a Visual Basic file, edit it, and save it back to SQL Server. You could also use Disconnected Edit in DTS Designer to modify the tasks and connections but you still have to change each task and/or connection.
Greg
March 4, 2009 at 7:37 am
OK,
I am working in Entreprise Manager / Microsoft Management Console.
In there, I see no option allowing to save as VB file. How do I go aout it ?
Regards
March 4, 2009 at 9:14 am
Open the package in DTS Designer, click on "Package" on the toolbar, "Save as..." and choose "Visual Basic File" in the Location dropdown.
Greg
March 5, 2009 at 9:08 am
Thanks for your help.
I completely missed checking what the "Location" was for. Too much in the Microsoft mindset of "Save as Type".
Learn something everyday.
Best regards
March 5, 2009 at 9:27 am
For anyone else interested, this is what I found out:
0. Out of curiousity, tried the IFCode function to format the code so I would not lose the indentation. Learned something new in the process.
1. The VB file is stored at the following location:
[font="Courier New"]C:\Program Files\Common Files\ODBC\Data Sources[/font]
of the local machine using the MMC console, NOT the servers' own drive.
2. This ends up as rather large file: 38312 lines ( this was for 32 tasks, not 60 !) 273 pages !
Each column involved requires sevral lines to describe it. For instance:
[font="Courier New"]Public Sub oCustomTask30_Trans_Sub29(ByVal oCustomTask30 As Object)
Dim oTransformation As DTS.Transformation2
Dim oTransProps as DTS.Properties
Dim oColumn As DTS.Column
Set oTransformation = oCustomTask30.Transformations.New("DTS.DataPumpTransformCopy")
oTransformation.Name = "DTSTransformation__29"
oTransformation.TransformFlags = 63
oTransformation.ForceSourceBlobsBuffered = 0
oTransformation.ForceBlobsInMemory = False
oTransformation.InMemoryBlobSize = 1048576
oTransformation.TransformPhases = 4
Set oColumn = oTransformation.SourceColumns.New("changed_by" , 1)
oColumn.Name = "changed_by"
oColumn.Ordinal = 1
oColumn.Flags = 102
oColumn.Size = 3
oColumn.DataType = 130
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True
oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.DestinationColumns.New("changed_by" , 1)
oColumn.Name = "changed_by"
oColumn.Ordinal = 1
oColumn.Flags = 104
oColumn.Size = 3
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True
oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing
Set oTransProps = oTransformation.TransformServerProperties
Set oTransProps = Nothing
oCustomTask30.Transformations.Add oTransformation
Set oTransformation = Nothing
End Sub[/font]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply