Converting xml files from a directory (using xslt operation) in ssis and move them into a new directory?

Question

I have bunch of XML files that I wanted to transformed them (using XSLT operations) in SSIS (SQL Server 2014). I have created my xsl and can transform the files one by one to the format that I want. Trouble is I can't work out how to transform and move the files at once.

Up until now, I have tried a Foreach Loop Container, XML TASK and a File System Task to do the job ( off course with no luck). I have created a variable for the filenames.

Have configured the Foreach Loop Container as below:

1. Foreach File Enumerator for the Enumerator in the Enumerator (Collection tab)

2. Set the destination directory

3. Mapped the FileName variable

Configured the XML Task as below:

Source ==> the first file from the source folder

Destination ==> FileName variable

SecondOperandType ==> XSL file

I'm not sure what to do next:

• to read the files from the source directory

• transform the files one by one

• move the transformed files into the destination folder

I would be really thankful if someone please help and point me to the right direction.

Answer 1

This below steps will answer you to loop through all xml files in folders and subfolders using for each loop container.

1. Take Foreach Loop Container from Toolbox
2. Create four different variables. (Take care that their scope must be package and not a container).
   2a. FilePath - DataType String - Value (Set full XML file path like c:\data\filexml.xml)
   2b. FileType - DataType String - Value (*.xml)
   2c. FileFolderPath - DataType String - Value (C:\Data)
   2d. DestFolderPath - DataType String - Value (C:\TransformedXMLFiles)
   
3. Right click on Foreach loop container and click edit
4. Foreach loop editor will be pop up
   4a. Select collection in left pane.
   4b. In right pane, select enumerator as Foreach File Enumerator
   4c. Click on expression eclipse (...) three dot button
   i. Select Directory left side and set variable as @[User::FileFolderPath]
   ii. Select FileSpec left side and set variable as @[User::FileType]
   4d. Select "Fully qualified" in Retrieve File Name section
   4e. Select Traverse Subfolder checkbox if you want to traverse the subfolders.
   4f. In left pane, select Variable Mapping
   4g. Select User::FilePath variable under variable column that will be used to iterate through the files in the Folder and Subfolder specified.
   4h. Put 0 (zero) value in Index column
5. Use your XML source container in for each loop
6. Use XSD or XSLT operations
7. Use File System Task to move file from source to destination