Microsoft DTS and db4o
Written on Monday, November 26, 2007 by Edwin Sanchez
Microsoft Data Transformation Services (DTS) is a good tool bundled in SQL Server. One of the things you can do with it is to upload data from different file formats like text file and excel file into SQL Server. In this post, I will try to log my experience in comparing it to the program I created in db4o to transfer data from text/excel file to db4o.
I tried to upload 2 files in SQL Server: one is a text file and the other is an excel file. I uploaded it as is based on the columns in the files. The 2 files eventually became tables in SQL Server. I performed a query left joining the 2 tables to find out if an item does not exist in the other. The result was 1,033 records.
My db4o program's objective is to have the same results as in the SQL version. But to my surprise, the results are not the same. First, it gave me more than double the number of records I got from SQL Server. I found out that there are some information in the file that has spaces after them. For example: I'm expecting "FOO" but the data is "FOO " (with one space character after). So I tried to trim them. After that, the result is still not the same in terms of numbers. There are 3 records more in the result from the db4o program. After some scrutinization, I have found out that I forgot an important element: the data is not consistent if it's lower or uppercase strings. (See related post here) I tried converting them all to uppercase and finally, the results are the same.
Here are the lessons learned:
1. DTS removes trailing spaces in your data. Your db4o program should do the same to obtain the same result.
So that means, it "looks" like you uploaded it in SQL Server "as is" but actually, all data are trimmed to remove the spaces. I proved that by inspecting the data in SQL Server with the len() function. It's not the same as the source data in text and excel files.
2. Remember that db4o/C# is case-sensitive.
Maybe this has not fully internalized in me and I forgot it again.
Hope this helps if you have this kind of stuff in your work.
------------------------------------------------------------------------------------------------------
Related Posts:
If you enjoyed this post Subscribe to our feed