July 23, 2008 at 4:30 pm
This gonna take some 'splainin', Lucy! 😛
I'm importing data from a vendor that is sort of in a mainframe format. There is one header record, then N detail records. The header record contains a record count (only of detail records, the header record is excluded from this count) and a batch total dollar amount.
The DTS job has an Execute SQL Task that truncates two temp tables. Upon successful completion, it runs two Text File Source tasks. The first imports the header record, it is followed by a Transform Data Task that limits the number of rows imported under Options/Data Movement to First Row = 1, Last Row = 1. The second Text File Source task gets the detail records and has a Skip Rows 1 under Properties to skip the header record. The Transform Data Task has no restrictions on First Row/Last Row, they're both zero.
The system works fine, it parses the data appropriately into whatever column is appropriate. The problem is that the detail records lop off the first record! The header record is parsed and loaded into the TempHeader table, but the first detail record disappears and the second detail record is the first record in the TempDetail table.
My DTS Fu is not the strongest, and this is a fairly complicated process. Everything was going well until I noticed this missing record. Any suggestions on where to check?
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
July 24, 2008 at 8:17 am
Just for giggles, have you tried setting the Skip Rows to 0 for your detail rows just to see what happens? I mean, since it is lopping off the first detail row, that would be an easy place to look, although that is not the behavior you would expect.
Just a guess...
If it was easy, everybody would be doing it!;)
July 24, 2008 at 8:32 am
Unfortunately I can't do that because the detail record layout is different than the header record, and with the mapping changing the import fails.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
July 24, 2008 at 8:52 am
One other approach would be working with the First Row setting on your Detail Transform step just to see what happens.
If it was easy, everybody would be doing it!;)
July 24, 2008 at 9:17 am
I was able to mock up your design with your settings and get it to work without losing any data.
Other considerations...
Is there something special about the data in the 1st detail row that would cause a problem? Are you doing a transformation of the data that would cause a problem with row 1 inserting into the temp table?
To test these ideas, cut the 1st row of detail data out of your file and then run the import. If it still lops off the 1st detail row, then you know it isn't a data related issue since you know that in the previous attempt it imported row 2 (which will now be row 1).
Also, you don't have the 'first row has column names' box checked for your detail file source by chance? Sorry I have to ask that, but sometimes it is the easy stuff that gets us.
I hope some of this is a help to you.
If it was easy, everybody would be doing it!;)
July 24, 2008 at 9:58 am
Unfortunately I can't lop off rows because the mapping then fails since the header record and detail records have different layouts.
I did just learn something interesting, though. I created a file with two rows: deleted all of the rows from the file after the first, and adjusted the row count and batch total in the header. The detail record did not come across, but I initially got an error that said there was invalid data after the end of line in column 332, but the record is only 250 bytes. I added a field marker at column 250, the error went away, but the one detail record did not import.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
July 24, 2008 at 10:57 am
When I said take off the first row, I meant the first DETAIL row...keep the header, and just cut out the first detail row to see if what is currently the 2nd DETAIL row would import if it was now in the position of the 1st row.
Just curious, was the detail record that wouldn't import the same one that wouldn't import before, or was it different data?
I would take a good look at that data file and make sure there is nothing wrong with it, or perhaps a rogue character somewhere on that line.
Good luck!
If it was easy, everybody would be doing it!;)
July 24, 2008 at 4:53 pm
I think it's a buffering problem. At the suggestion of our ERP manager, I tried it as a sequential flow: SQL truncate table, import file, DTS parse header, import file, parse detail, continue... I still lost the record. If I split the file into two files: header/detail, it works fine.
So I may end up with a little Perl script run by the FTP task to split the file into two parts as soon as it's downloaded.
I'm going to have to search the Microsoft site to see if I can find anything there. I should also try rewriting this under 2005 and see if I get the same behavior, not that the server in question will be upgraded to 2005 in the near future.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
July 24, 2008 at 5:04 pm
Found the reason for the problem, not sure how I'm going to solve it. Apparently it's a buffering problem: the header record is 80 characters, the detail records are 250 characters. If I pad out the header record with spaces to column 250, everything is good.
So, I think I have three choices. (A): Revise the file spec and get three vendors to change their processes, or (B): write a Perl script to either pad the header out to 250 or (C): split the incoming file into a header file and a detail file, which also works. I lean towards B.
I'm definitely going to have to try this under 2005.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
July 25, 2008 at 5:23 am
Very interesting...that was good work finding the cause. I tend to agree with you. Plan B or C would definitely be a better solution than going to 3 different vendors with a change (been there, done that). Much easier (and faster) to keep the solution in-house.
If it was easy, everybody would be doing it!;)
July 25, 2008 at 8:39 am
I was also working the problem with the Phoenix SQL Server group on their email list, and someone mentioned that they'd never been able to get files with mixed record lengths to work right. I tested by padding out the final filler in the header, and it worked perfectly. To me, it seems like an implementation error as no transfer files that I've worked with had header records that were the same length as the detail records.
I really have to test this under 2005. Very important thing to remember.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
July 28, 2008 at 9:31 am
I thought of another solution over the weekend and tested it this morning and it works nicely. The last field in the detail record is a 41 character blank filler. I kicked it up to 211 bytes (250 - 80 + 41) and now DTS believes all of the records are of uniform length, even though they aren't.
This way, I don't have a Perl script manipulating the file, which someone after me might not be able to cope with. I also don't have to force a change on my vendors, which is good for them, and keeps the record spec closely resembling the output file which is produced by this system for import into our ERP system.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
September 11, 2008 at 11:10 am
Well, turns out I was mistaken. My solution, padding out the final filler field so that the header record length matched the detail record length, doesn't work. One way or another, the record length must be uniform. So we updated the specs and sent it out to the vendors and told them they have to tweak their layout.
*sigh* I REALLY wish my method had worked!
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply