June 5, 2015 at 12:34 pm
Hello everyone . So I have decided to use SSIS in Management studio 2012 to achieve the task of importing multiple flatfiles and populating 3 Database Tables. One of the files has several delimiters including XML and I am in need of some help using the SSIS feature to parse <messageId>message lksgh;a;g;an;n;oan;grn;</messageID> without effecting the other data that exists into another column in my tables.
I have create the tables:
CREATE TABLE [dbo].[QABS](
[QABSTime] [datetime] NOT NULL,
[OrigServerName] [varchar](50) NOT NULL,
[DestServerName2] [varchar](50) NOT NULL,
[Service] [varchar](50) NOT NULL,
[XMLDoc] [varchar](max) NOT NULL,
[Description] [varchar](max) NOT NULL
);
CREATE TABLE [dbo].[QPLA](
[QPLATime] [datetime] NOT NULL,
[IpAddress] [varchar](50) NOT NULL,
[MessageID] [varchar](max) NOT NULL,
[UserName] [varchar](50) NOT NULL,
[ErrorNumber] [varchar](50) NOT NULL,
[ErrorDesc] [varchar](max) NOT NULL,
[FilePath] [varchar](max) NOT NULL
);
CREATE TABLE [dbo].[Exceptions](
[ExceptionTime] [datetime] NOT NULL,
[origServer] [varchar](50) NOT NULL,
[destServer] [varchar](50) NOT NULL,
[service] [varchar](50) NOT NULL,
[XMLDoc] [varchar](max) NOT NULL,
[errorNumber] [int] NOT NULL,
[errorDesc] [varchar](max) NOT NULL
)
June 5, 2015 at 12:44 pm
June 8, 2015 at 12:44 pm
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???
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply