Hi. I created an SSIS package to load data from csv files into a sql server 2008 table. I created the SSIS package directly on the server using Visual Studio 2008. The SSIS package and the load folder all reside on the same server on the local drives (no mapped drives).
When I execute the package directly by double-clicking on it and pushing the Execute button, it works perfectly. Several weeks ago I created a SQL Agent job to execute this job every morning at 7am and it was working fine for at least one week. Then, it stopped loading the data but with no error and saying the load was successful. It is very strange.
I have several other similar SSIS programs running from the same folder on the same server with no problem (these have been running well for several years). Just this program seems to have stop loading data but with no errors. I've pasted below what I see in the job history but it didn't do anything and it seems as though the step is being ignored/bypassed even though it used to work. I can't think of anything I changed. I had moved on to another project but found out about the problem when users running reports told me data was missing.
I have read many posts about permissions and tried changing the owner to "sa" but that also didn't work. I am still getting no errors but data isn't loading.
I do have a local copy of SQL Server Mgmt Studio on my computer which I didn't have when I created the load programs from years ago that work fine but I did not create the SSIS program locally on my computer. I did log onto the server like I did years ago and create the SSIS programs that way. I probably did initially create the SQL Agent job locally so thinking that might be the issue, I created a new SQL Agent job directly on the server but that didn't help either.
Clearly I am missing something. Not sure what else to try. Oh yeah I also rebooted the server, just in case.
Date 12/17/2013 2:57:01 PM
Log Job History (Run_Quality_RGA_Data_Load_112613)
Step ID 1
Job Name Run_Quality_RGA_Data_Load_112613
Step Name Run SSIS Pkg to load RGA data from CSV files
Sql Severity 0
Sql Message ID 0
Operator Net sent
Retries Attempted 0
Executed as user: UXX\SYSTEM. Microsoft (R) SQL Server Execute Package Utility Version 10.50.2500.0 for 64-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 2:57:02 PM DTExec: The package execution returned DTSER_SUCCESS (0). Started: 2:57:02 PM Finished: 2:57:02 PM Elapsed: 0.281 seconds. The package executed successfully. The step succeeded.