Transform from a select to multi-files

Question

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.

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

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

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 1

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 · Answer 2

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 · Answer 3

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