question

TaariqToffar-7981 avatar image
0 Votes"
TaariqToffar-7981 asked Monalv-msft edited

SSIS Excel Source Assisstance

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:

78759-image.png


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

78728-image.png



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

sql-server-integration-services
image.png (22.6 KiB)
image.png (31.2 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Monalv-msft avatar image
0 Votes"
Monalv-msft answered Monalv-msft edited

Hi @TaariqToffar-7981,

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.



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

YitzhakKhabinsky-0887 avatar image
0 Votes"
YitzhakKhabinsky-0887 answered YitzhakKhabinsky-0887 edited

HI @TaariqToffar-7981,

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:

78772-microsoftaceoledb120.png

Microsoft ACE OLEDB Provider download links:


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TaariqToffar-7981 avatar image
0 Votes"
TaariqToffar-7981 answered

@YitzhakKhabinsky-0887 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





5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TaariqToffar-7981 avatar image
0 Votes"
TaariqToffar-7981 answered YitzhakKhabinsky-0887 edited

Details error, still have not found a solution:

78837-capture.png



capture.png (43.2 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Based on the error message the ACE provider is working now.
Security permissions for the SQL Server Agent account are now at play.
You need to resolve them with your server admins.

0 Votes 0 ·