Friday, February 17, 2012

DTS import/export Question

When importing a flat-text file, is it possible within the import/export wizard to tie more than one column as a DTSSource?

As an example, in an online store, we have columns from the flat-file like so:

"ID","Name","Description","Rarity","Price","Image","Weight"

And in the destination database, the columns are:

"ProductID","Name","Description","Price","Image","Weight"

I'd like to do the following within the dts wizard:
'********************************************************
Function Main()
DTSDestination("ProductID") = DTSSource("id")
DTSDestination("Name") = DTSSource("name")
DTSDestination("Description") = DTSSource("description") &"<br /><br />"& DTSSource("rarity")
DTSDestination("tImage") = DTSSource("image")
DTSDestination("Price") = DTSSource("Price")
DTSDestination("Weight") = DTSSource("Weight")
Main = DTSTransformStat_OK
End Function
'********************************************************

Obviously the description line errors out. Any suggestion on how to accomplish this for an import?You could import 'rarity' as another column, then combine the two in your SELECT query:

SELECT (description + '<br /><br />' + rarity) AS description ...
Or import the rarity as a column and then run SQL to combine the two
UPDATE mytable SET description = (description + '<br /><br />' + rarity)...
of course you would have to provide logic to not combine previously combined columns. I realize both of these options contain reduntant data, I guess you could add a temp column, import,combine,delete temp column.

No comments:

Post a Comment