Import XML file with Bulk Insert option in SSIS package?

Question

Is it possible to import data from XML file using Bulk Insert option in SSIS package. Without using Data Flow Task?

Answer 1

The main components of an SSIS package are :

• Control Flow (Stores containers and Tasks)
• Data Flow (Source, Destination, Transformations)
• Event Handler (sending of messages, Emails)
• Package Explorer (Offers a single view for all in package)
• Parameters (User Interaction)

You will need to drag and drop the Bulk Insert task in the Control Flow.

You have two possible options :

Solution 1 : Read the XML file and import the data via the OPENROWSET function. This function is native to T-SQL and allows us to read data from many different file types through the BULK import feature, which allows the import from lots of file types, like XML. Use a ForEach Enumerator and call your existing stored procedure containing the OPENROWSET function

Solution 2 :

You will need 2 variables with string data types: SourceFolder and CurrentFileName.

SourceFolder defines where the files will come from and is used in either approach.

CurrentFileName is used in the ForEach Loop Container to capture the "current" file.