XML Process TASK

Xml Task:-The XML task is used when you need to validate, modify, extract or even create files in an xml format
Let’s create Package which will validate xml against xsd.
1)First we will two file one XML file which will validate another is XSD which will use to validate document

a) XML File content

b) XSD file content


2) Open BIDS(Business intelligence development studio)
3) Drag XML Task from tool box to Control Flow tab as shown below screen shot

4) Double click on XML Task XML Task editor window will be open

5) On the right there is property called “Operation Type” which define what type of operation will be perform by XML TASK. below is description of operation type values
a) Validate:-Validate and XML against XSD
b) XSLT :- Transform data into presentation format
c) XPath:-This option XML path language allows the extraction of
section or specific nodes from the structure of XML
document
d) Merge :- This option allow to merge two xml document with the
same structure
e) Diff:- This option use to identify the difference between two XML
Document and base on that produce the third document
called XML Diffgram that contain the difference between
the two
f) Patch:-This option applies the results of a diff operation to an xml
document to create a new xml document
6) Choose “Validate” option , There are three option in SourceType
a) FileConnection:-Xml file connection name
b) Direct Input:- Specify direct xml content
c) Variable:-Specify variable name which contain file path
We will use “File connection” option but before that we will create File
connection right click on “Connection Manager” select “New File Connection “ as mentioned below


File Connection manager editor will be open, Specify XML document path which need to validate



Same create for “XSD” (which will use to validate XML document)and “OutputFile”(For storing validation output) connection


7)Specify XML document content Connection value in source type property



8) Specify OutPut connection values in Destination property

9) Specify XSD connection values in “Second Operand” property

Set “ValidationType” property value “XSD” above screen shot because we are validating document against XSD
10) Now all property set we will execute package using F5” validation will be
Success


11) Change the sequence child element from XML document mentioned above

12) Run the Package


13) Open Output.xml file which will contain “false” value

14) If you will go to “Execution Result” panel we can see validation error




Let find Difference between to XML files
1) First we Need to create two XML files
a) XMLOnefile

b) XMLTwofile

If you will see above mentioned I have just changed sequence of child elements.
2) Set “Operation Type” property value “Diff”

3) Set three file connection as set above
a) One is for Source
b) Second is for Destination
c) Third for OutPut.xml

4) Run Package suing F5 button

5) Open Output.xml file you will get false mean xml is not proper

6) Change sequence then run you will get “true” in output.xml file

File System Task

I am new in SSIS although I have good experience with DTS. In the below article I am explaining how to move the file from location to another location using SSIS.

1) We will create to two location one is Source and another is destination with full right

2) Open BIDS.


3) Right click on “Control Flow” click on “Variables’” option

4) Variables windows will be open add below variable using “” add (below screen shot)

a) Destination:-Specify the destination path

b) FileExtension:- Specify the extension of file which move

c) FileName:- This variable value will be filled by “ForEachLoop” Container

d) SourcePath:- Specify the source path

e) SourcePathWithFile:- This variable store concatenation of Source path and FileName

5) Add “foreach loop container” into Control Flow window double click on it “ForeachLoop” editor will be open


a) Select “Collection” option select Enumerator type “Foreach File Enumerator
b) Expand “Expression” option on “Collection” window click on button “Property
Expression Builder” will be open



c) Select “Directory” from property drawdown list Click on “Expression” button
“Expression builder” window will be open specify “sourcePath” variable as mentioned below
screen



d) Click on ok button expression values will be shown on “Property Expression editor”. Same action perform for Name


E) Click “Variable mappings” option on “ForEachLoop” Editor. select FileName variable from variable dropdown list click ok

F) We will use “SourcePathWithFile” as expression variable for that select “SourcePathWithFile” Variable
press F4 button change below property

a) EvaluateAsExpression:- set it true

b) Expression:- create a expression which will concatenate SourcePath and FileName(Shown below screen)

c) Namesapce :-It is optional I have change value from User to UserExp so I can differentiate it from other variables

ForeachLoop task configuration completed

6) Drag “File System Task” from toolbox inside “ForEachloop” container

7) Double click on “File System Task” the editor will be open where we will configure below

properties

a) IsDestinationPathVariable: Set is true

b) DestinationVariable:- Select Destination variable

c) Operation:- Select “Move File”

d) IsSourcePathVariable:-Set is true

e) SourceVariable:- Select Source variable

Click ok

8) Go to Source folder create some file which will be moved

9) Go BIDS and run package using F5

10) File will be moved to destination folder and source folder will be empty