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?

Answer 1

Have you tried using a string datatype?

Answer 2

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.

Answer 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

Answer 4

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

Answer 5

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