Solved

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

Posted on 2011-03-15
8
1,149 Views
Last Modified: 2013-11-29
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."
0
Comment
Question by:RicS77
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
8 Comments
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 35138194
Please make sure your ftp site is accessible. We have faced same problem when your site credential expired it cause error.
0
 

Author Comment

by:RicS77
ID: 35138407
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.
0
 

Author Comment

by:RicS77
ID: 35148144
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....?
0
Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

 

Author Comment

by:RicS77
ID: 35298171
So Why do i pay for this service when I can't seem to get an answer to anything I ask it seems...
0
 
LVL 16

Accepted Solution

by:
vdr1620 earned 500 total points
ID: 35298743
Have you checked your access to the FTP server..try to open it manually and see if anything has changed recently.. I am sure you would have done this but then.. Also, try to run the package manually and see if you get the same error.. Let me know, what you see
0
 

Author Comment

by:RicS77
ID: 35299509
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.
0

Featured Post

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Configuring network clients can be a chore, especially if there are a large number of them or a lot of itinerant users.  DHCP dynamically manages this process, much to the relief of users and administrators alike!
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
After creating this article (http://www.experts-exchange.com/articles/23699/Setup-Mikrotik-routers-with-OSPF.html), I decided to make a video (no audio) to show you how to configure the routers and run some trace routes and pings between the 7 sites…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question