If you're trying to strip the leading zeros, I would go with this approach
This expression removes the INC from your string
REPLACE(WorkitemNumber,"INC","")
Once INC is removed, you can cast to a numeric type to drop the leading zeros and then back to a string to allow you to do concatenation or whatever else you may need to do
(DT_WSTR,10)((DT_I8)REPLACE(WorkitemNumber,"INC",""))
Personally, I find it more user friendly to chain Derived Columns together so that if it breaks, I can spot the error more easily than something like the above. In your case, I used the original replace to generate a column and then use
(DT_WSTR,10)((DT_I8)WorkItemNoINC)
in my second Derived column
Edit
Taking another bite at the apple
OLE_SRC Query
I have defined the following source query
SELECT
'INC0000001234' AS WorkitemNumber
, CAST(MONTH(D.foo) AS varchar(2)) + RIGHT('0' + CAST(DAY(D.foo) AS varchar(2)), 2) AS fudge
FROM
(
-- Generate a year's worth of dates
SELECT
DATEADD(d, NUMS.n, '2012-12-31') AS SourceDate
FROM
(
SELECT TOP 366 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n
FROM sys.all_columns AS SC
) NUMS
) D(foo);
This query generates 366 rows of data. A constant column named WorkItemNumber and one called fudge that will be all the possible month + day combinations. I left pad the number with a 0 so that a value of 121 is clearly January 21 and not December 1.
DER Drop INC
Here I create a new column called WorkItemNoINC and use the expression
REPLACE(WorkitemNumber,"INC","")
The result of this operation is that the text INC is removed from the source column and an empty string substituted in its stead.
DER WorkItemConcat
In this step, I concatenate the WorkItemNoINC column with our fudge value generated from the query. String + String yields string named WorkItemConcate (because I didn't proofread)
WorkItemNoINC + fudge
DER Strip leading zeros
In this operation, I will cast the value of WorkItemConcate to a bigint and back to string to strip the leading zeros.
(DT_WSTR,10)((DT_I8)WorkItemConcate)
Run
I added a data viewer after the last transformation and you can see that for all the possible values of day and month in a year, these expressions do not present a problem.
Making it fail
If I update my source query to use a NULL for WorkItemNumber, this package will not fail as you are experiencing.
If I change the casing in WorkItemNumber to inc, then I can generate this error.
Error: 0xC0049064 at DFT Whatevs, DER Strip leading zeros [18]: An error occurred while attempting to perform a type cast.
Error: 0xC0209029 at DFT Whatevs, DER Strip leading zeros [18]: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "DER Strip leading zeros" failed because error code 0xC0049064 occurred, and the error row disposition on "DER Strip leading zeros.Outputs[Derived Column Output].Columns[Derived Column 1]" 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.
Error: 0xC0047022 at DFT Whatevs, SSIS.Pipeline: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "DER Strip leading zeros" (18) failed with error code 0xC0209029 while processing input "Derived Column Input" (19). 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.
Similar but not the same as you. I don't have a 2008 instance handy to test against but try breaking your operations out across multiple derived columns transformations and add data viewers as needed.
