
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:
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
EmoticonEmoticon