May 28, 2008 at 4:45 pm
Is there a SQL script that can turn off (and on) implicit conversion? I am passing a varchar value into a varchar column. When I try to pass in 01, it is converted into a 1. I am using SSIS and the insert is fed from a SCD transformation. I can see the correct value in the data viewer right before it inserts so I suspect it happens in the SQL engine itself.
Thank you,
Robin
May 29, 2008 at 5:27 am
I do this a lot and have never seen the behavior you are describing. Check the data just before the OLEDB Destination component (after the SCD component) using a data viewer. If you use the advanced editor you can check the inputs and outputs of your components to make sure they are all character data types.
The OLE DB destination component with a string in the data flow inserting into a VARCHAR field does not strip off leading zeros - you have something else going on. What service pack are you on?
May 29, 2008 at 6:45 am
Thank you for responding Michael. I am on SP2 rel 9.00.3042. The data veiwer is on the constraint from SCD Transform object to Insert Destination. The source column is output as string with a length of 10. It comes from an Oracle table. It original datatype is varchar2(10). The insert target is SQL table with a varchar(10) data type. The other bit of odd behaviour that I did not mention in my first post is that the first time the package is run it inserts this value as an implicit number, stripping leading 0. However, because this particular column is part of the unique BK, it does a second insert when the package is rerun, inserting the 01 the second time around. Completely bizarre!
Thanks for any ideas you may have regarding this issue.
Robin
May 29, 2008 at 7:09 am
Have you looked at the properties of the source and destination objects within SSIS? The source object in particular. I've seen it make some bad choices about what data types the incoming data really is. It could be that SSIS is doing that to itself.
If that's the case - it may be as simple as just adjusting what the source thinks it is to expect as a data source.
----------------------------------------------------------------------------------
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?
May 30, 2008 at 9:22 am
I saw similar things happen with Excel imports in SQL Server 2000, where if the data in the first several rows LOOKED like a particular data type, a row far enough down the in the file that was obviously different would cause an error. As an example, imagine a column in which there are 6 digit text numbers for the first 100 rows, but they're all actually representing hex numbers, and just don't happen to have any A, B, C, D, E, or F, characters. Then along comes row 101 with one of them, and kaboom - error time. I haven't had opportunity to test this kind of thing in SQL 2005 as yet, but I mention it just in case. To solve my particular problem, I was dealing with a field length problem, and I had to be sure I specified a longer than 255 length AND be sure a record longer than 255 was among the 1st few rows of import data. Someone once told me that the import process examines the first x number of rows for typing purposes (I don't know the value for x). Hope that helps.
Steve
(aka smunson)
:):):)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply