Scenario:
We get the files in our Source Folder all day long. Each file is appended copy of previous file. We want to create a SSIS Package that will load only the most recent file to our table.
Solution :
We will be using Script task to find out the name of the most recent file and Data Flow Task to load that file to our table.Test_File_20131226.txt is the file that should be read as it is most recent file.
Step 1:
Create a variable VarFolderPath that will contain the folder path in which our files exist and second variable with name VarFileName which will hold the value of most recent File Name.
Step 2:
Drag Script Task to Control Flow Pane and Provide the variables to it as shown
Click on Edit Script and write below script. I have only added the code which is in Red. I have included Messagebox.show just for debugging.
/*
Microsoft SQL Server Integration Services Script Task
Write scripts using Microsoft Visual C# 2008.
The ScriptMain is the entry point class of the script.
*/
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.IO;
namespace ST_2650e9fc7f2347b2826459c2dce1b5be.csproj
{
[System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
#region VSTA generated code
enum ScriptResults
{
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
};
#endregion
public void Main()
{
// TODO: Add your code here
var directory= new DirectoryInfo(Dts.Variables["User::VarFolderPath"].Value.ToString());
FileInfo[] files = directory.GetFiles();
DateTime lastModified = DateTime.MinValue;
foreach (FileInfo file in files)
{
if (file.LastWriteTime > lastModified)
{
lastModified = file.LastWriteTime;
Dts.Variables["User::VarFileName"].Value = file.ToString();
}
}
MessageBox.Show(Dts.Variables["User::VarFileName"].Value.ToString());
Dts.TaskResult = (int)ScriptResults.Success;
}
}
}
Step 3:
Let's run our SSIS Package to make sure that our script is returning us expected file name( Most recent modified by)
Step 4 :
Next step will be to load this file to our destination table. Bring Data Flow Task to Control Flow Pane and connect Script Task to it.Drag Flat File Source inside Data Flow Task and make connection to any file in the Source Folder as all the files have same structure.
Step 5:
As latest file name will be changing, Let's configure our Flat File Connection Manager to use VarFileName variable.
Final Output:
Drag Multicast Transformation in Data Flow Task and connect Flat File Source to it. Double clicking on green line between them and put Data Viewer. I am using Multicast Transformation just for test purpose. In real scenario you will be using OLE DB Destination if you are loading data to table or any other destination depending upon your requirements.
The latest file is read from folder and display by using Data Viewer.
Thanks
ReplyDeleteIEEE Final Year projects Project Centers in Chennai are consistently sought after. Final Year Students Projects take a shot at them to improve their aptitudes. IEEE Final Year project centers ground for all fragments of CSE & IT engineers hoping to assemble.Final Year Projects for CSE
DeleteSpring Framework has already made serious inroads as an integrated technology stack for building user-facing applications. Spring Framework Corporate TRaining .
Specifically, Spring Framework provides various tasks are geared around preparing data for further analysis and visualization. Spring Training in Chennai
The Angular Training covers a wide range of topics including Angular Directives, Angular Services, and Angular programmability.Angular Training
Thanks a lot!!! YOu are the best.
ReplyDeleteThis comment has been removed by the author.
ReplyDeletebest explanation...
ReplyDeleteGood 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
Infusionsoft Quickbooks Integration
ReplyDeleteThanks 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
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
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
Thanks for Fantasctic blog and its to much informatic which i never think ..Keep writing and grwoing your self
ReplyDeleteBirth certificate in delhi
Birth certificate in ghaziabad
Birth certificate in gurgaon
Birth certificate in noida
How to get birth certificate in ghaziabad
how to get birth certificate in delhi
birth certificate agent in delhi
how to download birth certificate
birth certificate in greater noida
birth certificate agent in delhi
Birth certificate in delhi
Doesn't work
ReplyDeleteSorry got it..works well.. thanks
ReplyDeleteThank you so much for practically explaining things about SSIS and SQL.
ReplyDeleteSSIS Postgresql Write
Wow, Great information and this is very useful for us.
ReplyDeleteprofessional bridal makeup artist in chennai
best bridal makeup artist in chennai
The information you've shared in this blog is remarkable. Thanks for sharing such quality information.
ReplyDeletealuminium scaffolding dealers in chennai
scaffolding dealers in chennai
Good 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
Thanks for your blog!!.
ReplyDeleteJAVA Development Services
HR Pay Roll Software
Hotel Billing Software
Web Design Company
Hospital Management Software
SAP Software Services
may i get the source of the above
ReplyDeleteThis is very good information, Thank you
ReplyDeleteThanks for provide great informatic and looking beautiful blog, really nice required information & the things i never imagined and i would request, wright more blog and blog post like that for us. Thanks you once agian
ReplyDeletecourt marriage in delhi ncr
court marriage in delhi
court marriage in noida
court marriage in ghaziabad
court marriage in gurgaon
court marriage in faridabad
court marriage in greater noida
name change online
court marriage in chandigarh
court marriage in bangalore
I really feel there is a need to provide the best information about SSIS and its uitlities that produces the best results.
ReplyDeleteSSIS Postgresql Write
where we can download above example text files
ReplyDeleteDigital marketing agecny
ReplyDeleteDigital marketing agecny
Digital marketing agecny
Digital marketing agecny
Digital marketing agecny
Digital marketing agecny
Digital marketing agecny
Digital marketing agecny
Digital marketing agecny
Digital marketing agecny
Digital marketing agecny
Digital marketing agecny
Digital marketing agecny
Digital marketing agecny
Digital marketing agecny
Digital marketing agecny
Digital marketing agecny
Digital marketing agecny
Digital marketing agecny
Digital marketing agecny
Digital marketing agecny
Digital marketing agecny
Digital marketing agecny
Digital marketing agecny
Digital marketing agecny
Digital marketing agecny
Digital marketing agecny
Digital marketing agecny
Digital marketing agecny
Digital marketing agecny
Thank you, please visit https://www.ecomparemo.com/, thanks!
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.
ReplyDeleteCRM with Invoicing
How does it change the filename variable. I can't seem to get it to update, it's staying blank
ReplyDeleteI'm getting the same as the pictures all the way through to the end of step 5, The picture shows that when you evaluate the expression, it only shows the folder path, mine does the same. But the above shows data is being transferred. Mine gets an error "[Connection manager "Excel Connection Manager 1"] Error: The connection string format is not valid. It must consist of one or more components of the form X=Y, separated by semicolons. This error occurs when a connection string with zero components is set on database connection manager.
Delete"
Thank you very much for this, I'm slightly knew to MSBI and I recently put this into good use at my work.
ReplyDeleteAppreciation from South Africa (2020)
Great post - helped me out!
ReplyDeleteAn ever increasing number of private ventures today are utilizing on the web professional listings to their advantage. local classifieds
ReplyDeleteGreat post. Thank you. It helped me out !
ReplyDeleteThe C# code could have been better
ReplyDeleteSomething simple like this
var filesInOrder = new DirectoryInfo(path).GetFiles()
.OrderByDescending(f => f.LastWriteTime)
.Select(f => f.Name)
.ToList();
Vé máy bay Aivivu, tham khảo
ReplyDeletevé máy bay đi Mỹ
giá vé máy bay tết 2021
ve may bay di san francisco
đặt vé máy bay đi Pháp giá rẻ
giá vé máy bay đi Anh
vé máy bay từ Hà nội đi Los Angeles
combo đi đà nẵng
combo hà nội đà lạt 3 ngày 2 đêm
visa trung quốc 3 tháng
cách ly khách sạn trọn gói