Transform from a select to multi-files

  • Hi, this is the problem:

    I have a transformation where the source is a "select ..." and the destination is a file with a name according to the value of a column.

    Example:

    the query return:

    Col_X FileSuffix

    ----- ---

    K_010 A

    K_020 A

    K_030 B

    K_040 B

    The output of the first 2 rows is a file with name c:\temp\File_A.txt, the others is c:\temp\File_B.txt .

    I have tried to change, with an activeX transformation, the property "DataSource" of the fileout connection, but doesn't work.

    Here the sample code:

    str = "C:\temp\File_" & DTSSource("FileSuffix") & ".txt"

    Set oPKG = DTSGlobalVariables.Parent

    Set cnFiles = oPKG.Connections(2)

    cnFiles.DataSource = str

    ------------

    Thanks.

  • Build multiple transforms. select * where col = A, etc.

    You could build a generic package and then drive this with another package that checks for all values of your column and changes the filename and query. Actually, I may write an articel on this, it's kind of cool.

    Steve Jones

    steve@dkranch.net

  • I agree with Steve it would be easier. But if you do not want to go that way I will be glad to find code that works like this but I cannot delimit the data just output.

  • We have implemented the Steve solution, and it works well.

    The only thing is that running the "driver" package there are no detailed information on the generic package called; e.g. number of rows processed or detailed error occurred.

    Thanks for the suggest

    Ciao

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply