Transform from a select to multi-files

• g.firpo

  Old Hand

  Points: 366

  More actions

  February 8, 2002 at 8:03 am

  #104871

  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.

• Steve Jones - SSC Editor

  SSC Guru

  Points: 738043

  More actions

  February 8, 2002 at 10:51 am

  #423542

  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

• Antares686

  SSC Guru

  Points: 125444

  More actions

  February 8, 2002 at 11:40 am

  #423558

  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.

• g.firpo

  Old Hand

  Points: 366

  More actions

  February 13, 2002 at 10:32 am

  #423769

  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