Data Driven Query

Question

Data Driven Query

mebjen

SSC Veteran

Points: 219
More actions
January 5, 2004 at 9:19 am

#177390

DTS Guru,
Is it possible, and if so, how does one increase the file size that a DDQ can process? Currently, my DDQ can only process up to 49,500 rows of data from a .txt file. I would like to increase it to 100,000 rows.

mb

Viewing 15 posts - 1 through 14 (of 14 total)

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

noeld SSC Guru Points: 96590 More actions · Answer 1

AFAIK there is no limit in the number of rows unless you have explicitly set it on the option tab either by max error or max row

* Noel

mebjen SSC Veteran Points: 219 More actions · Answer 2

noeId,

I just checked, and maxError = 0, fetchBuffer Size = 1, firstRow = 0 and lastRow = 0 - which I belive are all default settings. Do you have any ideas as to why when I try processing a file larger than 49,500 I get an Error Message?

thanks MB

mdaniel SSCarpal Tunnel Points: 4616 More actions · Answer 3

mdaniel

SSCarpal Tunnel

Points: 4616

January 5, 2004 at 12:57 pm

#488697

Can you post the error message you are getting?

mebjen SSC Veteran Points: 219 More actions · Answer 4

mdaniel,

Here is the error message I get when the file is over 49,500 rows - what I have been doing is splitting the file in have and then processing - which is an indication to me that there really isn't a problem in 'Col011' - Your thoughts?

thanks MB

Step Error Source: Microsoft Data Transformation Services (DTS) Data Pump

Step Error Description:Column name 'Col011' was not found.

Step Error code: 80042008

Step Error Help File:sqldts80.hlp

Step Error Help Context ID:0

noeld SSC Guru Points: 96590 More actions · Answer 5

Can you check the Source ROW at which is reporting the Error?

* Noel

mebjen SSC Veteran Points: 219 More actions · Answer 6

noeId,

I'm not really sure of how to go about that, but what I can tell you is that if I take the file and make it 49,499 rows it process fine.

mdaniel SSCarpal Tunnel Points: 4616 More actions · Answer 7

If you process the 49500 row alone, does it give the same error?

Also, check that row to see if it doesn't have any special character in it.

mebjen SSC Veteran Points: 219 More actions · Answer 8

mdaniel,

If I process the 49500 row alone - everything is fine - process as expected.

There is no difference between the 49499 row and the 49500 in regards to special characters.

mb

noeld SSC Guru Points: 96590 More actions · Answer 9

- Are you running any scripts?

- what Patch level is your Server at?

- can you let it run with the Full file and in the option task select source rows error

- have you thought about Bulkinsert Task as a workaround ?

* Noel

mebjen SSC Veteran Points: 219 More actions · Answer 10

noeId,

1)Yes, I am running a script -

'**********************************************************************

' Visual Basic Transformation Script

'************************************************************************

' Copy each source column to the destination column

Function Main()

Dim SC

Dim col1Id

SC = DTSSource("Col001")

If isDate(SC) then

DTSDestination("fld06") = DTSSource("Col011")

DTSDestination("fld05") = DTSSource("Col010")

DTSDestination("fld04") = DTSSource("Col009")

DTSDestination("fld03") = DTSSource("Col008")

DTSDestination("fld02") = DTSSource("Col007")

DTSDestination("fld01") = DTSSource("Col006")

DTSDestination("a1") = DTSSource("Col005")

DTSDestination("a2") = DTSSource("Col004")

DTSDestination("a3") = DTSSource("Col003")

DTSDestination("a4") = DTSSource("Col002")

DTSDestination("timeDate") = DTSSource("Col001")

DTSDestination("pId") = DTSGlobalVariables("pId").value

Main = DTSTransformstat_InsertQuery

else

If isNumeric(sc) then

DTSGlobalVariables("pId").value = sc

else

end If

Main = DTSTransformstat_SkipInsert

end If

End Function

2)My Patch Level is - Service Pak 4

3)When I try to set the option to 'source row errors' I get the following DTS Designer Error - "Invalid transformation detected. Verify and retry your transformations.

mdaniel SSCarpal Tunnel Points: 4616 More actions · Answer 11

- Which version of SQL do you have? Wich sql service pack does the server have?

- In sql 2k to set the source error rows, goto to the options tab in the properties of the data driven query. Uncheck 7.0 format and check 'Error text', 'Source Error Rows' and 'Dest error rows'. Put the file name you want for the exception file. Try to execute the DTS with this options and look for the log files it creates.

mebjen SSC Veteran Points: 219 More actions · Answer 12

mdaniel,

SQL 2000 - Standard Edition // (SP2)

I did as you explained in 'setting source row errors', but when I try to 'Apply', I get the DTS Designer Error - "Invalid transformation detected. Verify and retry your transformations."

mb

noeld SSC Guru Points: 96590 More actions · Answer 13

It does not appear to be a code Error in the ActiveX Script so you should try to apply SQL Server SP3a!!

It does not make sense that you get an error on the Error Logging options

* Noel

mebjen SSC Veteran Points: 219 More actions · Answer 14

noeid,

I have contact MS Support - they have indicated a 'Bug' -- we'll see what the work-around will be.

thanks for all your help yesterday

mb