Next you need to Drag the SQL Server Destination Data Flow task from the toolbox and rename it to Load XML Data to SQL Server. Below are the following steps for the remainder of the configuration
1) You need to connect the green arrow from the XML Source Data File to the Load XML Data to SQL Server task
2) Double click the Load XML Data to SQL Server task to open up the SQL Destination Editor and click the New… button which will pop up the Configure OLE DB Connection Manager.
3) For a valid connection to the SQL Server Destination database one needs to click on the New… button which will once again pop up the Connection Manager where the Provider needs to be selected as Native OLE DBSQL Server Native Client 10.0, SQL Server name, Authentication Mode and finally the destination database name. Once added all the details click OK to save the changes.
4) The table name needs to be provided where the data imported from the XML file will be stored in the destination database. You then need to click the Mappings to check whether the elements in the XML document are mapped correctly to the table column in SQL Server Table. Click OK to save the settings. The final screen will look as shown below:
5) The package screen in BIDS will look as shown in the following image:
6) Select the SSIS package named “LoadXML.dtsx” if renamed otherwise by default BIDS names it as “package.dtsx”. Right click “LoadXML.dtsx” package which will popup a window where you need to select the Execute Package option, this will run the SSIS package.
