x

SSIS packages error

I have a job which calls a SSIS package that runs early in the morning. Most of the times it fails but just rerunning the job fix the issue. The SSIS packages has 5 dataflows. Each dataflow populates around 14 tables simeltanously. I did doubled the buffer size for the dataflow but it did not help. The error for the job:

1)input column "CreatedDate" (1306) on input "Destination Input" The column status returned was: "Conversion failed because the data value overflowed the specified type.". Description: "Invalid character value for cast specification.".

2)Description: The attempt to add a row to the Data Flow task buffer failed with error code

3)Description: Setting the end of rowset for the buffer failed

Any help will be great.

--Sudipta Parhi

more ▼

asked May 10, 2010 at 03:58 PM in Default

Sudipta gravatar image

Sudipta
12 3 3 3

(comments are locked)
10|1200 characters needed characters left

5 answers: sort newest

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

more ▼

answered Jun 22, 2010 at 06:02 PM

Dave Myers gravatar image

Dave Myers
123 15 15 16

(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Jun 22, 2010 at 10:05 AM

Scott Abrants gravatar image

Scott Abrants
11

(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered May 24, 2010 at 10:04 PM

Daniel Ross gravatar image

Daniel Ross
2.9k 10 13 14

(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered May 10, 2010 at 08:30 PM

Sudipta gravatar image

Sudipta
12 3 3 3

(comments are locked)
10|1200 characters needed characters left

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?

more ▼

answered May 10, 2010 at 05:08 PM

TimothyAWiseman gravatar image

TimothyAWiseman
15.5k 20 23 32

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x939
x27

asked: May 10, 2010 at 03:58 PM

Seen: 2901 times

Last Updated: May 10, 2010 at 03:58 PM