May 29, 2006 at 2:40 am
Hi peeps,
I have written a DTS package that exports tables configured in an ini file. I have on Transfrom Data Task, that gets executed for every configured table. I have the following script that re-assign the transformation for the Transform Data Task. This causes the Transformation Data Task to fail.
'Code
Option Explicit
Function Main()
DTSGlobalVariables("CurrentObjectNumber").Value = DTSGlobalVariables("CurrentObjectNumber").Value + 1
Dim intCurrentObjectNumber
intCurrentObjectNumber = DTSGlobalVariables("CurrentObjectNumber").Value
Dim intObjectCount
intObjectCount = DTSGlobalVariables("ObjectCount").Value
Dim arrObjectNames
arrObjectNames = Split(DTSGlobalVariables("ObjectNames").Value, ",", intObjectCount)
Dim strSectionName
strSectionName = arrObjectNames(intCurrentObjectNumber)
'get ObjectName and DestinationFileName from ini file
Dim strObjectName
strObjectName = GetINIString(strSectionName, "ObjectName", "", DTSGlobalVariables("ConfigurationFile").Value)
Dim objFso
Set objFso = CreateObject("Scripting.FileSystemObject")
Dim strDestinationFileName
strDestinationFileName = objFso.BuildPath(DTSGlobalVariables("DestinationFolder").Value, GetINIString(strSectionName, "DestinationFileName", "", DTSGlobalVariables("ConfigurationFile").Value))
Const DTSTransformFlag_AllowLosslessConversion = 512
Dim objPkg
Set objPkg = DTSGlobalVariables.Parent
Dim objDataPumpTask
Set objDataPumpTask = objPkg.Tasks("Export Data Task").CustomTask
Dim objDestination
Set objDestination = objPkg.Connections("Destination Connection")
' remove existing transformations
Dim intIndex
For intIndex = objDataPumpTask.Transformations.Count to 1 step - 1
objDataPumpTask.Transformations.Remove intIndex
Next
'specify new destination information
With objDestination
.DataSource = strDestinationFileName
End With
'specify new source information
With objDataPumpTask
.SourceObjectName = strObjectName
.DestinationObjectName = strDestinationFileName
End With
'create and set transformation
Dim objTransformation
Set objTransformation = objDataPumpTask.Transformations.New("DTS.DataPumpTransformCopy")
With objTransformation
.Name = "CopyColumns"
.TransformFlags = DTSTransformFlag_AllowLosslessConversion
End With
objDataPumpTask.Transformations.Add objTransformation
Set objDataPumpTask = nothing
Set objDestination = nothing
Set objFso = nothing
Set objPkg = nothing
Set objTransformation = nothing
Main = DTSTaskExecResult_Success
End Function
Function GetINIString(Section, KeyName, Default, FileName)
Dim INIContents, PosSection, PosEndSection, sContents, Value, Found
'Get contents of the INI file As a string
INIContents = GetFile(FileName)
'Find section
PosSection = InStr(1, INIContents, "[" & Section & "]", vbTextCompare)
If PosSection>0 Then
'Section exists. Find end of section
PosEndSection = InStr(PosSection, INIContents, vbCrLf & "[")
'?Is this last section?
If PosEndSection = 0 Then PosEndSection = Len(INIContents)+1
'Separate section contents
sContents = Mid(INIContents, PosSection, PosEndSection - PosSection)
If InStr(1, sContents, vbCrLf & KeyName & "=", vbTextCompare)>0 Then
Found = True
'Separate value of a key.
Value = SeparateField(sContents, vbCrLf & KeyName & "=", vbCrLf)
End If
End If
If isempty(Found) Then Value = Default
GetINIString = Value
End Function
'Separates one field between sStart And sEnd
Function SeparateField(ByVal sFrom, ByVal sStart, ByVal sEnd)
Dim PosB: PosB = InStr(1, sFrom, sStart, 1)
If PosB > 0 Then
PosB = PosB + Len(sStart)
Dim PosE: PosE = InStr(PosB, sFrom, sEnd, 1)
If PosE = 0 Then PosE = InStr(PosB, sFrom, vbCrLf, 1)
If PosE = 0 Then PosE = Len(sFrom) + 1
SeparateField = Mid(sFrom, PosB, PosE - PosB)
End If
End Function
'File functions
Function GetFile(ByVal FileName)
Dim FS: Set FS = CreateObject("Scripting.FileSystemObject")
'Go To windows folder If full path Not specified.
If InStr(FileName, ":\") = 0 And Left (FileName,2)<>"\\" Then
FileName = FS.GetSpecialFolder(0) & "\" & FileName
End If
On Error Resume Next
GetFile = FS.OpenTextFile(FileName).ReadAll
End Function
Function WriteFile(ByVal FileName, ByVal Contents)
Dim FS: Set FS = CreateObject("Scripting.FileSystemObject")
'On Error Resume Next
'Go To windows folder If full path Not specified.
If InStr(FileName, ":\") = 0 And Left (FileName,2)<>"\\" Then
FileName = FS.GetSpecialFolder(0) & "\" & FileName
End If
Dim OutStream: Set OutStream = FS.OpenTextFile(FileName, 2, True)
OutStream.Write Contents
End Function
The following error is returned: A DTSTransformCopy must specify no columns (signifying a sequential 1-to-1 mapping of all columns) or the same number of source and destination columns.
Does anybody know how to dynamically assign "DataPumpTransformCopy" properties.
Thanks in advance.
May 29, 2006 at 12:11 pm
You may want to take a look here:
May 30, 2006 at 2:34 pm
what does "Hi peeps," mean?
June 1, 2006 at 7:33 am
Pam Abdulla - Thanks for the responce but it doesn't seem to work the same. Darren uses a table/view to table/view pump. I am doing a table/view to delimited file.
I've been trying to solve this for about 3 days now and cannot seem to get it resolved. I find alot of post with this problem, but no solutions though.
ShellyP - 'peeps" is basically the same as "people".
November 23, 2007 at 9:37 pm
Did anyone find the answer for above question ? I am facing the same problem...
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply