1

In my Development machine I have MS excel is installed. I'm able to export data into excel from DB through SSIS (visual studio). but when i deployed it to QA environment where MS excel is not installed, the package is failing with the below errors. Please help me if it really required to have MS excel software installed on the target machine while exporting data from database through SSIS to excel.

this is the query i'm using create excel and tabs in excel sheet. Creation of excel file and new tab "inserted" in it. 

CREATE TABLE Inserted(

 [fileid] integer 
      ,[filename] string
      ,[rxcui] string 
      ,[tty] string
      ,[rxnorm_description] string
      ,[related_brand_name] string
      ,[related_scdc] string
      ,[related_df] string
      ,[related_ndc] string
      ,[create_date] date
      ,[create_user] string 
      ,[status] string
      ,[plan_year] string
      ,[update_date] date
      ,[update_user]string 
)

Connection manager used for the above script: 

@[User::var_excel_destination] +"\\"+"FRF_File_Import_Detail_"+ Right("0" +(DT_STR,4,1252) datepart("yyyy", getdate()),4)  
+ Right("0" +(DT_STR,2,1252) datepart("mm", getdate()) ,2)
+ Right("0" +(DT_STR,2,1252) datepart("dd", getdate()),2)+Right("0" + (DT_STR,2,1252) DatePart("hh",getdate()),2) 
+ Right("0" + (DT_STR,2,1252) DatePart("mi",getdate()),2) 
+ Right("0" + (DT_STR,4,1252) DatePart("ss",getdate()),2) +".xls"

It is failing at this step with the below error:

Error Description :Failed to acquire connection "ECM FRF File Import Processor". Connection may not be configured correctly or you may not have the right permissions on this connection.

Please help.

Hadi
  • 36,233
  • 13
  • 65
  • 124
Vikram D
  • 55
  • 1
  • 7

3 Answers3

1

No, the only thing that is required is the jet engine. https://www.microsoft.com/en-us/download/details.aspx?id=13255

Jason Byrd
  • 566
  • 3
  • 7
0

Yes,It requires MSexcel to be installed in the system.As Without it We dnt have internal definations to make system understand about the file format altogether.

Chetan Kulkarni
  • 404
  • 4
  • 15
  • Hi Chetan, Thank you for your input but in one of the posts here it says not really required. please refer the link below for your reference. https://stackoverflow.com/questions/19710758/ssis-export-to-excel-do-i-need-excel-on-the-target-machine – Vikram D Jun 24 '17 at 09:52
  • My bad,the answer there says,, you may need if you don't have 32 bit system(I have 64 bit system, when I was doing the same task,I found that , excel was important to be installed in Target machine) – Chetan Kulkarni Jun 24 '17 at 12:58
  • Hi Chetan, Please refer this link for more details. https://social.msdn.microsoft.com/Forums/en-US/c6c13caf-5a23-4b39-8c85-1bf390107393/is-it-necessary-to-have-ms-excel-software-installed-on-the-target-machine-while-exporting-data-from?forum=sqlintegrationservices – Vikram D Jun 28 '17 at 10:29
  • And, Excel software is not mandatory while exporting data to excel through SSIS. I have executed it on Visual studio. It is creating the file. But, the problem is when im running the deployed SSIS package through SQL job. – Vikram D Jun 28 '17 at 10:59
  • Okay ,my bad.Thanks – Chetan Kulkarni Jun 28 '17 at 11:12
0

I just set run in 32 bit mode on SQL server job. It worked like a charm.

Vikram D
  • 55
  • 1
  • 7
  • Please see this article: https://www.google.com/amp/s/balwantpatel.wordpress.com/2015/12/09/myth-excel-connection-manager-works-only-in-32-bit-mode-of-ssis/amp/ – Jason Byrd Jul 14 '17 at 05:39
  • Good Information Jason. Thank you Very much. But the problem in my scenario was: 1. I have Microsoft.Jet.OLEDB.4.0 installed in 32 bit. 2. set the SSDT to run in 32 bit mode. 3. It is working fine in SSDT. 4. when deployed and asked to run through SQL job, it was failing. 5. I set the property of the job to "run time 32 bit" and worked like a charm. – Vikram D Jul 14 '17 at 11:25