Create a XML Schema Collection in your database.
CREATE XML SCHEMA COLLECTION [dbo].[MyXmlFileSchemaCollection] AS
N'<xs:schema attributeFormDefault=”unqualified” elementFormDefault=”qualified” xmlns:xs=”http://www.w3.org/2001/XMLSchema”>
Define project parameters in your project for the source XML and XSD file location.
In your SSIS package, add a connection to the database where you created the schema collection from step 1.
Add a dataflow task to your package.
Choose an OLE DB source connection, pointing to your database, with Data access mode set to ‘SQL command’.
SELECT CAST(xml_schema_namespace(N’dbo’,N’MyXmlFileSchemaCollection’) AS nvarchar(max)) AS CatXsd
Your destination will be a Flat File with one column of type DT_NText.
Run the package in order to generate your XSD file in the location you specified in the project parameters.
Add the dataflow that will read your XML file.
Add a XML Source and point the XML and XSD location to the corresponding files. (we will make this dynamic later)
Click on the Columns section. This will generate all of your outputs from the XML file.
Add all the destinations to match your desired outputs.
- [XML Source].[XMLData]
- [XML Source].[XMLSchemaDefinition]
Ensure to ‘evaluate expression’ to validate your paths are correct.
The second thing you need to do on the import task properties is to set the DelayValidation to true. Since you might be deploying this package to another server and running it for the first time, the xsd file being created in the first task might not be there.
Set the same expressions from above to the two flat file connections as well, on the ConnectionString property.
Now you can convert your connections to a ‘Project Connection’ (shared data source, in pre-SQL2012 world) by right-clicking on each and selecting ‘Convert to project connection’. This is helpful because you only need to manage one set of connection in your deployment. In SQL 2012, ‘they’ finally fixed the issue around using shared connections in a SQL Server deployment by introducing the Project Deployment Model, which is reeeally useful. Thanks Microsoft!
Run the package to ensure everything is copasetic. Now we’re ready to deploy to the Integration Services Catalogs on the server, which is also new in SQL 2012. There are a bunch of blog posts on this, so I’m not going to reinvent the wheel. 🙂 Here are some really good articles on that: