Hello Everyone I am executing a SSIS package which does a select * on a view. Now this select * from view gives me casting error in management studio. However, when I try to run this package in visual studio it behaves differently with different sql providers. When I specify SQLOLEDB provider in connection string it returns numbers of rows prior to erroneous record and ends successfully. When I use SQLNCLI.1 provider in my connection string, the package fails with casting error as expected. Below are the connection strings I am using to connect to sql server and database; - - package behaves normally and returns error with this connection string as expected Data Source=ServerName;Initial Catalog=DBName;Provider=SQLNCLI.1;Integrated Security=SSPI; - - package behaves abnormally and does not return error with this connection string Data Source=ServerName;Initial Catalog=DBName;Provider=SQLOLEDB;Integrated Security=SSPI; Can anybody please shed some light on this scenario? Thanks.ss

ONE REASON CAN BE Arithabort error , use the following query as source in all u r enviroments ,the output will show u the actual difference DECLARE @options INT set @options = @@OPTIONS select CASE WHEN ( (1 & @options) = 1) then 'DISABLE_DEF_CNST_CHK' end as col1 union all select CASE WHEN ( (2 & @options) = 2) then 'IMPLICIT_TRANSACTIONS' end as col1 union all select CASE WHEN ( (4 & @options) = 4) then 'CURSOR_CLOSE_ON_COMMIT' end as col1 union all select CASE WHEN ( (8 & @options) = 8) then 'ANSI_WARNINGS' end as col1 union all select CASE WHEN ( (16 & @options) = 16) then 'ANSI_PADDING' end as col1 union all select CASE WHEN ( (32 & @options) = 32) then 'ANSI_NULLS' end as col1 union all select CASE WHEN ( (64 & @options) = 64) then 'ARITHABORT' end as col1 union all select CASE WHEN ( (128 & @options) = 128) then 'ARITHIGNORE' end as col1 union all select CASE WHEN ( (256 & @options) = 256) then 'QUOTED_IDENTIER' end as col1 union all select CASE WHEN ( (512 & @options) = 512) then 'NOCOUNT' end as col1 union all select CASE WHEN ( (1024 & @options) = 1024) then 'ANSI_NULL_DFLT_ON' end as col1 union all select CASE WHEN ( (2048 & @options) = 2048) then 'ANSI_NULL_DFLT_OFF' end as col1 union all select CASE WHEN ( (4096 & @options) = 4096) then 'CONCAT_NULL_YIELDS_NULL' end as col1 union all select CASE WHEN ( (8192 & @options) = 8192) then 'NUMERIC_ROUNDABORT' end as col1 union all select CASE WHEN ( (16384 & @options) = 16384) then 'XACT_ABORT' end as col1

Varun btw, there is no difference in the output of above query for my development and UAT ssis environments where the package is deployed. Thanks, -Salman.ss

I recently had problems with SSIS and the different providers too. Of my tests & info gathered, this is my conclusion: Tthe SQLOLEDB provider is the "old/generic" provider. To make use of new features in SQL 2005/2008 such a new datatypes (e.g. xml in 2005, and date etc in 2008), you can use the SQL native providers ( SQLNCLI.1 for 2005, SQLNCLI10 for 2008). To, differences in outcome depend on the environment (client/server versions) and required functionality. One reference I bookmarked: http://msdn.microsoft.com/en-us/library/ms130978.aspx Hope this helps! Joachim.

THanks Joachim. I did find your post and have been reading it. It looks like you had the opposite problem to me (OLEDB worked for you, but native client did not). The poblem now seems to have resolved itself after a server reboot, but it's a little worrying that it may happen again.