SSIS Excel Source Assisstance

Question

So I recently started with SSIS and I am having problems when deploying a data flow. I got it to work in visual studio by changing Run64BitRuntime on the package to false:

However when the package is deployed it does not work, I created a SQL Server Agent Job and changed the step to 32bit runtime and it still does not work, if I change the package to anything that does not involve an excel file it does work

Any help will be appreciated been struggling with this all day.

Answer 1

Hi @Taariq Toffar ,

1.According to the details error, it seems that the user who to execute ssis package in job does not have full permission to access to the excel file.
Please check if you run packages in job and open or use the excel files in other progress at the same time.

2.Please create a proxy that has the required permissions to the excel file.
A SQL Server Agent proxy account defines a security context in which a job step can run. Each proxy corresponds to a security credential. To set permissions for a particular job step, create a proxy that has the required permissions for a SQL Server Agent subsystem, and then assign that proxy to the job step.

Please refer to Create a SQL Server Agent Proxy.

Best regards,
Mona

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

HI @Taariq Toffar ,

However when the package is deployed it does not work

Please edit your question, and add the entire error.

I created a SQL Server Agent Job and changed the step to 32bit runtime and it still does not work

No need to use 32-bit runtime on the server. You need to use Microsoft ACE OLEDB provider on the SSIS run-time server. It has a 64-bit version. Just check the connection string in VS to learn what version is in use. It could be one of the following versions: 12.0, 15.0, or 16.0 Please make sure that the same version of ACE is installed on the server.

You can easily check what is installed by issuing the following command in SSMS:

EXEC master.sys.sp_MSset_oledb_prop;  

Here is what I see on my machine:

Microsoft ACE OLEDB Provider download links:

• https://www.microsoft.com/en-us/download/details.aspx?id=13255
• https://www.microsoft.com/en-us/download/details.aspx?id=39358
• https://www.microsoft.com/en-us/download/details.aspx?id=54920

Answer 3

@Yitzhak Khabinsky thanks for your help.

I now have both the 32bit and 64bit Microsoft.ACE.OLEDB.12.0 installed, after installing the latter the behavior is as follow:

• I can execute the package from the Integration Services Catalogs (Could not do this before)
• In VS using the Excel Source I cannot pick up sheets anymore
• Created a job for the above working package in SQL Server Agent and fails with error that can be found below

Date,Source,Severity,Step ID,Server,Job Name,Step Name,Notifications,Message,Duration,Sql Severity,Sql Message ID,Operator Emailed,Operator Net sent,Operator Paged,Retries Attempted
03/17/2021 16:15:02,test,Error,,TERRORHUB,test,,,The job failed. The Job was invoked by User TERRORHUB\ttoff. The last step to run was step 1 (1).,00:00:03,0,0,,,,0
03/17/2021 16:15:02,test,Error,1,TERRORHUB,test,1,,Executed as user: NT Service\SQLSERVERAGENT. Microsoft (R) SQL Server Execute Package Utility Version 15.0.2000.5 for 64-bit Copyright (C) 2019 Microsoft. All rights reserved. Started: 16:15:02 Package execution on IS Server failed. Execution ID: 42<c/> Execution Status:4. To view the details for the execution<c/> right-click on the Integration Services Catalog<c/> and open the [All Executions] report Started: 16:15:02 Finished: 16:15:04 Elapsed: 2.188 seconds. The package execution failed. The step failed.,00:00:03,0,0,,,,0

Answer 4

Details error, still have not found a solution: