2

I've been working on an SSIS package for which I've added derived columns (which I've posted earlier on here). The issue I'm running into is that this child package is running fine in one environment, but for some reason, is failing in my Dev environment.

The errors occurring are:

"SSISError Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "component "DerivedColumn" (9567)" failed because error code 0xC0049067 occurred, and the error row disposition on "output column "SchemaID"(9744)" specifies failure on error" and "SSISError Code DTS_E_PROCESSINPUTFAILED. TheProcessInput method on component "Derived Column" (9567) failed with error code 0xC0209029 while processing input "Derived Column Input"(9568). The identified component returned an error from the ProcessInputmethod".

I've compared the table's fields, particularly SchemaID in both environments and they're identical. I even tried recreating the particular table in Dev and re-tried running the package, to no avail.

This is the Derived Column Transformation Editor screen:

enter image description here

Can anyone please make any suggestions on this? Thank you in advance!

Hadi
  • 36,233
  • 13
  • 65
  • 124
daniness
  • 363
  • 1
  • 4
  • 21
  • Are you sure that the SchemaID transformation is always returning everything less than 13 characters? – rvphx May 22 '18 at 17:48

2 Answers2

0

Most probably its bad data that is not being converted through your derived transformation. What would be ideal is to create a test table and change the data type of SchemaID column to varchar and get all the data in there. It will be easier for you to debug the issue. Alternatively, you may want to open the "Advanced Editor" tab for your derived column and change either the data type of input schemaID or the length of it (never mind if its int or something else that doesnt support lengths).

rvphx
  • 2,324
  • 6
  • 40
  • 69
0

The SchemaId expression is the following:

SUBSTRING((DT_WSTR,15)PCMRetrievalCode,3,FINDSTRING(((DT_WSTR,15)PCMRetrievalCode),"C"C,1) - 3)

I think that the issue is caused by the FINDSTRING function. In case that PCMRetreievalCode column doesn't contains "C" or it will return an index < 3 then FINDSTRING(((DT_WSTR,15)PCMRetrievalCode),"C",1) - 3 will throw an exception because in the SUBSTRING function the length parameter should be positive.

You can add a conditional ? : to solve the issue:

FINDSTRING(((DT_WSTR,15)PCMRetrievalCode),"C",1) >= 3 ? SUBSTRING((DT_WSTR,15)PCMRetrievalCode,3,FINDSTRING(((DT_WSTR,15)PCMRetrievalCode),"C"C,1) - 3) : ""

Another suggestion is to increase the column length more than 13.

Hadi
  • 36,233
  • 13
  • 65
  • 124