Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have an SSIS package targeted at SQL Server 2012.

I have it deployed into the Stored Packages in Integration Services and then have a SQL Server Agent job which executes it.

The first part of the package which moves data from a number of tables from a live database into a staging database execute successfully. I then have a data flow task which uses a Recordset Destination to collect a list of IDs of other records to move into the staging database.

When I execute the package manually by doing a Run Package from Integration Services (through SQL Server Management Studio) the package executes successfully, running the dataflow task which uses the recordset destination, however when run through the SQL Server Agent job I get the following error

Message Executed as user: xxxx. Microsoft (R) SQL Server Execute Package Utility Version 11.0.2100.60 for 64-bit Copyright (C) Microsoft Corporation. All rights reserved. Started: 09:51:49 Error: 2013-03-18 10:16:02.73 Code: 0xC000F427 Source: xxx_dataflowtask_xxx SSIS.Pipeline Description: To run a SSIS package outside of SQL Server Data Tools you must install Recordset Destination of Integration Services or higher. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 09:51:49 Finished: 10:16:02 Elapsed: 1452.78 seconds. The package execution failed. The step failed.

does anyone know how to resolve this problem?

share|improve this question
    
Do you have SQL Server Integration Services for SQL 2012 installed on the server? –  mrdenny Mar 18 '13 at 14:23
    
yep! the other parts of the package work if this section is disabled –  kzhen Mar 18 '13 at 14:34
    
What edition of SQL Server is installed on the server? –  mrdenny Mar 18 '13 at 14:47
    
SQL Server 2012 enterprise –  kzhen Mar 18 '13 at 14:54
1  
Despite the error message stating the problem is with the Recordset Destination, it smells much more like a permission issue (works fine for manual execution, automated execution fails). What account is the SQL Agent running under? Is the Integration Services job step executed under a stored credential? Either way, does the account have access to the file system where your BufferTempStorage and/or BLOBTempStoragePath are located (defined per data flow)? By default, those will go to %temp% See bimonkey.com/2008/04/… –  billinkc Mar 19 '13 at 3:40
show 7 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.