Monday, 6 August 2012

Case
I have an XML file which I want to validate against an XSD file before processing it. However my XSD contains an include (or import) tag and the XML Task will not validate it and throws an error about certain parts that are not declared.

How can I validate an XML file against an XSD with an include (or import) tag?

1) New File Connection
I will use Connection Managers in this example to make things easier to configure. Feel free to use something else such as variables. Right click in the Connection Managers pane and select "New File Connection...".

New File Connection for XML and XSD file

Select the xml file in the File Connection Manager Editor and repeat these steps for the XSD file (movies2.xsd) so that you have two Connection Managers named movies.xml and movies2.xsd. You don't have to create a Connection Manager for the include xsd, because it's included by code.

File Connection Manager Editor

2) Script Task
Add an Script Task to your Control Flow and give it a suitable name. You can connect it to your Data Flow Task that processes the XML file.

Script Task to validate XML

3) The script
Edit the Script Task and choose your Script Language, hit the edit button to edit the script and copy the code from the main method below to your main method and add the import/include row.

Sunday, 5 August 2012

Case
I have an XML file which I want to validate before processing it. How can I validate an XML file against an XSD file with SSIS?

Solution
There are two solutions in SSIS for validating an XML file.A) XML Task, but the big downside is that it can't validate against XSD files with import or include tags. See(/vote for) this Microsoft Connect feedback.B) Script Task, but that requires some .Net knowledge.

In this post I will elaborate the XML Task solution and in the next post I will workout the Script Task solution.

1) New File Connection
I will use Connection Managers in this example to make things easier to configure. Feel free to use something else such as variables. Right click in the Connection Managers pane and select "New File Connection...".

New File Connection for XML and XSD file

Select the xml file in the File Connection Manager Editor and repeat these steps for the XSD file so that you have two Connection Managers named movies.xml and movies.xsd

File Connection Manager Editor

2) XML Task
Add an XML Task to your Control Flow and give it a suitable name. You can connect it to your Data Flow Task that processes the XML file.

XML Task

3) XML Task editor
Edit the XML Task and select the XML Connection Manager as input and the XSD Connection Manager as Second Operand. OperationType should be Validate and ValidationType should be XSD.

Set XML Task editor for validation

4) Testing
Run the package. Change something in the XML file to make it fail (a movie title longer than 50 chars).

Friday, 3 August 2012

Case
When changing a column in a table I get this message preventing me to save the changes:

Saving changes is not permitted. The changes you have made require the
following tables to be dropped and recreated. You have either made
changes to a table that can't be re-created or enabled the option
Prevent saving changes that require the table to be re-created.

This question got nothing to do with SSIS self, but editing tables is a common task for SSIS developers. And I'm always browsing a couple of minutes to find the right option to disable.

Wednesday, 1 August 2012

Case
I want log all my connection managers, delete all my excel files, check whether my flat files exists. And I don't want to add task for all of them.

Solution
You can use a Script Task to loop through the collection of Connection Managers and do something with the connection manager (log, delete, check, etc.). Either do the task in .Net code or fill a SSIS object variable which can be used in Foreach From Variable Enumerator. I will show you both.

Note: these solutions don't work for dynamic Connection Managers. For example with a foreach loop and expressions on the connectionstring.

A) Script Task only solution
Add a Script Task to the Control Flow and edit it. Copy the code of this main method to your main method. There are a couple of examples so adjust it to your own needs.