Home Forums SQL Server 7,2000 SQL Server Newbies SSIS Multiple FlatFile imports with Multiple Delimiters and XML in a txt document RE: SSIS Multiple FlatFile imports with Multiple Delimiters and XML in a txt document

  • I have a file that looks like this:

    2015-04-30 23:34:43:9277,PRODMILQMP-TR,PRODMILQMP-TR$,QM.Perception.Delivery.Service,"Request message XML:

    <messageRequest xmlns='http://questionmark.com/BESTT/'><header><version>1.0</version><messageID>6760f33cd2de5ef4e06e1bd8f4f9f0047f58c5a6e7adc0affff61f8baa57e056</messageID><authentication>pip</authentication><login>0</login><progress>0</progress><customerID></customerID><lang>en-US</lang><settings><logSettings version='853' /><presentationTierSettings version='2912' /><fileUploadSettings version='252' /><browserCheckSettings version='582' /></settings></header><action>StartAssessmentDirect</action><data schema='HTTP'><header id='HTTP_USER_AGENT'><![CDATA[Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 6.1; WOW64; Trident/4.0; QS 4.2.4.0; QS 5.3.0.4; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; .NET4.0C; .NET4.0E; InfoPath.3; QS 4.2.4.0; QS 5.3.0.4)]]></header><header id='LOCAL_ADDR'><![CDATA[10.254.1.61]]></header><header id='SCRIPT_NAME'><![CDATA[/perception5/session.php?CALL=MarineNet.pip&ID=c2c4fb7d-336f-42b8-93c5-7171339dd9c0]]></header><header id='SERVER_NAME'><![CDATA[www.marinenet.usmc.mil]]></header><header id='SERVER_PORT'><![CDATA[80]]></header><header id='REMOTE_ADDR'><![CDATA[138.162.140.54]]></header><header id='REMOTE_HOST'><![CDATA[214.24.211.15]]></header><header id='LOGON_USER'></header><field id='call' order='1'><![CDATA[MarineNet.pip]]></field><field id='id' order='2'><![CDATA[c2c4fb7d-336f-42b8-93c5-7171339dd9c0]]></field><field id='template' order='3'></field></data></messageRequest>"

    QM.Perception.Delivery.Core.ExceptionHandling.QABS.QMMessageCategoryException:Message Error No. 50620 - ParticipantLoginParams instance could not be created because the request contains an invalid value.

    at QM.Perception.Delivery.Service.ParticipantLoginParams..ctor(Request request, String dsn)

    at QM.Perception.Delivery.Service.Messaging.ResponseCommands.StartAssessmentCommandBase..ctor(IQMConfigWrapper qmConfigWrapper, Request request)

    at QM.Perception.Delivery.Service.RequestProcessor.SelectCommand(Request request)

    at QM.Perception.Delivery.Service.RequestProcessor.GetResponse(Request request)

    at QM.Perception.Delivery.Service.AssessmentDeliveryService.GetOrAddMessageFromCache(Request request)

    at QM.Perception.Delivery.Service.AssessmentDeliveryService.Request(XmlNode perceptionMessage)

    QM.Perception.Delivery.Core.ExceptionHandling.QABS.QMMessageCategoryException:Message Error No. 50619 - PIP call processing problem.

    at QM.Perception.Delivery.Service.ParticipantLoginParams..ctor(Request request, String dsn)

    System.ArgumentException:PIP setup problem. NAME has been left undefined.

    at QM.Perception.Delivery.Service.ParticipantLoginParams.SetPropertiesUsingPIP()

    at QM.Perception.Delivery.Service.ParticipantLoginParams..ctor(Request request, String dsn)

    How Do Delimit this. I have "," for the first 3 columns and XML that needs to be separated and inserted into an SQL Database. I also have to create another column to store the XML Tag <messageID> so I can sort on the Message ID in other corresponding file sources. Any Ideas???