December 12, 2011 at 12:24 am
Hi,
I have a record in the row as below
Futurewei Technologies, Inc.=N.A. Region=Usa=Products & Solutions=US Research Center=America Network Division=America Network Optical Dept.
so I want the records to be split as Futurewei Technologies, Inc., N.A. Region,Usa,Products & Solutions,
US Research Center, America Network Division, America Network Optical Dept into 7 different columns.
for that i'am using the following functions in derived transformation-
LTRIM(RTRIM(SUBSTRING([DC_Department1],1,FINDSTRING([DC_Department1],"=",1))))
LTRIM(RTRIM(SUBSTRING([DC_Department1],FINDSTRING([DC_Department1],"=",1),FINDSTRING([DC_Department1],"=",2))))
LTRIM(RTRIM(SUBSTRING(DC_Department,FINDSTRING(DC_Department,"=",2),FINDSTRING(DC_Department,"=",3))))
LTRIM(RTRIM(SUBSTRING(DC_Department,FINDSTRING(DC_Department,"=",3),FINDSTRING(DC_Department,"=",4))))
LTRIM(RTRIM(SUBSTRING(DC_Department,FINDSTRING(DC_Department,"=",4),FINDSTRING(DC_Department,"=",5))))
LTRIM(RTRIM(SUBSTRING(DC_Department,FINDSTRING(DC_Department,"=",5),FINDSTRING(DC_Department,"=",6))))
LTRIM(RTRIM(SUBSTRING(DC_Department,FINDSTRING(DC_Department,"=",6),255)))
But my package is errored out, pls suggest how to go about this.
Thanks,
December 12, 2011 at 8:36 am
First, please post in the appropriate forum. Moving to Integration Services since you mentioned a package.
Second, please include the error. If you have an error, give the text. If you have the wrong results, show them and explain what is wrong.
December 12, 2011 at 10:05 pm
HI,
I have a record like "Futurewei Technologies, Inc.=N.A. Region=Usa=Products & Solutions=US Research Center=America Network Division=America Network Optical Dept".
Now I need to split it up into 7 levels based on "=".
I have given the following expressions in Derived column transformation.
LTRIM(RTRIM(SUBSTRING([DC_Department1],1,FINDSTRING([DC_Department1],"=",1))))
LTRIM(RTRIM(SUBSTRING([DC_Department1],FINDSTRING([DC_Department1],"=",1),FINDSTRING([DC_Department1],"=",2))))
LTRIM(RTRIM(SUBSTRING(DC_Department,FINDSTRING(DC_Department,"=",2),FINDSTRING(DC_Department,"=",3))))
LTRIM(RTRIM(SUBSTRING(DC_Department,FINDSTRING(DC_Department,"=",3),FINDSTRING(DC_Department,"=",4))))
LTRIM(RTRIM(SUBSTRING(DC_Department,FINDSTRING(DC_Department,"=",4),FINDSTRING(DC_Department,"=",5))))
LTRIM(RTRIM(SUBSTRING(DC_Department,FINDSTRING(DC_Department,"=",5),FINDSTRING(DC_Department,"=",6))))
LTRIM(RTRIM(SUBSTRING(DC_Department,FINDSTRING(DC_Department,"=",6),255)))
But I'am getting the following error:
[Derived Column [986]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "component "Derived Column" (986)" failed because error code 0xC0049067 occurred, and the error row disposition on "output column "Department_L1" (1045)" 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.
Please suggest a wat its very urgent
December 12, 2011 at 11:02 pm
Often times in SSIS you will get three or four error messages, but only one of them really sheds any light on the issue. In the progress tab, look at all of the errors you are getting to see if that sheds any light.
HTH,
Rob
December 12, 2011 at 11:15 pm
can anyone tell how can we right the expression for the above scenario in derived column?
errors while running are the following:
1. [Derived Column [986]] Error: An error occurred while evaluating the function.
2. [SSIS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Derived Column" (986) failed with error code 0xC0209029 while processing input "Derived Column Input" (987). 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.
3. [ADO NET Source [425]] Error: The component "ADO NET Source" (425) was unable to process the data. Exception from HRESULT: 0xC0047020
Thanks
December 13, 2011 at 1:08 am
The usual questions:
* are there NULL values in your data?
* do all the rows contain string data of the same structure?
The FINDSTRING function implicetly converts the data to unicode. Could this give a problem with your data? Or is your input already DT_WSTR?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
December 13, 2011 at 1:21 am
My input in DT_NTEXT and I have changed to DT_WSTR.
There are no null data in the column.
Can someone tell how we can do it script component
December 13, 2011 at 1:30 am
Can do what in a script component?
Here's a starting point:
http://msdn.microsoft.com/en-us/library/ms136114.aspx
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
December 13, 2011 at 4:15 am
i'am new to c# script can you tell me how to code my above scenario in the script component
December 13, 2011 at 4:49 am
ptejasree (12/13/2011)
i'am new to c# script can you tell me how to code my above scenario in the script component
Have you read the article that I provided you in my previous post?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
December 13, 2011 at 5:00 am
Yes, I have read but how do i declare in the script to break the text into 7 levels.
Please help me with the c# code.
Thannks
December 13, 2011 at 5:12 am
Substring:
http://msdn.microsoft.com/en-us/library/aka44szs.aspx
IndexOf:
http://msdn.microsoft.com/en-us/library/5xkyx09y.aspx
Trim:
http://msdn.microsoft.com/en-us/library/t97s7bs3.aspx
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
December 13, 2011 at 5:29 am
Hi,
Thanks for the reply. But in my scenario I have to take the string values between "="
and break them into 7 levels.
So for which option I should go, Substringmethod,IdexOf Method or Trim Method
December 13, 2011 at 5:42 am
ptejasree (12/13/2011)
Hi,Thanks for the reply. But in my scenario I have to take the string values between "="
and break them into 7 levels.
So for which option I should go, Substringmethod,IdexOf Method or Trim Method
A combination of all of them, like you did in your derived column?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
December 13, 2011 at 6:03 am
Could you give me an example as I'am new to C#.
I even dont know how to write the code.
Appreciate your thanks very much.
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply