sql server - SSIS - Derived Column

Question

I am having an issue with using the Derived Column Transformation Editor and was wondering if anyone on here could help me out.

I have a column called WorkitemNumber that holds information like INC0000001234

I want to change the WorkitemNumber to a more unique number to prevent overlapping entries in between months. So I came up with this...

I have a Derived Column:

Derived Column Name: ConvertID
Derived Column: <add as new column>
Expression: REPLACE(WorkitemNumber,"INC","") + "913"
Data Type: Unicode string [DT_WSTR]
Length: 258

The expression above changes INC0000001234 to 0000001234913. What I would like to do is cut out all of the extra 0s, but I want to refrain from using LTRIM because it will break once the incident report numbers go to the next set of thousands.

I am pretty new with using SSIS and it would be really helpful if someone would be able to tell me how I would go about getting rid of the extra 0s on the end regardless of the amount of 0s in front of the incident number.

I would want the final output to be 1234913 recardless if the incident number was INC0000001234 or INC001234.

EDIT

I was able to drop the 0s but I am getting the following error and the information is not being written into my other tables...

[SQL03 [459]] Error: 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: "Invalid character value for cast specification".

[SQL03 [459]] 
Error: There was an error with input column "ConvertIncidentID" (1015) 
on input "OLE DB Destination Input" (472). 
The column status returned was: "Conversion failed because the data value 
overflowed the specified type.".

[SQL03 [459]] 
Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.  
The "input "OLE DB Destination Input" (472)" 
failed because error code 0xC020907A occurred, and the error row disposition on 
"input "OLE DB Destination Input" (472)" 
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.

[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED.  
The ProcessInput method on component "SQL03" (459) 
failed with error code 0xC0209029 while processing input "OLE DB Destination Input" 
(472). 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.

Answer 1

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.