December 13, 2005 at 2:14 pm
Hi all,
In sotre procedure/DTS package, is there any way to display row number in a delimited text file for import.
I have DTS package set up to run on a daily base, this DTS will call a store procedure to flush data in a table and then import the data from this text file to the table.
But the problem is when there is any row with corrupted data, I have to search through in the excel data sheet(open this text file in excel sheet) to locate this record among those around 10,000 records.
any better ways?
Thank you.
Betty
December 13, 2005 at 3:23 pm
Can you be a little more specific - is the import actually failing, or are you validating the data after import and finding that it is 'corrupt' because it fails certains validation or business rules?
If the second case, it is fairly straightforward to set up a global integer variable as a row counter and add it as an extra field as part of the import. A small amoungt of ActiveX scripting will initialise and increment the counter for you.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
December 13, 2005 at 3:41 pm
Hi Phil,
Actually the import is failed. I don't do any validating after import. As long as it could be imported and I assumed the data is all right.
What I am trying to ask is when the import is failed, I want to get a very specific error message from the log file or email whichever is easy, tell me what's wrong with the import file. So it will be easier for me to fix the text file without search through the big text file what is the problem.
Thank you.
Betty
December 14, 2005 at 3:49 am
If you are using DTSrun to execute the package (eg in a scheduled job), it will output any errors which includes the row(s) and column(s). If it is a scheduled job, change the Step properties to capture the output. This is how I trace data errors in my imports.
Far away is close at hand in the images of elsewhere.
Anon.
December 15, 2005 at 2:06 pm
Hi David,
Thanks for your instructions. I used sql server agent to schedule the job.
Do you mean that in Step tab, we can click edit button of the Step, and there is an advance tab there, under the operating systme command CmdExec command option, you can name the location of the export file, this file will give the detailed message like row(s) and column(s) of the problem records?
Betty
December 16, 2005 at 2:28 am
Yep, if you put a valid path to a filename in the 'Output File' field then all the output of the DTSrun will be in that file including any errors. Try it with data with a deliberate error and you should see the details of the error in the file.
Far away is close at hand in the images of elsewhere.
Anon.
December 16, 2005 at 11:44 am
Hi David:
The error message is look like this: It seems that the row number is missing.
Any idea?
Error Detail Records:
Error: -2147217913 (80040E07); Provider Error: 245 (F5)
Error string: Syntax error converting the varchar value 'Last error encountered: rows is ' to a column of data type int.
Error source: Microsoft OLE DB Provider for SQL Server
Help file:
Help context: 0
Error: 0 (0); Provider Error: 0 (0)
Error string: Errors encountered, rolling back.
Error source: Microsoft OLE DB Provider for SQL Server
Help file:
Help context: 0
DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1
DTSRun: Package execution complete.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply