Schedule Jobs does Nothing - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

Schedule Jobs does Nothing

Author

Topic

loudbliss
Starting Member

37 Posts

Posted - 2008-05-27 : 10:39:58

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?

tmitch
Yak Posting Veteran

60 Posts

Posted - 2008-05-27 : 21:25:29

Just curious, what is it that the package is doing? Are you using transactions in the package at all?

Do you have it deployed to the file system or MSDB?

Tim

---------------------

Tim Mitchell
www.BucketOfBits.com

loudbliss
Starting Member

37 Posts

Posted - 2008-05-28 : 11:31:45

Hi Tim, my package uses transactions because it insert/updates some rows.
Basically i read all the files of a folder, proceess its data and inserts in a SQL DB.

i have doplyed in the file System.

Here's an explanaition of what i could find in my loggins:

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.

rgombina
Constraint Violating Yak Guru

319 Posts

Posted - 2008-05-29 : 07:40:39

Try to add SQL Agent account to the restricted folder. It could be a permission of some sort.

loudbliss
Starting Member

37 Posts

Posted - 2008-06-02 : 11:13:16

Yeah, is seems to be a permission problem, im going to work on that to see if it works. Thx

rgombina
Constraint Violating Yak Guru

319 Posts

Posted - 2008-06-02 : 11:20:36

Just to narrow it down - at top level of the folder, add SQL Agent account with Full Access. Also, make sure Read Only is not checked.