SQLSearcher
asked on
SQL Agent running SSIS package and reports succeeded but data has not changed.
Hello Experts Exchange
I have two SSIS packages, when I run them in BIDs they run fine and data is updated, when I schedule them to run in SQL Agent the jobs report Succeeded but not data has changed.
There is three steps in the SSIS package.
1) Copy Access file from server to local server. (Execute Process Task)
2) Truncate Table. (Execute T-SQL Statement Task)
3) Import data from Access DB in to SQL. (Data Flow Task)
I am using SQL Server 2012, on Windows 2008.
Has anyone seen this problem before? How do I get SQL Agent to work?
Regards
SQLSeracher
I have two SSIS packages, when I run them in BIDs they run fine and data is updated, when I schedule them to run in SQL Agent the jobs report Succeeded but not data has changed.
There is three steps in the SSIS package.
1) Copy Access file from server to local server. (Execute Process Task)
2) Truncate Table. (Execute T-SQL Statement Task)
3) Import data from Access DB in to SQL. (Data Flow Task)
I am using SQL Server 2012, on Windows 2008.
Has anyone seen this problem before? How do I get SQL Agent to work?
Regards
SQLSeracher
ASKER
Hello SimonAdept
The service account for SQL Agent is my user account, so there should be no problem there.
This is the message in the history;
Message
Executed as user: DMUKAD\plg. Microsoft (R) SQL Server Execute Package Utility Version 11.0.5532.0 for 64-bit Copyright (C) Microsoft Corporation. All rights reserved. Started: 08:00:00 Error: 2015-02-19 08:00:01.35 Code: 0xC000F427 Source: Copy file from Source Description: To run a SSIS package outside of SQL Server Data Tools you must install Copy file from Source of Integration Services or higher. End Error DTExec: The package execution returned DTSER_SUCCESS (0). Started: 08:00:00 Finished: 08:00:01 Elapsed: 0.64 seconds. The package executed successfully. The step succeeded.
Does this mean anything to you?
Regards
SQLSearcher
The service account for SQL Agent is my user account, so there should be no problem there.
This is the message in the history;
Message
Executed as user: DMUKAD\plg. Microsoft (R) SQL Server Execute Package Utility Version 11.0.5532.0 for 64-bit Copyright (C) Microsoft Corporation. All rights reserved. Started: 08:00:00 Error: 2015-02-19 08:00:01.35 Code: 0xC000F427 Source: Copy file from Source Description: To run a SSIS package outside of SQL Server Data Tools you must install Copy file from Source of Integration Services or higher. End Error DTExec: The package execution returned DTSER_SUCCESS (0). Started: 08:00:00 Finished: 08:00:01 Elapsed: 0.64 seconds. The package executed successfully. The step succeeded.
Does this mean anything to you?
Regards
SQLSearcher
>> Description: To run a SSIS package outside of SQL Server Data Tools you must install Copy file from Source of Integration Services or higher.
This line suggests that you dont have a necessary component (Copy file from Source) on the server. So that looks like your step 1 is failing.
It's not an SSIS task that I've ever done myself. The MS reference suggests that it is a File System task rather than a Execute Process Task.
This line suggests that you dont have a necessary component (Copy file from Source) on the server. So that looks like your step 1 is failing.
It's not an SSIS task that I've ever done myself. The MS reference suggests that it is a File System task rather than a Execute Process Task.
Do you have proper permissions to create file at local server folder?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Resolved issue by myself.
There are many scenarios where SSIS will return success when the developer/user would not consider the outcome a success. e.g. the final step found no data to import, but this is not an error, so it completes and reports success.
Try having a look in SQL Agent's job history for that job.