question

sujithkumarmatharasi-0343 avatar image
0 Votes"
sujithkumarmatharasi-0343 asked Monalv-msft commented

SSIS Package runs fine locally but fails on SQL Server agent

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

sql-server-transact-sqlsql-server-integration-services
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 commented

Hi @sujithkumarmatharasi-0343 ,

  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.



· 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.

Hi @sujithkumarmatharasi-0343,

May I know if you have anything to update?

Best regards,
Mona

0 Votes 0 ·
sujithkumarmatharasi-0343 avatar image
0 Votes"
sujithkumarmatharasi-0343 answered

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

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.

TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered

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.

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.

sujithkumarmatharasi-0343 avatar image
0 Votes"
sujithkumarmatharasi-0343 answered sujithkumarmatharasi-0343 published

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 :

88356-image.png


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](255) NULL,
[Policy Number_Format] [nvarchar](255) NULL,
[Lockdown Date] [nvarchar](255) NULL,
[Lockdown] [nvarchar](255) NULL,
[Claim Number] [nvarchar](255) NULL,
[Claim ID] [nvarchar](255) NULL,
[Tier] [nvarchar](255) NULL,
[Sector] [nvarchar](255) NULL,
[State] [nvarchar](255) NULL,
[State Lable] [nvarchar](255) NULL,
[Template] [nvarchar](255) NULL
) ON [PRIMARY]
GO

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

88326-image.png

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



image.png (46.0 KiB)
image.png (164.5 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.

pituach avatar image
0 Votes"
pituach answered

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.


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.