November 16, 2016 at 12:37 pm
An SSIS package that I developed calls a stored procedure that takes DATE parameters using an OleDb command and passes SSIS variables of type DateTime. When originally developed and tested, this package worked.
The package now exists in three test environments. It's working in two of them, but failing in the third. I know from Google that this is a bug in SSIS because the parameters are DATE type.
So I'm trying to figure out some things:
- This used to work in all environments, why did it stop working? The package and procedure have not changed. AFAIK, the server has not changed either.
- Can I rely on it to continue functioning in the other environments? How is it working there with the exact same code? The package is the identical file in all places.
- Do I have to alter the procedure?
I would just alter the procedure, but it's a very sticky administrative procedure. The "failing environment" is the developers' sandbox. I can't create a defect from that environment. Since it works elsewhere, there must be a problem in the sandbox, and no one cares about the sandbox except developers.
The package was created in Visual Studio 2013 using SQL Server Data Tools 12.0.06525.0.
In the two functional environments, the package is being run by a job on a SQL 2014 server (12.0.5522.0) and calling the problematic procedure on a different SQL 2014 server (12.0.4100.1).
In the failing sandbox environment (which used to work but stopped working over a month ago and I have no logs from before then), the package is being run by a job on a SQL 2014 server (12.0.2000.8) and calling the problematic procedure on the same server.
In Visual Studio, I can't get the package (again, the same one that was working) to build against the sandbox server or my own local server (SQL 2014 12.0.4100.1). Both of these servers used to work for this procedure call. I don't have access to debug the package against the functional environments.
I've found a lot of information online about this bug, but I haven't seen that it's intermittent.
Guess what? I solved the problem while making sure this post was complete and accurate. I'm going to post this anyway so that someone else doesn't tear their hair out with frustration.
The sandbox environment (and my local) are using the following connection string, which may have (and probably) changed recently because the connection strings are stored outside the package:
Data Source=someserver;Initial Catalog=thedatabase;Provider=SQLNCLI10.1;Integrated Security=SSPI;Auto Translate=False;
The functional environments use this:
Data Source=anotherserver;Initial Catalog=thedatabase;Provider=SQLNCLI11;Integrated Security=SSPI;Auto Translate=False;
I changed provider to SQLNCLI11 and now it works.
November 16, 2016 at 2:04 pm
Well done on sorting that out.
Presumably the package was originally developed for a 2012 back-end (using Native Client 10.1) which was migrated to 2014 (NCLI 11)?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
November 16, 2016 at 2:11 pm
Phil Parkin (11/16/2016)
Well done on sorting that out.Presumably the package was originally developed for a 2012 back-end (using Native Client 10.1) which was migrated to 2014 (NCLI 11)?
That would make too much sense. No, it was always 2014. The problem environment is a sandbox. Things change, and no one knows who did it (or will own up to it).
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply