SSIS Package runs fine locally but fails on SQL Server agent

Question

Hi All,

I have an SSIS package that runs fine locally on my machine however it fails when I schedule it through SQL Agent.

After spending some time and trying to understand what's going on I think I figured out the error but not sure why it's happening.

So my SSIS package loads an excel file into a SQL table, as it's failing when I schedule in agent, I tried blanking out few columns in excel and then the job runs fine. So it's basically a few columns in the file which are causing the issue which I don't understand why?

I don't think the data type is an issue because those columns would load fine when I run them manually and the SQL table gets the data it's just the automation thing where it happens.

Can someone throw some ideas here if you have experienced this before, please?

Thanks,
Sujith

Answer 1

Good day,

SSIS Package runs fine locally but fails on SQL Server agent

Option 1: permissions issue

When you execute the package directly then you use your user permissions but when it is executed by the SQL Server Agent then you are using the permission of the user that execute the service. Check the permission and make sure the Agent user have the needed permission to execute the task

Check this link:
https://www.mssqltips.com/sqlservertip/2163/running-a-ssis-package-from-sql-server-agent-using-a-proxy-account/

2Do: Please provide the exact error messages which you get

More information: I tried blanking out few columns in excel and then the job runs fine. So it's basically a few columns in the file which are causing the issue which I don't understand why?

This discription might be related to the configuration of the target table and not only to the source file.

2Do! Please provide the DDL of the target table (query to create the table and all the related constrains and indexes). In addition a sample of excel file can help.

Answer 2

Hello Pituach,

Thanks for your reply.

1) I don't see it as a permissions issue as the package runs fine when i remove some columns and automate it , it runs fine.

2) Below is the error message i receive when i schedule it through SQL agent :

Here is the DDL of my table :

USE [ClaimsMI_Analysis]
GO

/****** Object: Table [FCA].[Portal_LCStates_Progress] Script Date: 15/04/2021 16:23:57 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Test](
[Policy Number] nvarchar NULL,
[Policy Number_Format] nvarchar NULL,
[Lockdown Date] nvarchar NULL,
[Lockdown] nvarchar NULL,
[Claim Number] nvarchar NULL,
[Claim ID] nvarchar NULL,
[Tier] nvarchar NULL,
[Sector] nvarchar NULL,
[State] nvarchar NULL,
[State Lable] nvarchar NULL,
[Template] nvarchar NULL
) ON [PRIMARY]
GO

Attached is the sample excel file i use to load into the table.

Another point to note, i created this table within the package thinking may be the data types will be off and hoping if i create the table in the package the table will pick up the data types and load accurately which runs fine locally though.

Thanks,
Sujith

Answer 3

Your error is coming from trying to read the Excel sheet, not the SQL Server table.

SSIS requires the exact same format of the source and destination at design time, not run time. Your code is apparently looking for a sheet named "ConfigurableRfeStateReport Phas" which does not exist in your source Excel file.

Answer 4

The sheet name is not the issue, the sheet name exists fine and as i said the package runs fine locally and if i exclude lets say last 5 columns or something the sql agent job runs fine too . So i dont see an issue with the file name or sheet name

Answer 5

Hi @sujith kumar matharasi ,

1. It seems that the external data of Excel Source has been changed.
   So, please delete the Excel Source and Excel Connection Manager and then create new Excel Source and Excel Connection Manager.
2. Please set the ValidateExternalMetadata to False.
3. Please share the screenshot of preview data in Excel Source Editor.

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.