Tutorial :SSIS 2008 - import xml file contents into sql server

Question:

I want to take a series of xml files and pull the xml of the file into a table in the database. I have a for each file enumerator, then an xml task to pull out the dtd and put the contents in a variable. Now that I have the file name and the contents in a variable, I need to insert both pieces of data into the database.

My table to store the data looks like this:

create table Import_Files  (      SequenceId              int             IDENTITY(1,1) NOT NULL,      FileName                varchar(200)    NOT NULL,      FileXml                 xml             NOT NULL,      Created                 datetime        DEFAULT(GETDATE()) NOT NULL,      Processed               bit             DEFAULT(0) NOT NULL  )

My Stored procedure:

CREATE PROCEDURE [dbo].[AddFile]       @FileName   varchar(200),      @FileXml    xml  AS  BEGIN      -- SET NOCOUNT ON added to prevent extra result sets from      -- interfering with SELECT statements.      SET NOCOUNT ON;        --Add new record      INSERT INTO Import_Files      ([FileName], FileXml)      VALUES      (@FileName, @FileXml)    END

I can't get it to work because hte xml data type isn't available in my execute sql task. Any ideas on how to make this work?

Solution:1

I would look at the import column transformation. It allows you to import the contents of a file for each row in a dataflow. The data source would simply be a listing of each file and any other column level meta data you need.

This would probably be more performant then doing a row by row insert from a spro

More info on setting this up here:

http://msdn.microsoft.com/en-us/library/ms141262.aspx

Solution:2

Have you tried using a string datatype?

Solution:3

As sugested earlier change the proc to accept a string datatype varchar(max) and do a convert to xml in the proc if you must store it in an xml column

Solution:4

Instead of using an Execute SQL Task, I would use an XML Source - generate all the fields you wanted into columns and then just pass those into the database table using an OLE DB Destination. This way you don't have to even use your stored procedure since SSIS already would do these inserts for you and it would recognize your xml type.

Solution:5

I had to use a Script task and call the sproc through code.

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com