April 14, 2015 at 9:50 am
SQL Server 2008R2 SP2 (10.50.4000)
Windows Server 32 bit OS
BIDS (Project Properties Run 64 bit det to false)
When I open my packages all of the SQL Server connections (2) work ok. However one or more connection (non SQL, ADO.Net Source linked to .Net Providers \ODBC Data Provider) create an error when I open the package. Example below.
Error3
Validation error. tblImp: tblImp: System.Data.Odbc.OdbcException: ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode)
at System.Data.Odbc.OdbcConnectionHandle..ctor(OdbcConnection connection, OdbcConnectionString constr, OdbcEnvironmentHandle environmentHandle)
at System.Data.Odbc.OdbcConnectionOpen..ctor(OdbcConnection outerConnection, OdbcConnectionString connectionOptions)
at System.Data.Odbc.OdbcConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.Odbc.OdbcConnection.Open()
at Microsoft.SqlServer.Dts.Runtime.ManagedHelper.GetManagedConnection(String assemblyQualifiedName, String connStr, Object transaction)
at Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManager100.AcquireConnection(Object pTransaction)
at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.AcquireConnections(Object transaction)
at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostAcquireConnections(IDTSManagedComponentWrapper100 wrapper, Object transaction)Package 2.dtsx00
The packages run fine through SQL Agent (except for one). However when I open all of the packages everyone of them has the error when opened in BIDS. All of the ADO.Net sources are complaining. As I dont think there is any 64 bit on the machine I dont see where the problem could be.
One or more components failed validation
Many Thanks
E
April 14, 2015 at 9:59 am
It looks like those connections use a DSN, and you probably don't have the DSN on your local machine (assuming you are opening the packages locally).
If you're opening the package on the server, it could be because of the fact that there are only 64-bit versions of the DSN. BIDS designer is 32-bit, and to circumvent the issue you can create duplicate 32-bit DSN's.
April 14, 2015 at 10:35 am
That's kind of where I was thinking the issue could be. However as the is is 32 bits and I am opening them on the server which is 32 bits my only last guess is that I am logged on as a different user to the user that runs the reports. So the data source works for sql agent but not for the account I am logged in as.
Hope that makes some sense.
Cheers
E
April 14, 2015 at 10:45 am
Ells (4/14/2015)
That's kind of where I was thinking the issue could be. However as the is is 32 bits and I am opening them on the server which is 32 bits my only last guess is that I am logged on as a different user to the user that runs the reports. So the data source works for sql agent but not for the account I am logged in as.Hope that makes some sense.
Cheers
E
Could be, depending on the authentication method used by the data source connections. It could also be the case that the DSN was created as a user-DSN, meaning that it will only be accessible to the user's profile who created it.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply