Scenario : Download Script
You are working as ETL Developer or SQL Server Integration Services developer. You get multiple files on daily basis in your Source folder. The files comes with date and some of them comes with date and time. You need to extract Name from file and then load to table accordingly. Each file Name does match with table Name.
How to load flat files to SQL Server Tables according to the name of file in SSIS Package by using Script Task
Here is script that I used to create tables for above sample files.
Create table dbo.Customer (Id int, Name varchar(100) ) GO Create Table dbo.TechBrothersInfo ( Id int, CourseTitle VARCHAR(100) )
Solution:
We are going to use Script Task with C# scripting language in our SSIS Package to load the files from source folder to sql server tables according to the names. we will also archive ( move) the files once loaded.
Step 1: Create New SSIS Package and Variables
Open SSDT ( SQL Server Data Tools) and create new SSIS Package. After that go ahead and create variables so we can use them in configuration to pass values anytime our requirement change.
ArchiveFolder: Provide the folder path where you would like to move files after loading. Datetime part will be added to file name.
FileDelimiter : Provide the delimiter which is used in your txt or csv files.
FileExtension : Provide the Extension of files you would like to load from folder.
LogFolder : Provide the folder path where you would like to create log file in case of error in script task
SourceFolder: Provide the source folder path where text files or csv files are places for import process.
SchemaName : Provide the schema in which tables are present. In my case all the tables exists in dbo schema.
Create variables in SSIS Package to load flat files dynamically to sql server tables according to file name
Step 2: Create ADO.NET Connection in SSIS Package to use in Script Task
Create ADO.NET Connection Manager so we can use in Script Task to Load data from flat files to SQL Server Tables.
Create ADO.NET Connection in SSIS Package to use in Script Task to load flat files to sql server tables according to file names
Step 3: Add Variables to Script Task to use from SSIS Package
Bring the Script Task on Control Flow Pane in SSIS Package and open by double clicking Check-box in front of variable to add to Script Task.
Add variables to Script Task to load files to sql server table as per file name in SSIS Package
Step 4: Add Script to Script task Editor in SSIS Package to import multiple CSV files or Text Files to SQL Server Tables according to the name of file
Click Edit Button and it will open Script Task Editor.
Under #region Namespaces, I have added below code
using System.IO; using System.Data.SqlClient;
Under public void Main() {
I have added below code.
string datetime = DateTime.Now.ToString("yyyyMMddHHmmss"); try { //Declare Variables string SourceFolderPath = Dts.Variables["User::SourceFolder"].Value.ToString(); string FileExtension = Dts.Variables["User::FileExtension"].Value.ToString(); string FileDelimiter = Dts.Variables["User::FileDelimiter"].Value.ToString(); string ArchiveFolder = Dts.Variables["User::ArchiveFolder"].Value.ToString(); string SchemaName= Dts.Variables["User::SchemaName"].Value.ToString(); SqlConnection myADONETConnection = new SqlConnection(); myADONETConnection = (SqlConnection) (Dts.Connections["DBConn"].AcquireConnection(Dts.Transaction) as SqlConnection); //Reading file names one by one string SourceDirectory = SourceFolderPath; string[] fileEntries = Directory.GetFiles(SourceDirectory, "*" + FileExtension); foreach (string fileName in fileEntries) { string TableName = ""; //Remove All Numbers and other characters and leave alphabets for name System.Text.RegularExpressions.Regex rgx = new System.Text.RegularExpressions.Regex("[^a-zA-Z]"); TableName = rgx.Replace(fileName.Replace(SourceDirectory, "").Replace(FileExtension, ""),""); // MessageBox.Show(TableName); //Writing Data of File Into Table int counter = 0; string line; string ColumnList = ""; //MessageBox.Show(fileName); System.IO.StreamReader SourceFile = new System.IO.StreamReader(fileName); while ((line = SourceFile.ReadLine()) != null) { if (counter == 0) { ColumnList = "[" + line.Replace(FileDelimiter, "],[") + "]"; } else { string query = "Insert into "+SchemaName+"." + TableName + " (" + ColumnList + ") "; query += "VALUES('" + line.Replace(FileDelimiter, "','") + "')"; // MessageBox.Show(query.ToString()); SqlCommand myCommand1 = new SqlCommand(query, myADONETConnection); myCommand1.ExecuteNonQuery(); } counter++; } SourceFile.Close(); //move the file to archive folder after adding datetime to it File.Move(fileName, ArchiveFolder + "\\" + (fileName.Replace(SourceFolderPath,"")).Replace(FileExtension,"") + "_" + datetime+FileExtension); Dts.TaskResult = (int)ScriptResults.Success; } } catch (Exception exception) { // Create Log File for Errors using (StreamWriter sw = File.CreateText(Dts.Variables["User::LogFolder"].Value.ToString() + "\\" + "ErrorLog_" + datetime + ".log")) { sw.WriteLine(exception.ToString()); Dts.TaskResult = (int)ScriptResults.Failure; } }
Step 5: Run SSIS Package to Load flat files Data to SQL Server table/s according to files Name
Save the script and Close the Script Task Editor. Run your SSIS Package.It should load the data from flat files to SQL Server tables according to the file names.
After loading the files , the files should be moved to archive folder. I tested and it load the files data successfully to my tables as shown below.
How to load csv/ txt files to SQL Server Tables according to name of files in SSIS Package
Video Demo: How to Load CSV files to SQL Server Table as per name of file
Related Posts / Videos on Dynamic Text / CSV files by Script Task
- How to Import Multiple Text or CSV files to SQL Server Table by using Script Task in SSIS Package
- How to Load Text /CSV files with same or less columns than destination table by using Script Task in SSIS Package
- How to load Data from Multiple Text / CSV Files to SQL Server Table with File Name by using Script Task in SSIS Package
- How to Create Tables Dynamically from Flat Files and load Data in SSIS Package
- How to load Flat files to SQL Server Tables according to the Name of Flat File in SSIS Package
- How to Create Multiple Text/CSV Files Dynamically From a SQL Server Table depending upon Distinct Column Value in SSIS Package
- How to Export large table to multiple text/csv files by row count in SSIS Package
- How to create Text / CSV File Dynamically from Table or View in SSIS Package
- How to create Text / CSV File Dynamically from Stored Procedure Results in SSIS Package
- How to Load all text/csv files to single Text/CSV File from a folder in SSIS Package
- How to export all the tables from a database to CSV files with date-time in SSIS Package
I am very proud to read such an informative blog. i Will follow your updates in future so, please add more and more ideas.
ReplyDeletePython Training in Chennai
Python course in Chennai
JAVA Training in Chennai
Big data training in chennai
Selenium Training in Chennai
Android Training in Chennai
Python Training in Chennai
Python Training in Anna Nagar
Great Article Artificial Intelligence Projects
DeleteProject Center in Chennai
JavaScript Training in Chennai
JavaScript Training in Chennai
Excellent Piece of information ...Very useful content and it really helped me solve my queries ..You have done a phenomenal work..Thanks...
ReplyDeletePython Training in Chennai
Data Science Training in Chennai
Devops Training in Chennai
Artificial Intelligence Training in Chennai
Selenium Training in Chennai
This post is really useful and i will share this with my friends and bookmark this url for my future use..Good Writing..
ReplyDeletePython Training in Chennai
Data Science Training in Chennai
DevOps Training in Chennai
Android Training in Chennai
digital marketing course in chennai
Azure Training in chennai
Getting this error, think the problem is my files are tab delimited and Character fields are quoted "", could you suggest a workaround? Thanks!
ReplyDeleteSystem.Data.SqlClient.SqlException (0x80131904): The identifier that starts with '200 "20088888" "88888 Direct Returns EU " "88888" "UNKNOWN" "200999" "UK NON-MERCHANDISE EU " "999" "200999" "UK NON-MERCHAND' is too long. Maximum length is 128.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at ST_329fed8be5864a2283b99045c8c9116b.ScriptMain.Main()
ClientConnectionId:9bb1bd94-61f6-4365-84e2-7f107be685c0
Error Number:103,State:4,Class:15
Another issue, the script is replacing the underscores in the file name which should be the table name.
ReplyDeleteInvalid object name 'dbo.ADISITEID' should be ADI_SITE_ID, my file and table are both name correctly.
[a-zA-Z0-9_] fixing this issue with this in script
Deletewhen I do that I get this System.Data.SqlClient.SqlException (0x80131904): Incorrect syntax near '\'.
DeleteĐặt mua vé máy bay liên hệ Aivivu
ReplyDeleteve may bay di my gia re
mua vé máy bay từ mỹ về việt nam
giá thuê máy bay từ anh về việt nam
chuyến bay từ Pairs về TpHCM
How can I download the source text files?
ReplyDelete