Loading an 2003 excel sheet using SSIS and running from agent job

• John Reddy

  Ten Centuries

  Points: 1250

  More actions

  August 5, 2011 at 3:24 pm

  #245774

  Hi,

  I have a very critical issue that we are facing on our 64-bit server.

  Environment Details:

  Operating System : Windows Server 2008 R2 64-bit Enterprise Edition

  Database System : Microsoft SQL Server 2008 R2 64-bit Enterprise Edition

  Issue Details:

  I am attempting to load a .xls 2003 Excel sheet into one our tables using SSIS.

  Initially, when i attempted to load the file, an error popped up at the OLE DB Destination about the conversion of non-unicode and unicode data from the excel sheet into the database table.

  I read a blog that suggested to use data conversion task to handle the error. I used the task, converted the input columns (D_WSTR) into (D_STR) and used proper data lengths.

  The SSIS package executes with no issues.

  When i attempt to run the package from an agent job step, the package does not run. For some reason, the command line is completely screwed up.

  I installed the ACE 12.0 64-bit driver since the JET driver is not supported on 64-bit machines. I have the SSIS project option "Run64bitruntime" set to false.

  There were a few issue regarding the extended properties with additional "" so i removed them too.

  My main concern is why is it still using the JET driver in the command line in the agent job since i read that the ACE driver is backward compatible.

  Following is the command:

  /FILE "H:\SQL _2008_SSIS\Crystal Clear _ SSIS Packages\Test_Project\Test_Project\Salomon_CUSIP_Xref.dtsx" /CONNECTION "Excel Connection Manager";"\"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\vendor_apps\datafeed\Salomon\Sal-XRef.xls;Extended Properties=EXCEL;\"" /CONNECTION "jabdw3265.cc";"\"Data Source=jabdw3265;Initial Catalog=cc;Provider=SQLNCLI10.1;Integrated Security=SSPI;Application Name=SSIS-Salomon_CUSIP_Xref-{A31BABBC-2653-4BF3-A67F-416B594E107D}jabdw3265.cc;Auto Translate=False;\"" /X86 /CHECKPOINTING OFF /REPORTING E

  Please help !!!

• Chuck Hottle

  SSCarpal Tunnel

  Points: 4777

  More actions

  August 5, 2011 at 4:20 pm

  #1364688

  This is an extensive discussion of the issues. You have to have the 32 bit version installed on the server.

  http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/289e29ad-26dc-4f90-bad4-ffb86c76e5f9/

  This is good too.

  http://msdn.microsoft.com/en-us/library/ms141766(v=SQL.100).aspx

• Koen Verbeeck

  SSC Guru

  Points: 259155

  More actions

  August 9, 2011 at 2:02 am

  #1365660

  Duplicate post. Please read original thread with replies here:

  http://www.sqlservercentral.com/Forums/Topic1155393-147-1.aspx

  Please do not crosspost. It wasted people's time and fragments replies.

  Need an answer? No, you need a question
  My blog at https://sqlkover.com.
  MCSE Business Intelligence - Microsoft Data Platform MVP

• John Reddy

  Ten Centuries

  Points: 1250

  More actions

  August 14, 2011 at 3:11 pm

  #1368415

  Koen Verbeeck (8/9/2011)

  ---

  Duplicate post. Please read original thread with replies here:

  http://www.sqlservercentral.com/Forums/Topic1155393-147-1.aspx

  Please do not crosspost. It wasted people's time and fragments replies.

  Koen,

  That was my bad. I accidently posted in the business intelligence forum at first and then realized that it was best to post it under the integration services forum.

  Apologies

  -

  John