Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ciaran
Starting Member
40 Posts |
 Posted - 2005-09-06 : 07:51:30
|
| Hi, was wondering how people facilitate detecting erros when pumping data from textfile into database. I am using a text file source component and a sql connection object.I use the transform data task to transfer the data to sql. However my boss asked me to sset up a dts which loads a 142 column file and i am worried that i have no way of highlighting on what line the the errors occurred on. I have asked the client to break the file into smaller sub files but this requires a lot of development time on their part and they are unwilling to agree. |
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2005-09-06 : 10:12:48
|
| DTS has nothing but bad error handling (IMHO). To get around this, I import text as VARCHAR and validate data using SQL once it's in a table.Depending on your comfort level: - Import each line as a single VARCHAR record, parse individual columns using SQL into another table, then parse the individual column results into their target tables. This is the safest option. Looking back, I wish I had used this option. - Instead of DTSing each line into a VARCHAR column, DTS each column directly into a VARCHAR column. Very safe if the source text has no missing columns. The danger is if a single row is missing a column, DTS will pull from the next line which in my case, is unacceptable. This is the method I use today. |
 |
|
|
ciaran
Starting Member
40 Posts |
Posted - 2005-09-06 : 10:53:06
|
| Sam, thanks for the reply. I am a step ahead in that i do pump the data into intermediary tables and then using sql i import the data from there. However i am worried about getting the data into these intermediary tables. this is where i see the main issue |
|
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2005-09-06 : 11:07:05
|
| So, your problem is identifying the line in the import file that contains the problem?You'll need to assign a line/row number to each line/row imported (for error reporting) I guess.Am I not understanding the problem? |
|
|
|
ciaran
Starting Member
40 Posts |
Posted - 2005-09-06 : 12:05:29
|
| Sam, you see the problem fine. Ya i think this is the only way to go so i have set an identity column on the import table and hopefully this will give me the areas/line number that the file is in error. I was wondering if any one new any other tricks/tactics for such an issue |
|
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2005-09-06 : 13:21:18
|
| Not that I know of. I'd choose to do an IDENTITY column myself.Sam |
|
|
|
ciaran
Starting Member
40 Posts |
|
|
|
|
|
|
|
detecting errors in textfile to Database transfer