1

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.

M_Y
  • 11
  • 2

1 Answers1

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
JIKEN
  • 337
  • 2
  • 7
  • Thanks for taking your time and answering my question. but I just need a bit of clarification on the step 5. There is no XML source container in the control flow; did you mean XML task container? if you could please let me know how to configure the XML task container as well. cheers, Max – M_Y Sep 07 '19 at 14:18
  • @M_Y - Please check this link. https://www.mssqltips.com/sqlservertip/3141/importing-xml-documents-using-sql-server-integration-services/ – JIKEN Sep 07 '19 at 14:26
  • Thank for sharing it @JIKEN, I think I couldn't explain my question very well. I would like to flatten about 5000 nested XMLs using XSLT operations. I assume in SSIS this (XSLT transformation) can only be done via XML Task Container which I'm not sure how to configure in a Foreach Loop Container. I appreciate your help. – M_Y Sep 07 '19 at 14:41