question

Can you be a little more specific? How does it fail? What errors show up in the log?

I am just guessing here, but the closest I have seen to this is when something else was tying up resources the package needed for long periods of time. Is there another large job running around that time that could be blocking it?

There is no job running at back end at that time. I get error like:

Executed as user: DATAWAREHOUSE\SYSTEM. Microsoft (R) SQL Server Execute Package Utility Version 10.0.2531.0 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 4:51:26 AM Error: 2010-05-09 04:52:21.55 Code: 0xC0202009 Source: Pop Events Destination - evEnrollmentSession [1271] Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80004005 Description: "Invalid character value for cast specification.". An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80004005 Description: "Invalid character value for cast specification.". End Error Error: 2010-05-09 04:52:21.57 Code: 0xC020901C Source: Pop Events Destination - evEnrollmentSession [1271] Description: There was an error with input column "CreatedDate" (1306) on input "Destination Input" (1284). The column status returned was: "Conversion failed because the data value overflowed the specified type.". End Error Error: 2010-05-09 04:52:21.57 Code: 0xC0209029 Source: Pop Events Destination - evEnrollmentSession [1271] Description: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "input "Destination Input" (1284)" failed because error code 0xC020907A occurred, and the error row disposition on "input "Destination Input" (1284)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure. End Error Error: 2010-05-09 04:52:21.57 Code: 0xC0047022 Source: Pop Events SSIS.Pipeline Description: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Destination - evEnrollmentSession" (1271) failed with error code 0xC0209029 while processing input "Destination Input" (1284). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 4:51:26 AM Finished: 4:53:15 AM Elapsed: 108.951 seconds. The package execution failed. The step failed.

I looks like there is an invalid date trying to be loaded, but it is a bit weird that it works OK when you rerun it.

When you open the package in BIDS, does it ever fail? If it does, I suggest you redirect the error rows and have a look at the dataset of the redirect rows and see if you can identify the problem.

Where are you getting the source for the date data? Many times this happens when loading data from some type of text file (i.e. csv) because every element in those files are text by default. Without explicit conversion and checking for NULL values, values that cannot be converted, and other CAST or conversion issues on the way in you are dealing with a time bomb. When working with data elements from these types of sources I ALWAYS use a derived column task and explicitly convert the values and do my type checking there before allowing it to just flow to the pipeline as is.

My first impulse would be to look for 1 of 2 things. The first has already been stated once, and that is that some other process (to include maintenance jobs) may be accessing the database at the time your package is attempting to do the insert. The other , based on the fact that you say you can run the job manually without failure would be permissions. Whenever you run the job manually, it takes on your permission set (which I would assume would be sa), but the job owner may not have sufficient permissions to perform the actions on the database. I would double check your job owner permissions