Connection problem from SQL Server 2016 to SQL Server 2008 R2 using SSIS
-
I have a SQL Server 2016 SSIS project that works just fine against SQL Server 2008, 2012, 2014, and 2016 data sources. I've tested it by putting my source database on each of those servers and pointing my project level connection string at it. Each time it runs to completion without a problem. If I put my source database, the exact same database I've used on every other server, on a SQL Server 2008 R2 instance it fails with the error below. The error VS_ISBROKEN is nonsensical as it works when the same database is on any other SQL Server instance. The instance is installed on a Windows 2008 box. I'm starting to think this is a bug in the provider. Does anyone have any thoughts? This project needs to be able to access databases on any version of SQL Server from 2008 R2 on so this is a problem of the show stopper type. The project is running on a SQL Server 2016 instance of Integration Services with the target being a SQL Server 2016 database engine. The platform is Windows 2012 R2 Datacenter.
"Beliefs" get in the way of learning.
- Robert Frasca - Wednesday, February 8, 2017 7:47 AMI have a SQL Server 2016 SSIS project that works just fine against SQL Server 2008, 2012, 2014, and 2016 data sources. I've tested it by putting my source database on each of those servers and pointing my project level connection string at it. Each time it runs to completion without a problem. If I put my source database, the exact same database I've used on every other server, on a SQL Server 2008 R2 instance it fails with the error below. The error VS_ISBROKEN is nonsensical as it works when the same database is on any other SQL Server instance. The instance is installed on a Windows 2008 box. I'm starting to think this is a bug in the provider. Does anyone have any thoughts? This project needs to be able to access databases on any version of SQL Server from 2008 R2 on so this is a problem of the show stopper type. The project is running on a SQL Server 2016 instance of Integration Services with the target being a SQL Server 2016 database engine. The platform is Windows 2012 R2 Datacenter.
Are you certain it's not a permissions issue?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Have you looked in the SQL Server errorlog of the offending server? Have you tried going to SSMS and running the query directly that fails directly against the server? Are you sure it's the fact that it's SQL Server 2008 R2 that's causing the failure, not some other difference such as database collation, authentication mode or permissions? You should also be able to get more detailed error messages from SSMS, either by configuring logging appropriately, running the package interactively from SSDT, or setting up event handlers on the tasks that are failing.
John
-
It's not a permissions problem. I'm accessing the database using the sa account. (Not my preferred method but I lost this battle. In this case it helps rule out a permissions problem.)
It's not the query. It works on every other platform and it doesn't matter how I execute the packages, i.e. manually or using a job (SQL Server Agent). We only use the default collation. It doesn't matter whether it's the default instance or a named instance. Running it interactively isn't really an option as SSDT isn't installed on my SQL Server 2016 instance but I can't imagine it would work any differently as I'm still using the same provider to query the same server using the same credentials.
"Beliefs" get in the way of learning.
-
Well, we can't see any more than you can, so you're going to have to get that error message by hook or by crook. How about running a trace against the server for the duration of the package execution (but no longer than that)?
John
-
Can you 100% confirm that SSIS is the culprit, by setting up an ODBC connection using the exact same conn string & using it within Access or Excel to execute a query?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
We've figured it out. We have some embedded queries that aren't stored procedure calls because we don't have the privilege to store a proc on the source server. Some of these queries derived their own parameters.
DECLARE @StartDate datetime
SET @StartDate = (SELECT MAX(StartDate) FROM MyTable)
SELECT Column1, Column2
FROM MyOtherTable WHERE StartDate > @StartDateTurns out that SQL Server 2008 R2, doesn't like anything that doesn't start with EXEC or SELECT. This is not true in later versions of the product.
The fix is to eliminate the DECLARE and SET and pass StartDate as a parameter from SSIS so the only statement now is:
SELECT Column1, Column2
FROM MyOtherTable WHERE StartDate > ?I think this is probably a better coding methodology anyway so I'm okay with it.
"Beliefs" get in the way of learning.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply