February 22, 2008 at 10:28 am
When importing negative numbers from flat files in DTS the minus sign can be on either side of the number. When importing negative numbers using BCP with Format files if the minus sign is to right of the number it will error out with "Invalid character value for cast specification". Can anyone explain the reason for that?
February 24, 2008 at 2:05 pm
DTS is probably considering the column a string, while in your format file you have it specified to a numeric value. You may have to import the column as a string.
On a side note, Is it possible to make the import file consistent? You should not store data in different formats, if it can be avoided. It only makes things more difficult down the road. The best solution is to import a file where all the "-" signs are in the front.
February 25, 2008 at 6:31 am
Adam
Thanks for the reply.... The data being imported is in a uniform format being exported from an Oracle database. The method has been working perfectly using DTS for a long time. The reason for the change was that we needed a faster import mechanism since some of the files being imported tend to be large (i.e. in excess of 5Gig). This usually take DTS a long time to import and we decided to not only speed up the import but streamline the whole process as a built in part of the database building. We chose BCP as testing showed it to be much faster and more suitable to our streamlining of the process.
BTW: The DTS importing utilizes the 'TRANSLATE' data function and does not object to the minus sign of the incoming negative value being on the RIGHT side. The resulting 'translated' values are always the correct negative or positive values.
My opinion of the cause for this 'discrepancy' is that Microsoft just did not do a full and thorough test of its software (as is the case with so many other Microsoft products). Maybe some one needs to tell Microsoft about having their development teams talk to each other before they independently release their product for general use.
Thanks again for your response.
February 25, 2008 at 6:40 am
Have you considered using SSIS? SSIS has bulk operations and is more scalable. I tend to lean on SSIS packages for ETL more than BCP because SSIS packages can be directly integrated into SQL. Additionally, you can schedule SSIS packages, without reducing security. Running BCP as a scheduled job requires that you use xp_commandshell, which adds a degree of risk.
February 25, 2008 at 7:27 am
Adam
Once again, Thanks for the assist, (Especially the fast turn-around) it is most appreciated!
Yes, I have considered SSIS. But our system (package) is distributed to many different clients, running as many different systems, configured in a multitude of different ways. That is why we decided to use a method that is, hopefully, available and uniform in most SQL Server installations. We are anticipating using the 'Bulk Insert' functionality of Microsoft's SQL Server's implementation of SQL statement/functions. We found that BCP mimicks the behaviour of that statement/function fairly closely. We are keeping our fingers crossed that Microsoft did NOT embed some sort of difference that will only become apparent after lengthy use.
Thanks again. It is always good to have the input from a knowledgable and 'disinterested' third party!
February 25, 2008 at 8:41 am
Hans Munkwitz (2/25/2008)
...My opinion of the cause for this 'discrepancy' is that Microsoft just did not do a full and thorough test of its software (as is the case with so many other Microsoft products). Maybe some one needs to tell Microsoft about having their development teams talk to each other before they independently release their product for general use...
You are way off base there.
BCP is a legacy program that goes back to the pre-Microsoft days of Sybase, and has not been enhanced much in 7.0, 2000, or 2005.
DTS is a much newer technology that was originally released with SQL Server 7, so it is not surprising that it has more features than BCP.
If you need to use the more streamlined load technology in BCP or Bulk Insert, you should consider modifying the format of the input file to suit BCP. Also, the need to use the translate function may be the reason why you are having performance issues with DTS. You might find that without it, DTS will load at about the same speed as BCP.
February 25, 2008 at 8:57 am
My experience leads me to believe that it is almost never a good idea to perform ANY transformation of the incoming data during the initial load phase. Pretty much invariably - it's MUCH faster to import first, then clean things up, and yes - that involves your "misplaced" signs.
Like Michael pointed out - DTS has a lot of powerful if applied correctly. Most of the times, the packages are misbehaving because something is being done at the wrong time.
I'd be curious how much better it would do with a "bulk load" stage and a "convert" stage. Gut feeling - it would be MUCH faster.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 25, 2008 at 12:31 pm
Mike and Matt
Thanks for your input.....
The last sentence in Matt's reply really says it all!
I hope this is the end of this thread...... Thanks to all who participated.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply