Desktop Engineer: What object are you using to create and write to the Excel file that you have on the server from your SSIS package?
Developer 1: We aren't using any object. Office was on the old server right?
Server Engineer: Office is not installed on the SQL Server.
Developer 2: You need to figure out what object we are using then.
Desktop Engineer: Will you allow us to see how you are building the object that is reading / writing Excel?
Developer 1: Of course, but let me tell a long rambling story here ... [For Brevity, I have removed the story]
Desktop Engineer: I see you have a connection object referencing Jet 4.0 and linking to the Excel file. Have we verified that the rights are setup properly for the account that is running this from our batch?
Developer 1: ... [More Rambling Stories] ...
Desktop Engineer: Let me ask again, have we verified the rights of the account?
Developer 2: No.
Desktop Engineer: Can someone verify these rights?
Server Engineer: I will check the rights if someone can tell me the account being used.
Developer 1: ... [Still Rambling] ... I will check on the account.
*** Then developer 1 continues to stand there, looking, desktop engineer shoes him off asking for the account info again ***
The long and short of it comes down to this ...
I spent half of my morning hunting down what objects we are using to perform Excel reads and writes from our overnight batch processing because our development server did not appear to have the right objects available. When we finally started to find some information, we determined that SSIS overwrote some registrations of the MDAC 32 bit on a 64 bit SQL 2005 Server.
The learnings here ... sometimes it's worth verifying that the object you are trying to reference is available in the OS in the places where it needs to be. In this case it was the OLEDB.JET.4.0 object that had become improperly registered. This seems to be common with SSIS and MDAC on 64bit Windows 2003 Servers.
No comments:
Post a Comment