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.
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.
Good information and, keep sharing like this.
ReplyDeleteCrm Software Development Company in Chennai
thanks for your extrodinary services , go ahead
ReplyDeleteScaffolding dealers in chennai
We help you to get best websites and good ranking in search engines, visit us
ReplyDeleteErp software development company in chennai
Professional webdesigning company in chennai
Best seo company in chennai
Crm software development company in chennai
Thanks for providing this information .I hope it will be fruitfull for me. Thank you so much and keep posting.web portal development company in chennai
ReplyDeleteprofessional web design company in chennai
smo company in chennai
seo company in chennai
sem company in chennai
twitter marketing company in chennai
erp software development company in chennai
best seo company in chennai
Thanks for providing this information .I hope it will be fruitfull for me. Thank you so much and keep posting.web portal development company in chennai
ReplyDeleteprofessional web design company in chennai
smo company in chennai
seo company in chennai
sem company in chennai
twitter marketing company in chennai
erp software development company in chennai
best seo company in chennai
Thanks for providing this information .I hope it will be fruitfull for me. Thank you so much and keep posting.scaffolding dealers in chennai
ReplyDeletealuminium scaffolding dealers in chennai
Nice post, you provided a valuable information, keep going.
ReplyDeletePrestashop ecommerce development company chennai
Prestashop ecommerce development company in chennai
Prestashop ecommerce development company
Prestashop ecommerce development company in india
Nice information keep sharing like this.
ReplyDeletescaffolding dealers in chennai
Aluminium scaffolding dealers in chennai
Aluminium scaffolding hire
Nice information keep sharing like this.
ReplyDeletescaffolding dealers in chennai
Aluminium scaffolding dealers in chennai
Aluminium scaffolding hire
Wow, Great information and this is very useful for us.
ReplyDeleteprofessional bridal makeup artist in chennai
best bridal makeup artist in chennai
Nice one and very useful for us.Thanks a lot and please keep sharing
ReplyDeleteGood information
ReplyDeletesecurity agency in chennai
best security service in chennai
Great information and this is very useful for us.
ReplyDeletepost free classified ads in india
Nice information Keep going
ReplyDeletevito food oil dealers in chennai
freezer with plastic body dealers in chennai
very useful information for us.
ReplyDeletebest cafe in chennai
Nice Blog!!. Thanks For Sharing..
ReplyDeleteHR Pay Roll Software
JAVA Development Services
Hotel Billing Software
Web Design Company
Hospital Management Software
My rather long internet look up has at the end of the day been compensated with pleasant insight to talk about with my family and friends.
ReplyDeleteCRM with Invoicing
Thank you for this post. it's helping me so much.
ReplyDeleteexcellent blog site. Get super DIY scaffold towers from diy scaffold tower. We provide scaffold towers designed by British design with safety and durability in mind for the best user experience.
ReplyDeleteThank you so much for sharing this worth able content with us. The concept taken here will be useful for my future programs and i will surely implement them in my study. Keep blogging article like this.
ReplyDeleteBest CRM System