Running a SSIS Package from SQL Server Management Studio 2008

I have set up a Import that copies data from a database on SQLExpress 2005 and Deletes and replaces the Data in a Database on SQL Server Management Studio 2008.

This Runs fine when i set it up for the first time? but if i try to assign a job in SQL Server Management Studio 2008 to run the procedure at a set time it fails?

It also fails if i try to run the stored procedure manually? the only way i can get it too work is to re create from new each time?

Can anyone shed any light on why this may be happening?

The Error message i get ;

Date,Source,Severity,Step ID,Server,Job Name,Step Name,Notifications,Message,Duration,Sql Severity,Sql Message ID,Operator Emailed,Operator Net sent,Operator Paged,Retries Attempted 09/01/2014 15:51:14,PostingJob_Aron,Error,0,RFWSQL,PostingJob_Aron,(Job outcome),,The job failed. The Job was invoked by User sa. The last step to run was step 1 (Import Data).,00:00:18,0,0,,,,0 09/01/2014 15:51:14,PostingJob_Aron,Error,1,RFWSQL,PostingJob_Aron,Import Data,,Executed as user: RFWSQL\SYSTEM. Microsoft (R) SQL Server Execute Package Utility Version 10.50.2500.0 for 32-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 15:51:15 Error: 2014-09-01 15:51:32.19 Code: 0xC0202009 Source: PostingImportAron Connection manager "SourceConnectionOLEDB" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "Login timeout expired". An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.". An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "Named Pipes Provider: Could not open a connection to SQL Server [1326]. ". End Error Error: 2014-09-01 15:51:32.19 Code: 0xC020801C Source: Data Flow Task 1 Source - MyPostings [1] Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "SourceConnectionOLEDB" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed. End Error Error: 2014-09-01 15:51:32.20 Code: 0xC0047017 Source: Data Flow Task 1 SSIS.Pipeline Description: component "Source - MyPostings" (1) failed validation and returned error code 0xC020801C. End Error Error: 2014-09-01 15:51:32.20 Code: 0xC004700C Source: Data Flow Task 1 SSIS.Pipeline Description: One or more component failed validation. End Error Error: 2014-09-01 15:51:32.22 Code: 0xC0024107 Source: Data Flow Task 1 Description: There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 15:51:15 Finished: 15:51:32 Elapsed: 17.14 seconds. The package execution failed. The step failed.,00:00:18,0,0,,,,0 09/01/2014 15:45:59,PostingJob_Aron,Error,0,RFWSQL,PostingJob_Aron,(Job outcome),,The job failed. The Job was invoked by User sa. The last step to run was step 1 (Import Data).,00:00:21,0,0,,,,0 09/01/2014 15:45:59,PostingJob_Aron,Error,1,RFWSQL,PostingJob_Aron,Import Data,,Executed as user: RFWSQL\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: 15:45:59 Error: 2014-09-01 15:46:20.59 Code: 0xC0202009 Source: PostingImportAron Connection manager "SourceConnectionOLEDB" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "Login timeout expired". An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.". An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "TCP Provider: A connection attempt failed because the connected party did not properly respond after a period of time or established connection failed because connected host has failed to respond. ". End Error Error: 2014-09-01 15:46:20.59 Code: 0xC020801C Source: Data Flow Task 1 Source - MyPostings [1] Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "SourceConnectionOLEDB" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed. End Error Error: 2014-09-01 15:46:20.59 Code: 0xC0047017 Source: Data Flow Task 1 SSIS.Pipeline Description: component "Source - MyPostings" (1) failed validation and returned error code 0xC020801C. End Error Error: 2014-09-01 15:46:20.59 Code: 0xC004700C Source: Data Flow Task 1 SSIS.Pipeline Description: One or more component failed validation. End Error Error: 2014-09-01 15:46:20.59 Code: 0xC0024107 Source: Data Flow Task 1 Description: There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 15:45:59 Finished: 15:46:20 Elapsed: 21.14 seconds. The package execution failed. The step failed.,00:00:21,0,0,,,,0 09/01/2014 15:24:00,PostingJob_Aron,Error,0,RFWSQL,PostingJob_Aron,(Job outcome),,The job failed. The Job was invoked by Schedule 24 (Run). The last step to run was step 1 (Import Data).,00:00:22,0,0,,,,0 09/01/2014 15:24:00,PostingJob_Aron,Error,1,RFWSQL,PostingJob_Aron,Import Data,,Executed as user: RFWSQL\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: 15:24:00 Error: 2014-09-01 15:24:21.93 Code: 0xC0202009 Source: PostingImportAron Connection manager "SourceConnectionOLEDB" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "Login timeout expired". An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.". An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "TCP Provider: A connection attempt failed because the connected party did not properly respond after a period of time or established connection failed because connected host has failed to respond. ". End Error Error: 2014-09-01 15:24:21.93 Code: 0xC020801C Source: Data Flow Task 1 Source - MyPostings [1] Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "SourceConnectionOLEDB" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed. End Error Error: 2014-09-01 15:24:21.93 Code: 0xC0047017 Source: Data Flow Task 1 SSIS.Pipeline Description: component "Source - MyPostings" (1) failed validation and returned error code 0xC020801C. End Error Error: 2014-09-01 15:24:21.93 Code: 0xC004700C Source: Data Flow Task 1 SSIS.Pipeline Description: One or more component failed validation. End Error Error: 2014-09-01 15:24:21.93 Code: 0xC0024107 Source: Data Flow Task 1 Description: There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 15:24:00 Finished: 15:24:21 Elapsed: 21.156 seconds. The package execution failed. The step failed.,00:00:21,0,0,,,,0 09/01/2014 15:11:00,PostingJob_Aron,Error,0,RFWSQL,PostingJob_Aron,(Job outcome),,The job failed. The Job was invoked by Schedule 24 (Run). The last step to run was step 1 (Import Data).,00:00:00,0,0,,,,0 09/01/2014 15:11:00,PostingJob_Aron,Error,1,RFWSQL,PostingJob_Aron,Import Data,,Executed as user: RFWSQL\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: 15:11:00 Error: 2014-09-01 15:11:00.74 Code: 0xC0014060 Source: {7A71992F-6187-4E30-AB94-F6A949E6EC47} Description: Failed to decrypt a package that is encrypted with a user key. You may not be the user who encrypted this package or you are not using the same machine that was used to save the package. End Error Could not load package "\PostingImportARON" because of error 0x8009000B. Description: Key not valid for use in specified state. Source: Started: 15:11:00 Finished: 15:11:00 Elapsed: 0.125 seconds. The package could not be loaded. The step failed.,00:00:00,0,0,,,,0

It is very likely that the table names (destination) are hardcoded to be something. So when run first time, it creates the object.

When you run a second time, that object already exists and therefore fails and exits out at that point in the code - not running your SP as it will be run as a single block or transaction.

What you need is something to check for the existence of the object name wrapped in an IF block so that the remaining logic can be processed.

IF OBJECT_ID('tempdb..#YourSpecificDates') IS NOT NULL 
    BEGIN
        DROP TABLE #YourSpecificDates
        /*Do other processes*/
        /*Exec your SP*/
    END