SSIS package to execute a stored procedure for each xml document is a specific directory

I have a table with a column type of xml. I also have a directory that can have 0 to n number of xml documents. For each xml document, i need to insert a new row in the table and throw the xml in开发者_如何学运维to the xml column.

To fit with our clients needs, I need to perform this operation using an SSIS package. I plan to use a Stored Procedure to insert the xml, passing in the file path.

I've created the stored procedure and tested, it functions as expected.

My question is, how do I execute the stored procedure from an SSIS package for each xml document is a specific directory?

Thanks in advance for any help. -

Basically you just need to loop through the files and get the full file paths to pass to the stored proc. This can be done easily using a For Each Loop and the ForEach File Enumerator. This page has a good description of how to set that up:

http://www.sqlis.com/post/Looping-over-files-with-the-Foreach-Loop.aspx

Within the loop then you just access the variable that is populated each time the loop executes (an XML file is found) and send it as a parameter into an Execute SQL Task (residing inside your For Eacu Loop container) to call your stored procedure. Here is an example of passing variables as parameters:

http://geekswithblogs.net/stun/archive/2009/03/05/mapping-stored-procedure-parameters-in-ssis-ole-db-source-editor.aspx

You don't need to use a stored procedure for this. You can do all of this within an SSIS package. Here's how:

Have a For-Each Loop task read all available files in the folder. Put the full path of the file into a variable called XMLFileName

Inside the For-Each loop, use a Data-Flow task read the contents.

The OLE_SRC is reading from the same SQL Server and it's statement is SELECT GetDate() as CurrentDateTime

The DerivedColumn component creates a column called XMLFilePath with the full path of the XML file

The ImportColumn component is the one that does the magic. It will take the XMLFilePath as an input column, give it the LineageId of a new output column you create and it will import the full XML for you. Read more on how to set it up here:

http://www.bimonkey.com/2009/09/the-import-column-transformation/

Use the OleDB Destination to write to the table.