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 |
|
chilluk
Starting Member
24 Posts |
 Posted - 2007-06-12 : 09:33:32
|
| Following on from some problems I have been having with flat files, I seem to be stumbling from one issue to another.I have coded my text file with some very contrived delimiters to ensure that my real data isn't tripping up the package. I am stripping all line break chars, tabs etc. Now I seem to get all rows imported, but numerous integer columns within the file are being set to zero.When I toggle the Keep Null options in the data flow task it will either import the row, but with the zeros, or not import the row at all. I have opened the file in text editors, and Excel and it all seems fine - in fact the same source file works OK with DTS/2000.At a guess it seems as though these columns are seen as null by the package and so with the option switched on it defaults to zero - but they are most certainly not null in the file!! The table is a staging tabe and so is very basic in structure (no defaults or constraints)SSIS seems a bit buggy or at the very least over sensitive to me - I am at the point of abandonment of it!!Please has anyone seen similar issues? |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-06-12 : 10:04:19
|
| Put a watch on the datastream when you import to see what is happening.My guess is that you have a datatype issue but could be lots of things.You do have the latest service pack?Persevere - you'll find it a much better product than dts.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
chilluk
Starting Member
24 Posts |
Posted - 2007-06-12 : 10:21:48
|
| Thanks for that.Where do I put a watch on it - through SSIS or just a normal SQL trace?It is certainly more feature rich but seems to behave a little unexpectedly!!EDIT : I configured a data viewer on the file and it's reading in zeros where there are none.Chris. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-06-12 : 10:38:04
|
| In the dataflow right click on the connector and it will give an option to add a data view (do it as a grid). when run this will be populated with the data being passed and stop after a few rows until you let it continue.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
chilluk
Starting Member
24 Posts |
Posted - 2007-06-12 : 10:47:44
|
| OK - what is weird in the preview within the flat file setup it shows values for rows which in the import / data watch it shows zeros for.The package runs through fine and finishes no errors. When I select Keep null values it imports less data, so it's as if it is perhaps seeing these values as null? However the table it's going into has no validation on it at all so nulls should be fine anyway.Not every row is set to zero - 90% of one column is , whereas 98% of another might be.The values are low integers in the main, going into an int type field in the table. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-06-12 : 10:57:19
|
| Have a look at the metadata to see what the datatype of the output from the source is.Also have a look at the data with a hex editor - sounds like they are not ascii zeroes or it's trying to convert it to something incorrectly.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
chilluk
Starting Member
24 Posts |
Posted - 2007-06-12 : 11:56:52
|
| Everything checked out as it should there.As a process of elimination I decided to take out the long description text and replace with the same short string for all records. That worked fine and all data imported OK.Something in that field is tripping this up. I am replacing char(10) and char(13) values with nothing (i.e. getting rid of them) as a test ut so far this has only lessened the number of errors rather than got rid of them all.What is also weired is that the affected integer fields aren't either side of this column yet are still affected. |
|
|
|
|
|
|
Losing Integer Values