Derived Column Error Help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SSIS and Import/Export (2008)

Derived Column Error Help

Author

Topic

jscot
Posting Yak Master

106 Posts

Posted - 2011-02-21 : 21:27:33

Hi guys,

Here is my sample data listed below

ADDRESS
san diego ca 91211
los angles ca 93455

my target field has city,state,zip

i am using these expression in derived column transformation to parse address

City:

REVERSE(SUBSTRING(REVERSE(ADDRESS),(FINDSTRING(REVERSE(ADDRESS)," ",2) + 1),LEN(ADDRESS) - FINDSTRING(REVERSE(ADDRESS)," ",2)))

State:

TRIM(REVERSE(SUBSTRING(REVERSE(ADDRESS),FINDSTRING(REVERSE(ADDRESS)," ",1),(FINDSTRING(REVERSE(ADDRESS)," ",2) - FINDSTRING(REVERSE(ADDRESS)," ",1)))))

Zip:

REVERSE(SUBSTRING(REVERSE(ADDRESS),1,(FINDSTRING(REVERSE(ADDRESS)," ",1) - 1)))

Here is error that i am getting:-

[Derived Column [47]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "component "Derived Column" (47)" failed because error code 0xC0049064 occurred, and the error row disposition on "output column "city" (60)" 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 "Derived Column" (47) failed with error code 0xC0209029 while processing input "Derived Column Input" (48). 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.

I tried to fix this problem but no luck. Please help me out where i am wrong. I appreciate your help

latch
Yak Posting Veteran

62 Posts

Posted - 2011-02-28 : 17:12:08

Hi,
you can try like these:

zip code: REVERSE(SUBSTRING(REVERSE(loc),1,5))
state : REVERSE(SUBSTRING(REVERSE(loc),7,2))

city: REVERSE(SUBSTRING(REVERSE(loc),9,LEN(loc) - 8))

i am not hard coding anything here its just assumption that always zip is of length 5 and state of length 2 then 5+2+2(spaces)=9
and 9-1 =8 to get the last letter while subtracting from reverse of string.

Thanks,
latch

sql_server_dba
Posting Yak Master

167 Posts

Posted - 2011-02-28 : 19:25:16

Can you briefly describe what you are doing in your SSIS package. Even i got this kind of error a week back and it was nothing related the derived column.

jscot
Posting Yak Master

106 Posts

Posted - 2011-03-03 : 21:08:47

Thanks for your reply, Here is my sample data listed below

ADDRESS
san diego ca 91211
los angles ca 93455

my target field has city,state,zip

That is my requirement.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources