We have a job that is using a foreach loop to grab input from a text file, perform some transformations and place it into a SQL Server database using OLE DB providers. The package works fine from the local environment (within Visual Studio). Then when you import the package from the file system into the MSDB package store, it works fine from there as well. The problem is when we try to schedule the job using the SQL Server Agent. It is a one step job and it says its successful when we run it, but it doesn't put any information into the database itself. We have other packages running that are working that run from the package store and take information from one database and put it into the same SQL Server DB we are trying to put our text file information into. Has anyone encountered the problem where the package works from the file system as well as the package store, but doesn't work when you schedule it as a job?

Hi John, Is the log file for the package created? Please check the log file which helps what exactly happened. But check the log destination (type) , you can check it in the provider type under logging in SSIS menu of the SSIS solution. Regards and good Wishes, Deepak.

Hi John, Is the log file for the package created? Please check the log file which helps what exactly happened. But check the log destination (type) , you can check it in the provider type under logging in SSIS menu of the SSIS solution. Regards and good Wishes, Deepak.

That's for the reply. I saw this article before posting on logs (http://msdn.microsoft.com/en-us/library/ms138020.aspx), but where do you see the logs for the job that ran? In the solution file, I went to SSIS --> Logging and enabled it all and had it add to a CSV file. I'm not sure where the logs are supposed to go? I see where the error logs are for SQL Server Agent, but since no errors are thrown, nothing for this job/package show up there.

Actually I found that I put the logs into the event viewer and they all come out as messages. There are a lot of them. What log entry should I use to focus in on the issue at hand?

Hi John, There are many events which can be logged. They are under Details tab in the same logging uunder SSIS menu.They have to be checked before the progress or error can be logged in the file or event viewer. Concentrate mostly on OnError,OnProgress,OnInformation. For the event viewer, the log can be checked under Applicaiton Logs under Windows Logs (might have already been checked by you) Also, when you add the package from msdb in SQL Agent job , check if the server is configured properly for the corresponding step.Regards and good Wishes, Deepak.

Hi John, There are many events which can be logged. They are under Details tab in the same logging uunder SSIS menu.They have to be checked before the progress or error can be logged in the file or event viewer. Concentrate mostly on OnError,OnProgress,OnInformation. For the event viewer, the log can be checked under Applicaiton Logs under Windows Logs (might have already been checked by you) Also, when you add the package from msdb in SQL Agent job , check if the server is configured properly for the corresponding step.Regards and good Wishes, Deepak.

maybe the account which sql server agent service runs under it, hasn't access to specified physical file, or you used windows authentication in data base connection managers and this account hasn't appropriate permission on database. http://www.rad.pasfu.com

Problem has been solved. That was one of the issues. Turning on logging helped with that. There was also issues with a mapped network drive. We needed to put in a script that mapped the network drive in which we were grabbing the file.