DTS Import from file split field create multiple records

  • Thread starter Thread starter Paolo
  • Start date Start date
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...
 
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?
 
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!
 
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?
 
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.
 
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
 
Back
Top