Using SSIS to Pull Oracle data into an XML Field in SQL 2008 R2

• Brandon Forest

  SSCommitted

  Points: 1977

  More actions

  July 1, 2015 at 10:47 am

  #318647

  We have a custom web C#/SQL2K8R2 workflow application that I need to pull Oracle data into a varchar(max) field as an XML DOM document. I have no problem pulling the Oracle data using OLEDB, but I'm not sure how to create the XML DOM doc. Once I get it into the DOM doc, I then need to assign metadata about the XML DOM doc and insert it all into a staging table:

  CREATE TABLE [stg].[EtlImports](

  [EtlImportId] [int] IDENTITY(1,1) NOT NULL,

  [EtlSource] [varchar](50) NOT NULL,

  [EtlType] [varchar](50) NULL,

  [EtlDefn] [varchar](max) NULL, --Either a SQL statement or path to file on disk

  [EtlData] [varchar](max) NULL, --BLOB field to hold the XML data or FILESTREAM path to file on disk

  [EtlDateLanded] [datetime] NOT NULL,

  [EtlDateProcessed] [datetime] NULL,

  [EtlStatus] [varchar](50) NULL,

  [Comments] [varchar](4000) NULL

  ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

  I will have a separate SSIS package to pull the [XML/File] field and process the data into the workflow tables.

  My question is: Is there a wasy I can use the ADO Recordset Destination task to accomplish this, or do I have to create a custom C# script to create the XML DOM Doc?

  I'm open to any method at this point. 😉

  Cheers!

  Brandon Forest

  Senior Business Information Analyst

  Blue Shield of California