Can DTS include a static column?

[TheDrider]

First, I'd like to apologize for posting a DTS question here. I'm not sure how many SQL gurus actually follow the DTS threads...


I am creating dynamic DTS packages in VB to import an Excel spreadsheet into a database table. Since I could not get DTS to work with a #TEMP_TABLE, I upload the data into a static table.

The problem lies in that I want more than one user to be able to import data at a time. (The data is manipulated a bit and assigned to a user after it's imported.)

How can I set up another desitination column that simply inserts a static USER_ID (which is known during DTS creation at VB run-time)?

The code below is standard auto-generated from SQL Server. I tweaked it a bit to make it dynamic.

Public Function StartColumnDTS(col_id As Integer, fileName As String, dtsDB As String, dtsSVR As String, dtsUID As String, dtsPWD As String) As String
    Set goPackage = goPackageOld

    Call SetupPackage(goPackage, fileName, dtsDB, dtsSVR, dtsUID, dtsPWD)
    Call Task_Sub1(col_id, goPackage)

    ' Save or execute package
    'goPackage.SaveToSQLServer "(local)", "sa", ""
    goPackage.Execute
    StartColumnDTS = tracePackageError(goPackage)
    goPackage.UnInitialize

    'to save a package instead of executing it, comment out the executing package lines above and uncomment the saving package line
    Set goPackage = Nothing
    Set goPackageOld = Nothing
End Function


'------------- define Task_Sub1 for task DTSTask_DTSDataPumpTask_1 (Transform Data Task: undefined)
Private Sub Task_Sub1(col_id As Integer, ByVal goPackage As Object)
    Dim oTask As DTS.Task
    Dim oLookup As DTS.Lookup
    Dim oCustomTask1 As DTS.DataPumpTask2

    Set oTask = goPackage.Tasks.New("DTSDataPumpTask")
    oTask.Name = "DTSTask_DTSDataPumpTask_1"
    Set oCustomTask1 = oTask.CustomTask

    oCustomTask1.Name = "DTSTask_DTSDataPumpTask_1"
    oCustomTask1.Description = "Transform Data Task: undefined"
    oCustomTask1.SourceConnectionID = 1
    oCustomTask1.SourceObjectName = "FP_Column_Import$"
    oCustomTask1.DestinationConnectionID = 2
    oCustomTask1.DestinationObjectName = "FP_COLUMN_DATA_IMPORT"
    oCustomTask1.ProgressRowCount = 1000
    oCustomTask1.MaximumErrorCount = 0
    oCustomTask1.FetchBufferSize = 1
    oCustomTask1.UseFastLoad = True
    oCustomTask1.InsertCommitSize = 0
    oCustomTask1.ExceptionFileColumnDelimiter = "|"
    oCustomTask1.ExceptionFileRowDelimiter = vbCrLf
    oCustomTask1.AllowIdentityInserts = False
    oCustomTask1.FirstRow = "0"
    oCustomTask1.LastRow = "0"
    oCustomTask1.FastLoadOptions = 5
    oCustomTask1.ExceptionFileOptions = 0
    oCustomTask1.DataPumpOptions = 1

    Call oCustomTask1_Trans_Sub1(oCustomTask1)  ' OBAN
    Call oCustomTask1_Trans_Sub2(oCustomTask1)  ' PEC
    Call oCustomTask1_Trans_Sub3(oCustomTask1)  ' EEIC
    Call oCustomTask1_Trans_Sub4(oCustomTask1)  ' RCCC
    Call oCustomTask1_Trans_Sub5(oCustomTask1)  ' ESP
    Call oCustomTask1_Trans_Sub6(oCustomTask1)  ' PROG
    Call oCustomTask1_Trans_Sub7(oCustomTask1)  ' AMOUNT
    
    If col_id = 4 Then
        Call oCustomTask1_Trans_Sub8(oCustomTask1)  ' CCN DESC
    End If
    
    goPackage.Tasks.Add oTask
    Set oCustomTask1 = Nothing
    Set oTask = Nothing
End Sub


Public Sub oCustomTask1_Trans_Sub1(ByVal oCustomTask1 As Object)

        Dim oTransformation As DTS.Transformation2
        Dim oTransProps As DTS.Properties
        Dim oColumn As DTS.Column
[COLOR=red]
        Set oTransformation = oCustomTask1.Transformations.New("DTS.DataPumpTransformCopy")
                oTransformation.Name = "DTSTransformation__1"
                oTransformation.TransformFlags = 63
                oTransformation.ForceSourceBlobsBuffered = 0
                oTransformation.ForceBlobsInMemory = False
                oTransformation.InMemoryBlobSize = 1048576
                oTransformation.TransformPhases = 4
                
                Set oColumn = oTransformation.SourceColumns.New("OBAN", 1)
                        oColumn.Name = "OBAN"
                        oColumn.Ordinal = 1
                        oColumn.Flags = 102
                        oColumn.size = 255
                        oColumn.DataType = 130
                        oColumn.Precision = 0
                        oColumn.NumericScale = 0
                        oColumn.Nullable = True
                        
                oTransformation.SourceColumns.Add oColumn
                Set oColumn = Nothing
[\color]
                Set oColumn = oTransformation.DestinationColumns.New("OBAN_TX", 1)
                        oColumn.Name = "OBAN_TX"
                        oColumn.Ordinal = 1
                        oColumn.Flags = 104
                        oColumn.size = 2
                        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

        oCustomTask1.Transformations.Add oTransformation
        Set oTransformation = Nothing

End Sub
[\code]

I just need to add another Column Transformation for the user_id.  I presume the code in red is the are to change, but not sure what to set it to.

Thanks for any help you can throw at me.

Also, anyone know why the TGML markup didn't work here?

 

[SQLDenis]

populate a global variable and use that as a value

something like this
DTSDestination("UserID") = DTSGlobalVariables( "UserID" ).Value

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[TheDrider]

After a brief redirect, I'm finally able to get back to this problem...

I've created a global variable from a param passed into main function:

    Dim objGlobal   As DTS.GlobalVariable
    goPackage.GlobalVariables.AddGlobalVariable "USER_ID", user_id

I'm not sure how to use it though. Do I need to create another Transformation object for the destination column?
I've looked at the DynamicPropertiesTask object also but I'm not sure if that's what I need.

I've got all the pieces -- I'm just not sure how to tie them together. Most of the examples show me how to use gobals to modify existing Query Tasks, but not in line with existing Transformation tasks.

How can I add my global variable to the Source data to be copied?

Thanks again.