sql server dts: Hi Allan,

With your help I have successfully loaded a big text file into SQL database using your Article “Processing The Same Row More Than Once” (http://www.sqldts.com/default.aspx?266

I have one more question to ask you regarding to the DTS Speed

The data I have in source has 10,000 rows, and the data generated after the transformation in destination will be 10,000*120 = 1,200,000 record

The process takes me about 7 minutes to finish on a computer with 2GHz and 512M RAM.

But if I comment out the following lines (7,8,9,10,11,12,13,14,15,17,18,19,20,21,22,27,28), it only takes me about 3 minutes to finish.
It seems to me that SQL Server will need about 3 minutes to generate those 1,200,000 records, and another 4 minutes on the rest of the data transformation

Does it still have room to speed up this Data Transaction process? Or the total 7 minutes is reasonable?


line1 Function Main(
line
line3 Dim strInde
line4 IF DTSGlobalVariables("gv_Counter").Value <= 242 THE
line5 DTSDestination("Code") = DTSSource("col104"
line6
line7 'get last day of the mont
line8 DTSDestination("AsOfDate") = DTSGlobalVariables("gv_Month").Value & "/" & Day(DateSerial(DTSGlobalVariables("gv_Year").Value, DTSGlobalVariables("gv_Month").Value+1,0)) & "/" & DTSGlobalVariables("gv_Year").Valu
line
line10 'the value of this colume in source would be empty, but the destination is decimal fiel
line11 strIndex = "col" & CStr(DTSGlobalVariables("gv_Counter").Value
line12 If IsNumeric(DTSSource(strIndex)) The
line13 DTSDestination("Return") = DTSSource(strIndex
line14 End I
line1
line16 DTSGlobalVariables("gv_Counter").Value = CInt(DTSGlobalVariables("gv_Counter").Value) +
line17 IF DTSGlobalVariables("gv_Month").Value = 12 The
line18 DTSGlobalVariables("gv_Month").Value =
line19 DTSGlobalVariables("gv_Year").Value = DTSGlobalVariables("gv_Year").Value +
line20 ELS
line21 DTSGlobalVariables("gv_Month").Value = DTSGlobalVariables("gv_Month").Value +
line22 END I
line23 Main = DTSTransformStat_SkipFetc
line24 ELS
line25 'the start column is col123 and end column is col24
line26 DTSGlobalVariables("gv_Counter").Value = 12
line27 DTSGlobalVariables("gv_Month").Value = 1
line28 DTSGlobalVariables("gv_Year").Value = 199
line29 Main = DTSTransformStat_SkipInser
line30 END I
line3
line32 End Functio

7 minutes ?

You are reading from a text file ? Not the fastest of drivers but the only
real thing I can see that may speed you up is a BULK INSERT into a SQL
Server table of the text file first and then use the SQL Server driver which
is probably faster.

The thing about this is that you are processing every row 120 times so this
will take some time as you do have logic in there as well.

--

----------------------------
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org



[quoted text, click to view]
database using your Article "Processing The Same Row More Than Once"
(http://www.sqldts.com/default.aspx?266)
[quoted text, click to view]
(7,8,9,10,11,12,13,14,15,17,18,19,20,21,22,27,28), it only takes me about 3
minutes to finish.
[quoted text, click to view]
1,200,000 records, and another 4 minutes on the rest of the data
transformation.
[quoted text, click to view]
& "/" & Day(DateSerial(DTSGlobalVariables("gv_Year").Value,
DTSGlobalVariables("gv_Month").Value+1,0)) & "/" &
DTSGlobalVariables("gv_Year").Value
[quoted text, click to view]