I have a folder with multiple XML files. I need to bulk insert each one into a table in sql server. I am at a complete loss as to how to get this to work, as I am new to SSIS.
Currently, My SSIS package pulls the files off an FTP server and uses a command line to unzip the xml (the come as .xml.gz). This all works great, but now I'm at a loss as to get the files into the database, as the bulk insert task only takes delimited files.
Suggestions?
You can accomplish this by using a ForEach Loop Container with an enumerator type of file. If the XML files are complex, you can use an XML Task. Otherwise, use a Data Flow with an XML Source.
If you choose to do so, the following article explains how to use an XML Source.
There is a step to the process that isn't apparent. When you set up your For-Each, and assign the file to a variable (IE: User::InputFile), if you try to use that in the XML Source (XML file from variable), it will give you an error saying it can't read the variable.
And it can't. You have to prime the pump by manually entering an example (working) file address in the variables tab to the variable that will be providing the address (IE: User::Inputfile).
Do this and it will let you progress to the columns tab, where you can finish utilizing the for-each loop.