March 7, 2012 at 2:56 am
Hi there,
Apologies if covered elsewhere - I have searched but couldn't find, I'm new to SSIS so I'm hoping I've missed something obvious....
I have a strange error. I have at least two tables from a source I'm trying to extract data from this is happening on. The source data is held in a SQL server.
When browsing the tables (record counts, 857003 & 327336) via Management Studio the main id column which is decimal(31,0) will after so many records in the viewer, return <Unable to read data>.
However, when I browse the same tables from Query Analyzer no problems reading the data.
But, if I run a query of subset of this data in management studio that when browsing was incorrect it now shows no issues.
Onto SSIS - When I try to extract the data using OLE DB or ADO Net Source I will get errors that the "Value is too large for the column". When I use a sql command within the OLD DB Source and subset on some of the records that I know I can't see in Management Studio and use preview results I get the error <Error displaying this cell>.
What's weird is it can read some of the rows but not all. Is there some setting or config I can change that will fix this. I've asked our DBAs and they say tables look fine and the fact I can see data in query anayzer suggests it's something to do with SSIS / Management Studio.
I'd really appreciate any help on this as feel I'm going mad.
March 7, 2012 at 8:31 am
Natalie,
Have you tried deleting your tasks and cm's and just trying something simple like a copy to csv?
I have had issues in the past where cm's for flat file destinations go squif cause of table schema changes (and you cant refresh, all you can do is drop and start again).
Of more concern is the query issue. If you cant get the data set back is ssms, you wont in ssis! I always test as much as i can in ssms before even opening bids.
Sounds to me like an access issue. If you can get one tables data but not another, that sounds like access. However if you just cant get a subset of that table, then thats weird and might either be some view/read trigger or maybe corruption. Try getting your DBA's to run your query and see what they get. Then ask them to interperate the results.
HTH
Adam Zacks-------------------------------------------Be Nice, Or Leave
March 7, 2012 at 9:19 am
Hi Adam,
Thanks for the reply.
Yep, tried those still not working, however I have got a workaround...
Basically on the select statement from the source database if I do a cast on the problem column and convert to nvarchar 31 SSIS can read the data fine, very odd and I know I haven't really got to the bottom of why but the DBAs are adamant data in the source database is fine.
I'm thinking it may be something to do with the source SQL server being 2000 and SSIS running off 2008 and how decimals are held in the database, but that's just me guessing.
Thanks for your help and hopefully my workaround might help anyone else that's encountered this 🙂
March 7, 2012 at 9:23 am
If the source db is 2k then very likely bids 2k8 might have issues. Did you try 2k5?
The other thing to consider is user data types (if thos existed in sk).
Either way glad its sorted.
Adam Zacks-------------------------------------------Be Nice, Or Leave
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply