One of my previous clients marketed a software created for residential real estate developers. This software product used SQL Server to generate reports for the developer clients and was valued for the traffic and other reports which are used by the developers to improve their marketing efforts among their sales prospects. One report which is created by this software is a statement of Price Per Sq Footage (computed from dividing total sales price total square feet of the model). This value must be created from text files supplied by developer who has purchased this real estate software.

I created this SSIS package by taking advantage of several previous SQLServerCentral articles about using the ForEach Loop Container to import multiple files into an SQL database destination, and to move the imported text files into an Archived folder once the files have been processed. I added a Data Conversion tool to the project, in order to get around the roadblocks which Microsoft places in imported data types that do not conform to Microsoft standards (SSIS Integration piece will automatically convert imported data types to Unicode). Any imported data types which do not conform to the Unicode requirements will cause much frustration when one tries to get the data types to match and accept each other.

The articles I used were:

• Real World Foreach Loop Container example
• Batch ETL of Multiple Data Files Using an SSIS Foreach Loop Container

Here are the steps involved in processing the text files and creating the special derived columns for our real estate software clients.

Building the Package

Open SSDT with administrative rights. Otherwise you may receive a permissions error in running the SSIS package, although package may run and you may receive a package executed with success message.

In SSDT, create an Integration Services Project. The View menu toolbar item will allow you display any of the project units (Server Explorer, Solution Explorer, Object Explorer) which you will need to see in order to complete the project creation.

On the design screen, drag an Execute SQL Task tool onto the Control Flow. This will be the initial task of the project. The tool will create the ODE SQL Server db table, with the appropriate data types assigned. For this report, data needed are the region of the real estate lot, the lot price and lot square feet. This data will be used later in the Derived Column task to create a computed column for Price Per Sq Foot. Since these data are imported daily from the external data source, the table must be truncated for each import run o f the project.

This is the “If Table is not Null, then Drop Table. GO. Create Table” statement in the SQL Execute task:

The next step is to load a ForEachContainer to create the loop process which will import the data text files for processing. Since this is a repetitive process, we must create two variables (one for the File name, which will change; and one for the File Archive location, which will remain constant).

I will abridge the description of the ForEach Loop Container, since I am borrowing from other SQLServerCentral articles for this product. Before the ForEach Loop Container is completed, two variables must be created to define the text files to be imported and the location of the Archive folder to which the text files will be moved once the data have been imported to the ODE data table. Right click on the Control Flow screen, and select Variables to create the two new variables. The variables are “varArchiveFolder” and “varFilePath”. The variable creation screen is given below;

This container needs a file folder assigned as the location for the text file(s) to be imported and processed. Right click on the title bar of the container, then click Edit to bring up the ForEach Editor screen. Enter the location of the text file(s) which contain the data to be imported, and add the wild card *.txt parameter to identify which files to be imported from the appropriate folder (in this case “C:\SSIS\NightlyData):

I use the Data Conversion Editor to ease the import process. SSIS seems to like the eight-byte signed integer data type.

The Expression for the Derived Column Transformation Editor must include a conditional statement that if the price column is Null or 0, the value for the derived column is 0. Otherwise, if the price column is 0, you will receive an error that you cannot divide by zero.

Expression

[Copy of sq__ft] == 0 ? 0 : [Copy of price] / [Copy of sq__ft]

If (“Copy of sq_ft” = 0 or null, set value to 0, else set value as “[Copy of price] / [Copy of sq__ft]”)

The final step is defining the location for the text files, after the importing of the data into the SQL Server Data Table. After the container Collection parameter is set, bring in a file system task inside the ForEach Loop Container into the Control Flow. Define the location of the Archive folder to which the text files will be moved one the data has been imported. This folder is defined using the other variable (varArchiveFolder).

After executing the SSIS project, the text files containing the data are moved to the location of the Archive Folder:

You can see that below:

Results of the SSIS package; below SSMS displays the data that has been imported into the DerivedTable.

Summary

I created this article to demonstrate the use of derived columns inside a ForEachLoop Container for a client requirement for some auxiliary marketing reports. I found the Data Conversion tool useful to escape the problem of mismatched data types, which is an issue with versions of Microsoft SSIS. The SSIS process seems to attempt to convert all column values to be compatible with Unicode during the import process. Several SQLServerCentral articles about the importing of multiple text files using the ForEach Loop Containers were especially useful. The above combination of tools (Derived Column and Data Conversion) within the container provided a helpful solution to a specific client requirement.

I make it a practice to open SSIS and SSDT as administrator to avoid annoying permission errors. This error seems to appear most frequently during the Data Flow process.