SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Scheduled Job Does Nothing (SSIS Package)


Scheduled Job Does Nothing (SSIS Package)

Author
Message
mlimp
mlimp
SSC-Enthusiastic
SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)

Group: General Forum Members
Points: 108 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?
imtu80
imtu80
SSC Rookie
SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)

Group: General Forum Members
Points: 32 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!
Michael Earl-395764
Michael Earl-395764
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14003 Visits: 23078
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.
imtu80
imtu80
SSC Rookie
SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)

Group: General Forum Members
Points: 32 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?
Michael Earl-395764
Michael Earl-395764
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14003 Visits: 23078
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.
mlimp
mlimp
SSC-Enthusiastic
SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)

Group: General Forum Members
Points: 108 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.
pduplessis-723389
pduplessis-723389
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3341 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
Michael Earl-395764
Michael Earl-395764
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14003 Visits: 23078
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.
mlimp
mlimp
SSC-Enthusiastic
SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)

Group: General Forum Members
Points: 108 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.
JMartin-392745
JMartin-392745
Old Hand
Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)

Group: General Forum Members
Points: 343 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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search