142 How to move files and append current date to the file in SSIS

[@dbin805]

thank you for putting this together. nice job.

[@vak7553]

I have an issue with the flat file connection manager as I’m giving sample file name , so when I execute the package the file move to dest folder , next time when I run the package it fails as the file used in the connection manager is missing in the source folder it’s moved to dest folder , please advise.
I get the error the system cannot find the file specified

[@learnssis]

here I can think of 2 solutions
1. use the foreach loop container and inside the foreach loop container put the data flow task that imports the file, this way data flow task will only get executed if there is a file in the folder.
2. use script task to check if a file exists or not, if file exists then set the value to a bool variable like IsFileExists = true. And then before loading the data using data flow task, on precedence constraints check if IsFileExists==true then execute data flow task.
ua-cam.com/video/BInCRdxyzL0/v-deo.html

[@vak7553]

@@learnssis in the flat file connect manager properties I used the expression as connectionstring and pointed to filepath variable. No more error then with dynamic configuration of the flat file connection

[@BrundaG-n1f]

I have load the excel data to sql using ssis ,data should load in consecutive manner like 1st dec 1st file should be loaded and nect dec2nd file should be loaded. Can you help with this

[@learnssis]

Take a look at this video, I have shown how to load the files in an order
ua-cam.com/video/rtYir_2SlPM/v-deo.html

[@BrundaG-n1f]

It is based on date modified but I want to load using dates in filename

[@learnssis]

@@BrundaG-n1fIt is an interesting topic, maybe I will make a video on this one.
How I will do this is that
1. I will create a sql server table to hold the file information
create table fileInfo(Id int identity, FilePath varchar(1000), FileDate date)
2. Now I will write some C# code to insert the data into sql server table with file name and date part of the file name.
This is my code.
string currentdatetime = DateTime.Now.ToString("yyyyMMddHHmmss");
string LogFolder = @"D:\Files\Logs\";
try
{
string[] files = Directory.GetFiles(@"D:\Files", "*.CSV", SearchOption.TopDirectoryOnly);
string connectionstring = @"server = DESKTOP-EKJ1P64\SQL2019; database = Training; Integrated Security=SSPI;";
SqlConnection sqlConnection = new SqlConnection();
string sqlquery = "";
sqlConnection.ConnectionString = connectionstring;
sqlConnection.Open();
foreach (string file in files)
{
//Emails_20230105.CSV
string str = Path.GetFileNameWithoutExtension(file);
str = str.Substring(str.Length - 8);
DateTime dt = DateTime.ParseExact(str, "yyyyMMdd", CultureInfo.InvariantCulture);
sqlquery = @"INSERT INTO [dbo].[fileInfo] VALUES ('" + file.Replace("'", "") + "', '" + dt.Date + "')";
SqlCommand sqlcom = new SqlCommand(sqlquery, sqlConnection);
sqlcom.ExecuteNonQuery();
}
}
catch (Exception exception)
{
using (StreamWriter sw = File.CreateText(LogFolder + "\\" + "ErrorLog_" + currentdatetime + ".log"))
{
sw.WriteLine(exception.ToString());
}
}
----------------------------
Now I can easily select the data from my sql table either in ascending order of date or descending order of date
select * from fileInfo order by FileDate desc

[@BrundaG-n1f]

I already have a date column in my table but while loading only it should load like consecutive manner

[@vak7553]

Please also
Create a video for capturing errorcolumnname for the bad record in the error log file in sql table
Along with the bad record

[@learnssis]

SSIS does not provide the errored column names, thus I am not sure how we can capture it.

[@vak7553]

@@learnssis how then we can identify which column had an issue

[@vak7553]

I’m using this piece of code but it’s not helping public override void Inputo_ProcessInputRow(Input0Buffer Row)
string errorColumnName = "Unknown Column" ;
IDTSComponentMetaData100 metadata = this. ComponentMetaData;
//Diagnostic Logging
string debugMessage = "Error Column ID: " + Row. ErrorColumn. ToStringO) + "
";
// Find the column that has the error
foreach IDTSInputColumn100 column in metadata. InputCollection[0] InputColumnCollection)
debugMessage += "Column: " + column .Name + ", LineageID:
+ column.LineageID + "
";
if (column.LineageID = Row. ErrorColumn)
errorColumnName = column. Name;
break;
// Assign the error column name to the output_ buffer
Row. ErrorCoulumnName = errorCoLumnName;
//Write the debugMessage to a file for troubleshooting
System. IO. FiLe.AppendAllText("C: |\Users| |vkothari|\Downloads|\SSIS_DEBUGLog.txt", debugMessage)