![]() ![]() We’ll next create a new variable to contain the file name the FOR EACH LOOP captures. This example assumes that all files are in a given folder. By selecting VARIABLE MAPPINGS we can create a variable where the FOREACH LOOP CONTAINER will store the file name. If you’ve selected FULLY QUALIFIED like I’ve done below it will grab the location and file name. For this we will need to load the file name and map it to a variable. We’ve told SSIS to look at a folder and file text files. The collection tab will look something like this: Because we’re working with files, select the enumerator as FOREACH FILE ENUMERATOR (should be the default). The collection tab is where most of the fun happens. For this example, highlight the GENERAL tab and name the task LOOP THROUGH FILES. We’ll begin by first naming the task so that any others that come after us can know what the task is doing. By double clicking the task object it will bring up the FOREACH LOOP EDITOR. The first step is drag a FOREACH LOOP CONTAINER to the onto the control flow window. Let’s begin! Creating the FOREACH LOOP CONTAINER At the bottom of the article I’ve included two files to use in this example. By the end of the tutorial, you will know how to loop through files, dynamically determine the file name, create a derived column, and load the data to your database environment. Now, by login into your SQL Server database, create the table using the script at the bottom of the article. Within that folder, create another folder called ARCHIVE. First, let’s create a folder on the root folder of the C drive and call it FILENAMEEXAMPLE. Let’s setup our table and folder structure first. This process will also provide some exposure to the following tasks The process for this is fairly straightforward. There are different schools of thought on backing out data the methodology used below is the one that my business has chosen as a best practice. ![]() Our method for backing out data is to capture the file name during the load process. This is a common practice when working with vendors or when you are working with a mainframe. ![]() The focus of this article is to examine how to back out data when the data is received in the form of a file and loaded to a database. This can either be in the form of database to database data movement or file to database. A data warehouse administrator is often tasked with loading data to the production environment. ![]()
0 Comments
Leave a Reply. |