sql server dts: Instead of writing "Hello" I want it to read a value from a cell in the
excel sheet I am running this from. The code is in VBA:


Public Sub oCustomTask1_Trans_Sub3(ByVal oCustomTask1 As Object)

Dim oTransformation As DTS.Transformation2
Dim oTransProps As DTS.Properties
Dim oColumn As DTS.Column
Set oTransformation =
oCustomTask1.Transformations.New("DTSPump.DataPumpTransformScript")
oTransformation.Name = "DTSTransformation__2"
oTransformation.TransformFlags = 63
oTransformation.ForceSourceBlobsBuffered = 0
oTransformation.ForceBlobsInMemory = False
oTransformation.InMemoryBlobSize = 1048576
oTransformation.TransformPhases = 4

Set oColumn =
oTransformation.DestinationColumns.New("Product", 1)
oColumn.Name = "Product"
oColumn.Ordinal = 1
oColumn.Flags = 120
oColumn.Size = 20
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oTransProps = oTransformation.TransformServerProperties

oTransProps("Text") =
"'**********************************************************************" &
vbCrLf
oTransProps("Text") = oTransProps("Text") & "' Visual Basic
Transformation Script" & vbCrLf
oTransProps("Text") = oTransProps("Text") &
"'************************************************************************" &
vbCrLf
oTransProps("Text") = oTransProps("Text") & "' Copy each
source column to the destination column" & vbCrLf
oTransProps("Text") = oTransProps("Text") & "Function
Main()" & vbCrLf
oTransProps("Text") = oTransProps("Text") & "
DTSDestination(""Product"") = ""hello""" & vbCrLf
oTransProps("Text") = oTransProps("Text") & " Main =
DTSTransformStat_OK" & vbCrLf
oTransProps("Text") = oTransProps("Text") & "End Function"
oTransProps("Language") = "VBScript"
oTransProps("FunctionEntry") = "Main"

Set oTransProps = Nothing

oCustomTask1.Transformations.Add oTransformation
Set oTransformation = Nothing

End Sub

Hello Gallardo,


That will be completely different and not really resemble the code you have
here which looks to be a VB.bas export of something you have.

Here is what I would do.

You are calling this from XL right?

OK have XL read the value in the cell. Pass it into the package as a Global
Variable value.
The transform data task will simply set the destination value to the value
of the global variable.

It makes sense to have XL read the value seeing as you are in there already
rather than going in to DTS and having DTS come back out.

--

Allan Mitchell
http://wiki.sqlis.com | http://www.sqlis.com | http://www.sqldts.com |
http://www.konesans.com

[quoted text, click to view]