May 27, 2008 at 8:44 am
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?
May 27, 2008 at 11:58 am
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!
May 27, 2008 at 2:31 pm
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.
May 27, 2008 at 10:04 pm
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?
May 28, 2008 at 5:31 am
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.
May 28, 2008 at 9:27 am
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.
May 29, 2008 at 1:41 am
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
May 29, 2008 at 5:18 am
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.
June 2, 2008 at 9:10 am
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.
October 31, 2008 at 9:21 am
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
October 31, 2008 at 9:38 am
Hi mlimp,
To find the name of the account that is running the SQL Agent, look in Windows Services. That's the account that needs permissions on the folder. You could get round that by making the Agent account a member of the local Administrators group on the server, but I wouldn't - far too much power.
Cath
June 10, 2009 at 6:57 am
Has anyone found the resolution for this? I am also having the same issue.
July 22, 2009 at 4:49 pm
I have exact the same problem, I have a Foreach Loop to transfer 3 files, one loop for each file from one place to another, everything runs fine until I run it as a job agent. After I read the posts I changed the network mapping drive X:\ to the Universal address, \\myserver\myfolder, then it worked. I'd been working on this problem for hours, and googled for hours until i read the posts above. The permission issue was indeed causing lots of trouble, the XML configuration file seems to be a good solution for this, I also tried proxy account, which failed constantly so i gave up. One of my experiences is if a package won't work, try start a new one from scratch and make the modification, if you make changes on the one that not working, it seems the changes won't get updated. Another suggestion is to use the rules of devide and conqure, if you can transfer a single file from server a to server b, then it's not likely a permission issue, then the Foreach Loop failure was caused by something else.
March 1, 2011 at 12:28 pm
If and only if it is a valid security option .....
Go to Start> Admin Tools> Computer Management> Local Users & Groups> Groups> Administrators group> Add the service account ex. Domain\acctname that the SQL Server Agent is running under in services as mentioned above by Cath
August 23, 2023 at 12:06 am
Hi Team I am having the similar issue. SQL agent job is running successfully. The script usually first move csv files from In folder to data folder once data is processed into the database it move files into archive folder. So now the files are only stuck into data folder its not at all moving to archive folder niether i can see the data is processing into the database, However, job is running successfully. Any idea where I should look !!
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply