Exporting to Muliple file from a single table using DTS

[manthenasri]

Hi Friends

I have been trying to solve this problem for the last 2 days but no luck.

Here is the problem that I am facing.

The task on had is to transfer data from a single table (the source) to multiple files (Destination) based on the record type.

I have tried changing the Datasource property of the Text File Connection object dynamically by using an ActiveX Script. But the data is still being written only to one file.

Can anyone please help me.

Thanks in advance.

Srinivas.

[MAK]

please post your activex code, let me take a look.

[k2kataria]

Hi,

Just changing the data source of the text file connection putting out ur data to a file will write to the same file.

You need to change both the data source and the destination file. Try having multiple Text file Destination components to make it simple. You can still have the same data source. Just filter the data set being passed to them before u activate each of the destination files.

Let me know if this works.

Ketan

[manthenasri]

Hai MAK

I took a long time to relpy as it was a long weekend for us. Sorry for the delay.

Here is the piece of code that i am using


'************************************************* *********************
' Visual Basic Transformation Script
'************************************************* ***********************

Dim SSN
Dim RATE
Dim EDATE
Dim RVAC
Dim REXTVAC
Dim REXTVACDATE
Dim RSIC
Dim REME
Dim RDATE
Dim INT_CODE

Dim lOldBusinessUnit
Dim lNewBusinessUnit

Dim oPKG
Dim oConnection
Dim oDataPump

' Copy each source column to the destination column

Function Main()


lNewBusinessUnit = DTSSource("INT_CODE")

If lNewBusinessUnit <> lOldBusinessUnit then
Set oPKG = DTSGlobalVariables.Parent

Set oDataPump = oPkg.Tasks("DTSTask_DTSDataPumpTask_1").CustomTask
oDataPump.DestinationObjectName = "\\servername\DFS\iDataInt\Labor\OUT\"&lNewBusines sUnit&".ben"
Set oDataPump = Nothing

Set oConnection= oPKG.Connections(".ben files")
oConnection.Datasource ="\\servername\DFS\iDataInt\Labor\OUT\" & lNewBusinessUnit & ".ben"
Set oConnection = Nothing

lOldBusinessUnit = lNewBusinessUnit
msgbox "done"
End if

DTSDestination("SSN") = DTSSource("SSN")
DTSDestination("RATE") = DTSSource("RATE")
DTSDestination("EDATE") = DTSSource("EDATE")
DTSDestination("RVAC") = DTSSource("RVAC")
DTSDestination("REXTVAC") = DTSSource("REXTVAC")
DTSDestination("REXTVACDATE") = DTSSource("REXTVACDATE")
DTSDestination("RSIC") = DTSSource("RSIC")
DTSDestination("REME") = DTSSource("REME")
DTSDestination("RDATE") = DTSSource("RDATE")
DTSDestination("INT_CODE") = DTSSource("INT_CODE")

Main = DTSTransformStat_OK

End Function

Function PreSourceMain()

lOldBusinessUnit = 0
lNewBusinessUnit = 0

PreSourceMain = DTSTransformstat_OK
End Function


Srinivas.