Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Scheduled Job Does Nothing (SSIS Package) Expand / Collapse
Author
Message
Posted Tuesday, May 27, 2008 8:43 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, November 15, 2008 7:49 AM
Points: 20, Visits: 86
Hi,

I Scheduled a SSIS package on my Sql Server Agent, when i go to the Job History it says the Job succeded for the time it was scheduled to run, but im noticing the Job does nothing, i mean the Package is running but it's not doing what it is supossed to do.
I know the Package works because when i go to the BI Studio and rightclick>Execute Package it works.

Any help?
Post #506965
Posted Tuesday, May 27, 2008 11:58 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 25, 2009 5:15 PM
Points: 2, Visits: 22
I have same issue, When I right click on SSIS Job and run the package under SQL Agent it says run successfully but it does not download file(s) from FTP nor it loads the data (of course).

But, the SSIS package works fine when I run from the project or execute from Integration Services/Stored Pacakges/File System/[my package name]

Help please!
Post #507141
Posted Tuesday, May 27, 2008 2:31 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 10, 2010 5:07 AM
Points: 2,661, Visits: 23,078
Turn on logging in the package and look for errors and other messages. It is likely a permissions issue due to the SQL Agent running under different credentials, but you need to get the messages from SSIS to figure this one out.
Post #507262
Posted Tuesday, May 27, 2008 10:04 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 25, 2009 5:15 PM
Points: 2, Visits: 22
It doesn't show anything in log.
Here it is
Microsoft (R) SQL Server Execute Package Utility Version 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved.
Started: 10:52:46 PM DTExec: The package execution returned DTSER_SUCCESS (0). Started: 10:52:46 PM Finished: 10:52:56 PM Elapsed: 10.343 seconds.
The package executed successfully. The step succeeded.,00:00:10,0,0,,,,0

FYI, I am downloading file from FTP to a network storage drive. Do I need to give read write permission on NSD. If yes, then to which user account?
Post #507403
Posted Wednesday, May 28, 2008 5:31 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 10, 2010 5:07 AM
Points: 2,661, Visits: 23,078
Your permissions will have to be configured to allow access for the SQL Agent service account unless you have specified a proxy (which is unlikely).

What you have posted is not the complete set of logging from a package log. Right-click in the control flow of the package and choose "Logging" from the context menu to configure logging. To find your issue, start with logging all errors, warnings, messages, validation, and information events.
Post #507578
Posted Wednesday, May 28, 2008 9:27 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, November 15, 2008 7:49 AM
Points: 20, Visits: 86
Michael, i think i found my problem.

I have a For Each Loop Container that is supossed to read files from a folder.
The loggin says: For Each File enumerator is empty. The For Each File enumerator did not find any files that matched the file pattern, or the specified directory was empty.

But that's not true. I can run the package from the BI Studios and i will run and find the files.

Here's the structure of my package:

I've got a Foreach Loop thats reading from a restricted folder (but my Windows User has access)
and its got a Variable called FileName for mapping the actual File's Name

Inside it at my Data Flow I've got a Flat File Source thats connected to a FlatFileConnManager. That Conn manager has its ConnString set to a unexisting file in C:\MyFile.txt because i have a File System Task which has a validation that requires the FlatFileConnManager to be set to an especific file and not a Variable.
But my FlatFileConnManager has an Expression for the ConnectionString to be set to the User::FileName variable mentioned before.

I think maybe somewhere there something's wrong that the log is telling me the FEF enumator is empty.

I also noticed in the SQl Server Agent that in the Steps of my Job when i go to the Data Sources tab i see my FlatFileConnManager and it's connection string is C:\MyFile.txt. Maybe that's the problem the it doesnt know it has to map it to the variable?

I have no idea.

I'd appreciate any help there.
Post #507780
Posted Thursday, May 29, 2008 1:41 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, November 3, 2010 2:32 AM
Points: 1,249, Visits: 400
Hey,

Even if your windows user has access, this job will be run by your SQL server agent.
As such, the question will always be, does your SQL server agent have rights to look at that Windows NT folder

~PD
Post #508230
Posted Thursday, May 29, 2008 5:18 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 10, 2010 5:07 AM
Points: 2,661, Visits: 23,078
If it is working in the IDE, the variable mapping is ok. This is a permissions issue, or a relative folder issue.

Check permissions first - log in with the SQL Agent service account and try running the package. In the default configurations, the packages being run through the Job Agent will run with the SQL Agent credentials.

Remember that the folders the package can see are relative to the server the Job Agent is running on - not the server the SSIS package is stored on (if that is a different server). So, make sure the folders are on the correct drive relative to the server that is running the package. Also remember that the SQL Agent is running as a service so mapped drives may not be there - use UNC for network drives.
Post #508300
Posted Monday, June 2, 2008 9:10 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, November 15, 2008 7:49 AM
Points: 20, Visits: 86
Definitely it is a Sql Agent Credential problem.

Is it possible that the Sql Agent takes advantage of the credentials the Server has ?
Because i would like to avoid as much as posible having to ask for permission for the Sql Agent in the folder.

If not, what's the exact name of the user i need to ask permission for (that is the Sql Agent user) ?


Thx.
Post #509940
Posted Friday, October 31, 2008 9:21 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, January 31, 2014 3:13 PM
Points: 103, Visits: 357
I am expereinceing a very similar issue, basically I am using a file enumberator to work through some files that are uploaded to a database.

The package works fine from VS2005 both logged in as myself and when I log in using the service account details. However once uploaded the package warns that the For Each file enumerator is empty.

There is no issue with permissions to the directory as the FTP download task works fine and the service accoutn ahs full access to the directory that the files are stored in once downloaded.

The package is as follows;

Load variables with file source locations
Download via FTP to directory - Fine
Loop through files to load data - FAILS with warning that the for each file enumberator is empty or cannot find matching files.
Query database and load result set variable - Fine
shred result set and assign to package variables - Fine
script task to take variables, add formatting and load to variable - Fine
send email task with formatted message - Fine

If anyone has any advice it would be appreciated.

Many thanks

John
Post #595067
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse