DTS Import from file split field create multiple records

P

Paolo

Hi guys,

I am creating a DTS in SQL server by importing from a tab sep file,
I realized that one field could contain multiple, comma separeted,
values: eg.
Field1 Field2 Field3 Date etc..
Normal:
160 2827863 15489229 2005-03-07 21:10 ....
Exeption:
197 2827867 15200387,2371987239,02109283,23812319,120310928310
2005-03-07 21:19 ....

I'd like to know if it possible to do the following, which conceptually
is not difficult.
So I my idea was while it is copying the Source fields into the
destination ones perform a check and if the field is multple then create
more records in that table:
here is the stupid code I wrote in the DTSTransformation ActiveX
Transformation windows
Function Main()
Dim SplittedCells
Dim CellContent
SplittedCells = Split(DTSSource("Col003"), ",")
For i = LBound(SplittedCells) To UBound(SplittedCells) Step 1
DTSDestination("TaxID") = DTSSource("Col001")
DTSDestination("GeneID") = DTSSource("Col002")
DTSDestination("LastUpdate") = DTSSource("Col004")
DTSDestination("GeneRifText") = DTSSource("Col005")
DTSDestination("PubmedID") = SplittedCells(i)
---> Here there should be an .Addnew-like command...and .Update-like
also <---
Next
Main = DTSTransformStat_Ok
End Function

My question is: is there anybody who knows if it is possible to do...if
it is the correct way...
Thanks in advance...
 
S

Sylvain Lafontaine

Yes, the easiest way would be to create ADO objects inside the DTS' script
and use them to perform the multiple insertions.

I wonder why you didn't thought of asking your question in the
m.p.sqlserver.dts newsgroup?
 
P

Paolo

Sylvain said:
Yes, the easiest way would be to create ADO objects inside the DTS' script
and use them to perform the multiple insertions.
Click to expand...
Ok I will try..thanks for the reply
I wonder why you didn't thought of asking your question in the
m.p.sqlserver.dts newsgroup?
Click to expand...
Actually I didn't see it...
I'll add it to my list!
 
P

Paolo

Sylvain said:
Yes, the easiest way would be to create ADO objects inside the DTS' script
and use them to perform the multiple insertions.

I wonder why you didn't thought of asking your question in the
m.p.sqlserver.dts newsgroup?
Click to expand...
In any case you suggest something like this:
Function Main()
Dim SplittedCells
Dim CellContent
SplittedCells = Split(DTSSource("Col003"), ",")
if (NumberOfElementsInArray >1) Then
Dim objConn 'Create a connection to db
Dim rs 'Create a recordset object
rs.Open
For i = LBound(SplittedCells) To UBound(SplittedCells) Step 1
rs.AddNew
rs.Item.Fields("TaxID") = DTSSource("Col001")
rs.Item.Fields("GeneID") = DTSSource("Col002")
rs.Item.Fields("LastUpdate") = DTSSource("Col004")
rs.Item.Fields("GeneRifText") = DTSSource("Col005")
rs.Item.Fields("PubmedID") = SplittedCells(i)
rs.Update
Next
Else
DTSDestination("TaxID") = DTSSource("Col001")
DTSDestination("GeneID") = DTSSource("Col002")
DTSDestination("LastUpdate") = DTSSource("Col004")
DTSDestination("GeneRifText") = DTSSource("Col005")
DTSDestination("PubmedID") = SplittedCells(i)
End if
Main = DTSTransformStat_Ok
End Function

Right?
 
S

Sylvain Lafontaine

Yes, why not if it works?

You can also sent SQL insert commands directly to the SQL-Server via the
command object instead of using an intermediary recordset object; as this
will save you some round-trip to the server and increase the performance.
 
P

Paolo

Sylvain said:
Yes, why not if it works?

You can also sent SQL insert commands directly to the SQL-Server via the
command object instead of using an intermediary recordset object; as this
will save you some round-trip to the server and increase the performance.
Click to expand...
I solved by using a global variable and skipping from fetching the next
row (DTSTransformStat_SkipFetch) as suggested in this article:
http://www.sqldts.com/default.aspx?266

So first I created a Global variable in package properties called ArrayIndex

then I used this code:
Function Main()
'yes everytime you redo the split
Dim SplittedCells
Dim CellContent
SplittedCells = Split(DTSSource("Col003"), ",")
If CInt(DTSGlobalVariables("ArrayIndex").Value) <=
UBound(SplittedCells) Then
DTSDestination("TaxID") = DTSSource("Col001")
DTSDestination("GeneID") = DTSSource("Col002")
DTSDestination("LastUpdate") = DTSSource("Col004")
DTSDestination("GeneRifText") = DTSSource("Col005")

DTSDestination("PubmedID") =
SplittedCells(DTSGlobalVariables("ArrayIndex").Value)

DTSGlobalVariables("ArrayIndex").Value =
CInt(DTSGlobalVariables("ArrayIndex").Value) + 1

Main = DTSTransformStat_SkipFetch

Else
DTSGlobalVariables("ArrayIndex").Value = 0
Main = DTSTransformStat_SkipInsert
End If
End Function
 
Top