SSIS - How To Load Multiple Files ( .txt or .csv ) To a Table With File Names

 

SSIS - How To Load Multiple Files ( .txt or .csv ) To a Table With File Names

Scenario: 

We receive sale files for different regions ( Asia, Europe, North America) in our Source Folder. We need to load these files to RegionSale Table with File Name and Folder Path. In this example the files are .txt but they can be .csv.

Here is Sample data for each of the file that I am using 
Asia_Sale_20131224.txt
RegionCd,SalePersonName,SaleAmt
AS,Aamir,100
AS,Raza,200
AS,Sukhjeet,300

Europe_Sale_20131224.txt
RegionCd,SalePersonName,SaleAmt
EU,Mike,1000
EU,Robert,1500

NorthAmerica_Sale_20131224.txt
RegionCd,SalePersonName,SaleAmt
NA,Jimmy,700
NA,Lisa,200

Solution : 

We will be using Foreach Loop Container to loop through these files and then use Derived column to generate a new column for File Name with Folder Path. Let's start with step by step approach

Destination Table DDL 
Create table dbo.RegionalSale
 (RegionSaleID int identity(1,1),
 RegionCd Char(2) ,
SalePersonName Varchar(50) ,
SaleAmt INT,
FileQualifiedPath VARCHAR(100))

Step 1:
Create new SSIS Package. Create variable VarSourceFolder and set the value of this variable to your folder in which files exist. I created the variable so if we have to change the Source Folder path , we can do it by using SSIS Configuration for this variable.


Step 2: 
Bring Foreach Loop Container to Control Flow Pane and Set Directory=VarSourceFolder as shown below. As we are going to read only .txt files. Provide *.txt under Files text box.

Change *.* to *.txt as shown in #2 to only read .txt files. Also choose Full qualified to get complete path of file on each iteration.

Go to Variable Mapping and create a new variable VarFilePath that will hold fully qualified path on each iteration.

Step 3:
Bring Data Flow Task inside Foreach Loop Container and open it by double clicking. Inside Data Flow Task,Drag Flat File Source and create connection string by using any file from Source Folder.

Click on Columns to make sure everything working as expected. You can preview some data by clicking on Preview.

Step 4:
Drag Derived column Transformation and connect Flat File Source to it. Generate a new column by using the VarFilePath as shown below

Expression :(DT_STR,100,1252)@[User::VarFilePath]
I have converted the value of variable to Varchar(100) by using Cast(DT_STR,Length,Code)

Step 5:

Drag OLE DB Destination and connect Derived Column Transformation to it and choose dbo.SaleRegion as our destination table and map the input column to destination columns.



Step 6:
As we have created Flat File Connection to Asia file but the file name will be changing with each iteration so we have to go to Flat File Connection and write expression for connection string to use the VarFileName variable.

Set DelayValidation to True.

Step 7:
Run the SSIS Package and check if the data from all files are loaded to table.
All the files are loaded successfully with fully qualified path.

Comments

Post a Comment

Popular posts from this blog

What is Gini Impurity?

Image
  Gini Impurity A measurement used to build Decision Trees to determine how the features of a dataset should split nodes to form the tree. What is Gini Impurity? Gini Impurity is a measurement used to build Decision Trees to determine how the features of a dataset should split nodes to form the tree. More precisely, the Gini Impurity of a dataset is a number between 0-0.5, which indicates the likelihood of new, random data being misclassified if it were given a random class label according to the class distribution in the dataset. For example, say you want to build a classifier that determines if someone will default on their credit card. You have some labeled data with features, such as bins for age, income, credit rating, and whether or not each person is a student. To find the best feature for the first split of the tree – the root node – you could calculate how poorly each feature divided the data into the correct class, default ("yes") or didn't default ("no&quo
Read more

Artificial Neural Network Interview Questions

Image
  Artificial Neural Network Interview Questions 1. Explain Neural Network?  Neural Network is a sequence of an algorithm that gives its best to recognize the underlying relationship in a data-set through a process. The artificial neural network has several differences from biological brains. These networks play a crucial role in deep learning.    2. Explain Biological Neural Network and Artificial Neural network? Biological Neural Network  is prepared with the help of real neurons. Our nervous system is made via neurons and brain. A biological neural network is our nervous system in which neurons are linked with each other. Human has a mind to understand and can operate any task in a particular situation, but how can a robot do that? For that purpose, an artificial brain is designed, which is known as  Neural Network . The human brain has neurons to transfer the information or data. Similarly, the neural network has nodes to perform the task. Nodes are the mathematical functions. 3. Wh
Read more

20+ Questions to Test your Skills on Logistic Regression

Image
1. What do you mean by the Logistic Regression? It’s a classification algorithm that is used where the target variable is of categorical nature. The main objective behind Logistic Regression is to determine the relationship between features and the probability of a particular outcome. For Example,  when we need to predict whether a student passes or fails in an exam given the number of hours spent studying as a feature, the target variable comprises two values i.e. pass and fail. Therefore, we can solve classification problem statements which is a supervised machine learning technique using Logistic Regression. 2. What are the different types of Logistic Regression? Three different types of Logistic Regression are as follows: 1. Binary Logistic Regression:  In this, the target variable has only two 2 possible outcomes. For Example,  0 and 1, or pass and fail or true and false. 2. Multinomial Logistic Regression:  In this, the target variable can have three or more possible values witho
Read more