You may be wondering, why generate so many files? Well, let’s look at the following scenario. AdventureWorks’ Marketing department just completed modifying the Descriptions for their products and want to send out new brochures to their retailers. The printer does not have a SQL Server database and needs each of the descriptions in a separate file so they can create a brochure for each product. Your job is to generate each file from the CatalogDescription column in the Production.ProductModel table in AdventureWorks2012.
To summarize what we will be doing. First, we will need to create a recordset with the data from the table, then loop through each record and create a file and save it to a specified folder on a drive.
*Please note, you will need to have a basic understanding of creating an SSIS Package, connection managers and variables within the package.
First, create a new SSIS package in SQL Server 2012 Data Tools (BIDS) and setup the following Connection Managers and variables.
Flat File Connection Manager
Since we want each record to be an XML file, we will need to configure the connection to generate a filename at runtime. To do this, go to the properties for the Connection Manager and set up an Expression for the Connection String. In the image below, I am using the ProductModelID (PK in the Production.ProductModel table) to build a unique file name for the output with the expression.
SSIS Package Variables
We need the following variables in the package.
ProductModelID – Int32 with a default Value of 1, this will hold the ProductID of each record
xmlCatalog – String – this will hold the Catalog Description of each record
ixmlOutput – Object – this will hold the recordset we will be creating in step 2.
Now we can create the recordset. Here is the Query I am using. I am taking the XML data and using the cast function to change it to a varchar.
,cast( [CatalogDescription] as varchar(8000)) as CatalogDesc
where CatalogDescription is not null
Use the SQL Task Editor under the Control Flow components to create the recordset. For the Result Set select the “Full Result Set” option (shown below). This will hold all the records from our query. Select the Connection Manager you created earlier under the Connection option.
Under SQL Statement paste in the query above
Next I set up the SQL task to send the results of the query to the object variable, xmlOutput, we created in the beginning. On the left side click on the Result Set Item. Then click on the Add button on the bottom left of the window. Enter a 0 (zero) for the Result Name and Select the Object variable you created at the beginning. Click on OK once everything has been configured.
In order to create a file for each record in the recordset, we will need to setup the Foreach Loop Container in the Control Flow tab.
Configure the Foreach Loop Editor as illustrated below. For the enumerator, select the Foreach ADO Enumerator. Under the ADO object source variable select the object variable, xmlOutput. Under the Enumeration Mode Select the Rows in the first table
Go to the Variable Mappings section of the editor by clicking on the item in the left pane of the editor.. Since our recordset returns 2 columns, we will need to put each record of each of the two columns in a variable so that we can write the contents out to a file.
In this window, select each of the variables and start the index with a 0 (zero). Once everything is configured click on OK.
Now we are ready to setup a Data Flow task to write the XML data to a file. Drop the Data Flow Task into the Foreach Loop Container box and double Click on it to open the Data Flow tab.
In the Data Flow Task, we will need to setup a source and destination. In this case we will be using the Script Component for the source (Circled in Red). This component will read the data in the recordset we created earlier. Since we are calling the data flow task inside a Foreach Loop Container, it is reading one record at a time.
The output is a Flat File Destination (Circled in Blue).
To configure the Script Component task, drag the Script Component tool from the toolbox. A window will pop up asking to specify the type. In this case select the Source option. Then click on OK.
Once the Script Component appears on the screen, double click on the task to get to the editor. If you have used the Script Component under the Control Flow tab, this may look familiar to you. You will need to specify the ReadOnly and/or ReadWriteVariables along with the ScriptLanguage. In this example, I am using Microsoft Visual Basic 2010.
Here is where it is a little different. You need to specify the outputs for the Editor. Click on the Inputs and Outputs option in the left pane.
Under Output Columns click on the Add Column button and type in a name for the output. In this case I am creating a column called CatalogResult. Make sure to select the DataType on the right hand side. Since this is essentially XML data, I selected the text stream data type.
Go back to the Script section and click on the Edit Script button. Enter the following code under the CreateNewOutputRows() sub. This code will only retrieve the records with Catalog Descriptions. It will then get the data from the variable, xmlCatalog. Once it has the data, it then assigns the record to the output column we created in the script component editor.
Dim strCatalog As String
If Me.Variables.xmlCatalog.ToString() <> "NoCatalogDesc" Then
strCatalog = Me.Variables.xmlCatalog.ToString
** Please note you will need to add the Imports System.XML and System.Text to the Imports section.
Setup the Flat File Destination for the data. Drag the Flat File Destination object to the Data Flow Task and double click on it. Select the Flat File Connection Manager created earlier. Click on the Mappings option in the left pane. Map the output column from the Script Component to the Column created in the Flat File Connection Manager. Click on OK.
Once this is complete, you are ready to save and execute the package. When exectution is complete, you can see that it created a separate XML file for each ProductModelID, as shown below.
Here is what the XML file looks like when you open it.
The steps outlined above can be used to export data out to other types of flat files, such as a csv file. Just make sure to setup your recordset to pull the data you need. The files are now ready to be transmitted to the retailers.
For the complete description of the Script Component, you can go to MSDN at http://msdn.microsoft.com/en-us/library/ms136060(v=sql.110).aspx