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

jn93 506 Reputation points
2022-12-27T01:54:16.547+00:00

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'));  

274191-image.png

SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
1,854 questions
C#
C#
An object-oriented and type-safe programming language that has its roots in the C family of languages and includes support for component-oriented programming.
6,974 questions
{count} votes

Accepted answer
  1. Yitzhak Khabinsky 20,011 Reputation points
    2022-12-27T14:19:24.907+00:00

    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");  
     }  
    }  
    

1 additional answer

Sort by: Most helpful
  1. Jack J Jun 19,216 Reputation points Microsoft Employee
    2022-12-27T06:23:19.17+00:00

    @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.