asked on

Scheduled SSIS Job using variables, all of the sudden failing via Job Activity Monitor

So, I have a job that has been on the schedule for about six months or so. Everythings has been fine, then all of the sudden it doesn't want to work.
Job: Process incoming eligibility file (sghs0310.11) [where "0310.11" is the date, and yes the ".11" is the extension]
The file is dropped on our ftp site, the job runs the next morning then emails me when the job is complete with success or failure. It's been working great until about two weeks ago, then I started receiving this message:

"Executed as user: HSP\sqlsvc. Microsoft (R) SQL Server Execute Package Utility Version 10.0.2531.0 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 8:44:10 AM Error: 2011-03-14 08:44:11.14 Code: 0xC001401E Source: SGHS_ELG_PBM_ETL Connection manager "FTP_GR_Elig_FFC" Description: The file name "\\hsftpsrv\FTPDATA\sghs\sghs0310.11" specified in the connection was not valid. End Error Error: 2011-03-14 08:44:11.14 Code: 0xC001401D Source: SGHS_ELG_PBM_ETL Description: Connection "FTP_GR_Elig_FFC" failed validation. End Error Error: 2011-03-14 08:44:11.47 Code: 0xC001401E Source: SGHS_ELG_PBM_ETL Connection manager "FTP_GR_Elig_FFC" Description: The file name "\\hsftpsrv\FTPDATA\sghs\sghs0310.11" specified in the connection was not valid. End Error Error: 2011-03-14 08:44:11.47 Code: 0xC0202070 Source: SGHS_ELG_PBM_ETL Connection manager "FTP_GR_Elig_FFC" Description: The file name property is not valid. The file name is a device or contains invalid characters. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 8:44:10 AM Finished: 8:44:11 AM Elapsed: 0.952 seconds. The package execution failed. The step failed."

Alpesh Patel

Please make sure your ftp site is accessible. We have faced same problem when your site credential expired it cause error.

RicS77

ASKER

It is our internal ftp site, not external. I did check that the credential is set to not expire and the password is still valid/same. When I run the package locally it works. my sqlsvc account used as my package proxy does has full control also.

RicS77

ASKER

Can someone please help me understand how Sql Agent job runs an SSIS package using a variable for an extended period of time perfectly fine, and then just decides to start failing stating that the file name is not valid....?

RicS77

ASKER

So Why do i pay for this service when I can't seem to get an answer to anything I ask it seems...

ASKER CERTIFIED SOLUTION

vdr1620

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

RicS77

ASKER

I've checked the access a few times. I've even retyped the password to ensure it was still granted.
Coincidentally I had installed an SSIS toolset the day before it started failling. I just finished uninstalling it and the job ran successfully. Not sure if this is a coincidence or what.