C# Script task to get hardcode date if the date is not valid

Question

Hi All, I have the script task shown below inside my SSIS task to get the date from the my filename. Suppose my filenaming got changes like shown in image below, how can I edit my script to get date of 1900-01-01 if the date is not valid to ensure there is no error when processing the file. Please refer the SQL code below which can treat this kind of scenario.

C# script
public void Main()
{

            string FilenameWoutExt = System.IO.Path.GetFileNameWithoutExtension(Dts.Variables["User::v_FILENAME"].Value.ToString());  
  
            string Filelastnaming = Right(FilenameWoutExt, 13);  
            string yy = Filelastnaming.Substring(0, 4);  
            string mm = Filelastnaming.Substring(4, 2);  
            string dd = Filelastnaming.Substring(6, 2);  
            string hh = Filelastnaming.Substring(9, 2);  
            string m = Filelastnaming.Substring(11, 2);  
            string dateValue = yy + "-" + mm + "-" + dd + " " + hh + ":" + m;  
  
            Dts.Variables["User::v_FILEDATE"].Value = dateValue;  
  
        }  
        public static string Right(string original, int numberCharacters)  
        {  
            return original.Substring(original.Length - numberCharacters);  
        }  

T-SQL
DECLARE @FILENAME VARCHAR(200) = ?;

DECLARE @FILE_SUBSTR 	VARCHAR(20) = SUBSTRING(@FILENAME, LEN(@FILENAME)-11, 8);  
  
INSERT INTO tmp.TELEMATICS_TRIP_DTL_FILE (INPUT_FNAME, FILE_DATE)   
VALUES (@FILENAME, IIF(ISDATE(@FILE_SUBSTR)=1, CAST(@FILE_SUBSTR AS DATE), '1900-01-01'));  

Answer 1

Hi @jn93 ,

We will use tokenization instead of string parsing.
The fileDateTime variable is of DateTime data type. It will contain the correct value.

c#

void Main()  
{  
 //string FilenameWithoutExt= "RL_TB_COMCN_TMI_20220801_0000";  
 string FilenameWithoutExt= "RL_TB_COMCN_TMI_20220801_0000 2";  
  
 // break string into a string arrays of tokens  
 string[] tokens =  FilenameWithoutExt.Split('_');  
 string dateValue = tokens[tokens.Count()-2] + tokens[tokens.Count()-1];  
  
 try  
 {  
     fileDateTime = DateTime.ParseExact(dateValue, "yyyyMMddHHmm", CultureInfo.InvariantCulture);  
 }  
 catch (Exception ex)  
 {  
     fileDateTime = DateTime.Parse("1900-01-01");  
 }  
}  

Answer 2

@jn93 , Welcome to Microsoft Q&A, you could try the following code to get hardcode date if the date is not valid.

Code:

       string FilenameWoutExt = System.IO.Path.GetFileNameWithoutExtension(Dts.Variables["User::v_FILENAME"].Value.ToString());  

        string Filelastnaming = Right(FilenameWoutExt, 13);  
        string yy = Filelastnaming.Substring(0, 4);  
        string mm = Filelastnaming.Substring(4, 2);  
        string dd = Filelastnaming.Substring(6, 2);  
        string hh = Filelastnaming.Substring(9, 2);  
        string m = Filelastnaming.Substring(11, 2);  
        string dateValue = yy + "-" + mm + "-" + dd + " " + hh + ":" + m;  
        DateTime temp;  
        if (DateTime.TryParse(dateValue,out temp))  
        {  
            MessageBox.Show("The date is valid");  
        }  
        else  
        {  
            temp = DateTime.Parse("1900-01-01");  
           
        }  
        Dts.Variables["User::v_FILEDATE"].Value = temp;  

Hope my code could help you.

Best Regards,
Jack

---

If the answer is the right solution, please click "Accept Answer" and upvote it.If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.