June 23, 2015 at 8:17 am
Hi everyone I am trying to figure out how to parse a specific string od fata out of a column in ssis into another new column with findstring and substring.
FINDSTRING(XMLMessage,"<dateTime>",1) != 0 ? (SUBSTRING(XMLMessage,1,FINDSTRING(XMLMessage,"<dateTime>",10) - 19)) : XMLMessage
I need to search through the string in the column XMLMessage and find"<datteTime>" start at 10 characters and take 19 from the column inbto a new column. I need some syntax help with this ssis column import
June 23, 2015 at 8:19 am
Here is a sample of the information in that column as a blob of data.
<?xml version="1.0" encoding="utf-8"?><soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"><soap:Body><RequestResponse xmlns="http://questionmark.com/BESTT/"><RequestResult><messageResponse xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns=""><header><version>1.0</version><messageID>09823ab8d085e0ac0301690ef750334854cd1476ec3b94454004b565d2f1f9df</messageID><login>0</login><progress>0</progress><customerID>NGQMRepository</customerID><lang>en-US</lang><licenseText>Questionmark Perception licensed to USMC</licenseText><authentication>Unknown</authentication><entryPoint /><settings /><dateTime>2015-04-30T20:39:27.6669106-04:00</dateTime><branched>false</branched></header><action>Error</action><data xsi:type="ErrorResponseData"><errorCategory>Security</errorCategory><errorCode>50224</errorCode><errorMessage>Access denied. Missing NAME parameter.</errorMessage></data></messageResponse></RequestResult></RequestResponse></soap:Body></soap:Envelope>
June 23, 2015 at 9:06 am
Try this:
SUBSTRING(xmlMessage,FINDSTRING(xmlMessage,"<dateTime>",1) + LEN("<dateTime>"),FINDSTRING(xmlMessage,"</dateTime",1) - (FINDSTRING(xmlMessage,"<dateTime>",1) + LEN("<dateTime>")))
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
June 23, 2015 at 9:10 am
Doesn't like it. Says the syntax is incorrect
June 23, 2015 at 9:17 am
When I pasted in the expression it replaced the < and > so you need to replace "& lt" and "& gt" with < and >.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
June 23, 2015 at 9:32 am
Just this weekend I had to develop a step that pulled a particular value from an XML statement using the XML query tools. It worked great. I won't be able to provide a sample until this evening though. I've added this as a place holder so that I can find it this evening.
June 23, 2015 at 9:33 am
TITLE: Microsoft Visual Studio
------------------------------
Error at Data Flow Task [Derived Column 1 [45]]: Attempt to find the input column named "xmlMessage" failed with error code 0xC0010009. The input column specified was not found in the input column collection.
Error at Data Flow Task [Derived Column 1 [45]]: Attempt to parse the expression "SUBSTRING(xmlMessage,FINDSTRING(xmlMessage," < and > ;dateTime < and > ;",1) + LEN(" < and > ;dateTime < and > ;"),FINDSTRING(xmlMessage," < and > ;/dateTime",1) - (FINDSTRING(xmlMessage," < and > ;dateTime < and > ;",1) + LEN(" < and > ;dateTime < and > ;")))" failed and returned error code 0xC00470A2. The expression cannot be parsed. It might contain invalid elements or it might not be well-formed. There may also be an out-of-memory error.
Error at Data Flow Task [Derived Column 1 [45]]: Cannot parse the expression "SUBSTRING(xmlMessage,FINDSTRING(xmlMessage," < and > ;dateTime < and > ;",1) + LEN(" < and > ;dateTime < and > ;"),FINDSTRING(xmlMessage," < and > ;/dateTime",1) - (FINDSTRING(xmlMessage," < and > ;dateTime < and > ;",1) + LEN(" < and > ;dateTime < and > ;")))". The expression was not valid, or there is an out-of-memory error.
Error at Data Flow Task [Derived Column 1 [45]]: The expression "SUBSTRING(xmlMessage,FINDSTRING(xmlMessage," < and > ;dateTime < and > ;",1) + LEN(" < and > ;dateTime < and > ;"),FINDSTRING(xmlMessage," < and > ;/dateTime",1) - (FINDSTRING(xmlMessage," < and > ;dateTime < and > ;",1) + LEN(" < and > ;dateTime < and > ;")))" on "Derived Column 1.Outputs[Derived Column Output].Columns[Derived Column 1]" is not valid.
Error at Data Flow Task [Derived Column 1 [45]]: Failed to set property "Expression" on "Derived Column 1.Outputs[Derived Column Output].Columns[Derived Column 1]".
------------------------------
ADDITIONAL INFORMATION:
Exception from HRESULT: 0xC0204006 (Microsoft.SqlServer.DTSPipelineWrap)
------------------------------
BUTTONS:
OK
------------------------------
June 23, 2015 at 9:37 am
Thank you for your assistance. I look forward to your response. SSIS isn't difficult its just got a lot of features and a lot of functionality. Some of the syntax is confusing to me. Like findstring is actually a reverse charindex. I am still learning but this is definitely a road block I want to overcome. Thanks for your help in advance.
June 23, 2015 at 9:42 am
dunn_Stephen (6/23/2015)
TITLE: Microsoft Visual Studio------------------------------
Error at Data Flow Task [Derived Column 1 [45]]: Attempt to find the input column named "xmlMessage" failed with error code 0xC0010009. The input column specified was not found in the input column collection.
Error at Data Flow Task [Derived Column 1 [45]]: Attempt to parse the expression "SUBSTRING(xmlMessage,FINDSTRING(xmlMessage," < and > ;dateTime < and > ;",1) + LEN(" < and > ;dateTime < and > ;"),FINDSTRING(xmlMessage," < and > ;/dateTime",1) - (FINDSTRING(xmlMessage," < and > ;dateTime < and > ;",1) + LEN(" < and > ;dateTime < and > ;")))" failed and returned error code 0xC00470A2. The expression cannot be parsed. It might contain invalid elements or it might not be well-formed. There may also be an out-of-memory error.
Error at Data Flow Task [Derived Column 1 [45]]: Cannot parse the expression "SUBSTRING(xmlMessage,FINDSTRING(xmlMessage," < and > ;dateTime < and > ;",1) + LEN(" < and > ;dateTime < and > ;"),FINDSTRING(xmlMessage," < and > ;/dateTime",1) - (FINDSTRING(xmlMessage," < and > ;dateTime < and > ;",1) + LEN(" < and > ;dateTime < and > ;")))". The expression was not valid, or there is an out-of-memory error.
Error at Data Flow Task [Derived Column 1 [45]]: The expression "SUBSTRING(xmlMessage,FINDSTRING(xmlMessage," < and > ;dateTime < and > ;",1) + LEN(" < and > ;dateTime < and > ;"),FINDSTRING(xmlMessage," < and > ;/dateTime",1) - (FINDSTRING(xmlMessage," < and > ;dateTime < and > ;",1) + LEN(" < and > ;dateTime < and > ;")))" on "Derived Column 1.Outputs[Derived Column Output].Columns[Derived Column 1]" is not valid.
Error at Data Flow Task [Derived Column 1 [45]]: Failed to set property "Expression" on "Derived Column 1.Outputs[Derived Column Output].Columns[Derived Column 1]".
------------------------------
ADDITIONAL INFORMATION:
Exception from HRESULT: 0xC0204006 (Microsoft.SqlServer.DTSPipelineWrap)
------------------------------
BUTTONS:
OK
------------------------------
SSIS Expression language is case sensitive and I types the column name as xmlMessage. Your column name is XMLMessage. If you fix that it will probably work as it will now find the column and should work.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
June 23, 2015 at 9:52 am
Error: 0xC0049067 at Data Flow Task, Derived Column 2 [12]: An error occurred while evaluating the function.
Error: 0xC0209029 at Data Flow Task, Derived Column 2 [12]: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "Derived Column 2" failed because error code 0xC0049067 occurred, and the error row disposition on "Derived Column 2.Outputs[Derived Column Output].Columns[TimeStamp]" 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 Data Flow Task, SSIS.Pipeline: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Derived Column 2" (12) failed with error code 0xC0209029 while processing input "Derived Column Input" (13). 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.
Information: 0x40043008 at Data Flow Task, SSIS.Pipeline: Post Execute phase is beginning.
Information: 0x4004300B at Data Flow Task, SSIS.Pipeline: "To_QPLA_Exceptions" wrote 0 rows.
Information: 0x40043009 at Data Flow Task, SSIS.Pipeline: Cleanup phase is beginning.
Task failed: Data Flow Task
Warning: 0x80019002 at Package1: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (3) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
August 6, 2015 at 9:27 am
Sorry its been awhile. this is how I solved my problem. I used SUBSTRING AND FINDSTRING with a logical operator to search for the opening and closing tags of the XMLMessage I wanted to select information from and then used the Opening FINDSTRING syntax to pull out the data I wanted to populate my new column with. Here is my example for those who are having similar issues with learning SSIS.
SUBSTRING(XMLMessage,FINDSTRING(XMLMessage,"<dateTime>",1) + 10,FINDSTRING(XMLMessage,"</dateTime>",1) - FINDSTRING(XMLMessage,"<dateTime>",1))
SUBSTRING(XMLMessage,FINDSTRING(XMLMessage,"<messageID>",1) + 11,FINDSTRING(XMLMessage,"</messageID>",1) - FINDSTRING(XMLMessage,"<messageID>",1))
SUBSTRING(XMLMessage,FINDSTRING(XMLMessage,"<authentication>",1) + 16,FINDSTRING(XMLMessage,"</authentication>",1) - FINDSTRING(XMLMessage,"<authentication>",1))
SUBSTRING(XMLMessage,FINDSTRING(XMLMessage,"<login>",1) + 7,FINDSTRING(XMLMessage,"</login>",1) - FINDSTRING(XMLMessage,"<login>",1))
SUBSTRING(XMLMessage,FINDSTRING(XMLMessage,"<progress>",1) + 10,FINDSTRING(XMLMessage,"</progress>",1) - FINDSTRING(XMLMessage,"<progress>",1))
SUBSTRING(XMLMessage,FINDSTRING(XMLMessage,"<errorCategory>",1) + 15,FINDSTRING(XMLMessage,"</errorCategory>",1) - FINDSTRING(XMLMessage,"<errorCategory>",1))
SUBSTRING(XMLMessage,FINDSTRING(XMLMessage,"<errorCode>",1) + 11,FINDSTRING(XMLMessage,"</errorCode>",1) - FINDSTRING(XMLMessage,"<errorCode>",1))
SUBSTRING(XMLMessage,FINDSTRING(XMLMessage,"<errorMessage>",1) + 14,FINDSTRING(XMLMessage,"</errorMessage>",1) - FINDSTRING(XMLMessage,"<errorMessage>",1))
August 6, 2015 at 10:00 am
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply