August 31, 2011 at 4:27 pm
I have a package I created in VS 2005 in a SQL 2005 solution. Because someone saved changes to the package using an SP or something that I didn't have, I had to finish working on it in VS 2008, so the package got upgraded, but the solution has not yet been upgraded.
The package works fine in BIDS, but when I put it up on SQL 2008, it errored when my end user tried to run the job that calls it. The error is:
Error: 2011-08-31 17:17:07.82
Code: 0xC0209303
Source: ActuarialReportingToolLoad Connection manager "MyDB"
Description: SSIS Error Code DTS_E_OLEDB_NOPROVIDER_64BIT_ERROR. The requested OLE DB provider SQLNCLI.1 is not registered -- perhaps no 64-bit provider is available. Error code: 0x00000000.
An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040154 Description: "Class not registered".
End Error
This is related to the OLEDB connection manager. I created a new one, using the proper provider, deleted the old one, and replaced all instances where the old one was called. The package works in BIDS and I can run the package in the GUI from the Integration Services menu where it wasn't working before (that was my first troubleshoot).
The job is still failing on the same error. I'm running the job as an operating system command calling dtexec.exe. I've tried replacing the command line with a new one (renaming the package from MyPackage.dtsx to My Package, which is what the command line in the "Run Package" gui lists it as), but it's still not working.
Google gives me this link: http://msdn.microsoft.com/en-us/library/ms141766.aspx. Hello. I'm running SQL 2008 64 bit on Windows 2008 64 bit. Directly calling the 32 bit executable does not fix the issue. Also, I cannot the Execute options tab as talked about here when creating a new job or a new job step:
Use 32 bit runtime on the Execution options tab of the New Job Step dialog box.
A search in my project for Provider=sqlncli.1 doesn't reveal anything I missed in this particular package.
Does anyone have any thoughts?
August 31, 2011 at 5:20 pm
Try setting the RUN 64 BIT RUN TIME to FALSE on project properties in BIDS
August 31, 2011 at 5:30 pm
prvmine (8/31/2011)
Try setting the RUN 64 BIT RUN TIME to FALSE on project properties in BIDS
that's right!
September 6, 2011 at 5:42 am
prvmine (8/31/2011)
Try setting the RUN 64 BIT RUN TIME to FALSE on project properties in BIDS
One of the articles I found mentioned that, but I can't find that property.
Could you give me more specific details on where I can locate it?
September 6, 2011 at 7:09 am
follow-> in BIDS -> (Menu Bar) Project -> SSIS Properties... -> in SSIS Property Pages -> Debugging -> Debug Options -> Run64BitRuntime
September 6, 2011 at 8:00 am
rfr.ferrari (9/6/2011)
follow-> in BIDS -> (Menu Bar) Project -> SSIS Properties... -> in SSIS Property Pages -> Debugging -> Debug Options -> Run64BitRuntime
I don't have that path in my VS. I have Project -> Properties, but not SSIS Property Pages. And in the Project Properties, there is no Run64BitRuntime property.
I'm running BIDS off my local box, which is 32 bit. All my client tools are on my local box. We don't develop on the servers.
September 6, 2011 at 8:35 am
see the files attached
September 7, 2011 at 6:35 am
Hi. Let's try this again.
I don't HAVE SSIS Properties. See attached.
September 7, 2011 at 7:51 pm
If you highlight the project in BIDS Solution Explorer
then Right click on the project
in the drop down list select Properties - which opens project property pages
Run64BitRuntime is under the Debugging Configuration Properties
Hope this helps. . .
September 8, 2011 at 6:02 am
Okay, I did find that setting, prvmine. Changed it. Saved the project, uploaded the package and the job failed again with the same error.
So I checked out the package, saved the project again (after verifying that setting was on every single Debug Configuration possible), checked in the project, uploaded the package and the job failed again with the same error.
The project itself is still saved as a 2005 project while the package was upgraded to 2008. Could this be the problem?
September 8, 2011 at 7:03 am
the SSIS is in another box/server?
if yes then try create a DSN connection in SSIS server to connect in Data Source, and change OLE DB connection in your package to DSN!!
but, to use in your SSIS server the 'ODBC Data Source Administrator' of folder -> ..\windows\syswow64\odbcad32.exe, because in folder ..\windows\system32\odbcad32.exe contain only connections 64bit, the 32bit connections is in syswow64!!!
September 8, 2011 at 8:05 am
You just lost me.
The SSIS package is being developed on my local box, then loaded up to the Server that it is run on. My local box is 32 bit. The server is 64 bit. Other than that, I have no idea what any of the program information you posted means. I run the package via a job with the DTEXEC.exe command line call.
FYI: The SSIS package reverted back to the original SQLNCLI.1 client after I checked it in. I've replaced the OLE DB connection manager 4 times and it keeps reverting back.
Prior to check-in, connection string is:
Data Source=MyServer;Initial Catalog=MyDB;Provider=SQLNCLI10.1;Integrated Security=SSPI;Application Name=SSIS-MyPackagename-{9908CFD0-5AD4-46EC-9EE0-50EECDF1AB58}MyServer.MyDB;Auto Translate=False;
Post check-in, connection string is:
Data Source=MyServer;Initial Catalog=CreditMyDBExtra;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;
What the heck is going on here?
EDIT: And the runtime in the project properties deployment area changed back to TRUE sometime during my changing of the connection manager.
September 8, 2011 at 8:14 am
I've narrowed it down to a name change issue.
If I leave the connection manager named ServerName.DatabaseName, it stays as the 10 client. But as soon as I remove the servername (which we don't want on connection managers for various reasons), it switches the client back to the original 1.0.
ARGH.
EDIT: I am officially asking for help to see if this is a bug or not.
The project / solution and package have all been upgraded to Visual Studio / SQL Server 2008. It doesn't matter if the Run64BitRuntime property in the project properties -> Debugging page is set to True or False.
I can create an OLE DB connection manager that uses 10.0, check it into TFS, and everything is fine. The connection manager uses the Server.Database naming convention. I close the package, open it up again, and it's still using 10.0 in the connection string. I check the package out for edit, change the connection manager to not use Server. in the name, check it back in, and the connection string still is using 10.0. Until I close and re-open the package. As soon as I do that, the connection string is using 1.0.
I'd appreciate everyone checking their own systems to see if it's just me or if anyone else is having this issue too. Thanks.
September 8, 2011 at 8:49 am
Brandie, take it easy!!!!
i found this link http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/920a0817-d9b8-4766-b0bf-baa6430acf93/, in the forum of Microsoft! with same problem and has solved!
try!!!
my previous previous post, is refers the other data sources, i.e. TXT files, EXCEL files, DBase Files..., the DSN in 32bit odbc works!
September 8, 2011 at 9:21 am
You don't have a package configuration somewhere that is overwriting the design-time value do you? It sounds like that could be it since having a different name makes it keep the design-time setting.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply