SSIS - Script Task find a string value in a file name

user_17829044 0
Viewed 547

I have to build a job in SSIS to check if file with today's date exist then load data into targeted table.

My problem is I have different files stored with different dates in this folder with the format FileName_yyyyMMddhhmmss.csv and am not able to check if I have a filename with today's date (FileName_20220309) exist.

What I have done so far is I have created 3 variables

  1. FolderPath
  2. FileName
  3. FileExistsFlg

For the variable FileName, I have used the following expression to get the format FileName_20220309

"Player_info_" + (DT_WSTR,50)(((DT_I8) ((DT_WSTR,4)DATEPART("yyyy",GetDate()) + RIGHT("0" + 
(DT_WSTR,2)DATEPART("mm",GetDate()) ,2) +RIGHT("0" + (DT_WSTR,2)DATEPART("dd",GetDate()),2))))

I have used a Script Task component where I have passed variables FileName and FolderPath as ReadOnlyVariables and FileExistsFlg as ReadWriteVariables

Below is my script used in the script task component


#region Namespaces
using System;
using System.IO;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
#endregion

namespace ST_3692973debdd4531ac4eced28213e38f
{
[Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
public partial class ScriptMain : 
Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
    public void Main()
    {
        // TODO: Add your code here
        String Filepath = Dts.Variables["User::FolderPath"].Value.ToString()+Dts.Variables["User::FileName"].Value.ToString();
        String SearchString = Dts.Variables["User::FileName"].Value.ToString();
        if(
            File.Exists(Filepath))
        {
            Dts.Variables["User::FileExistsFlg"].Value = 1;
        }

        MessageBox.Show(Filepath);
        MessageBox.Show(Dts.Variables["User::FileExistsFlg"].Value.ToString());

        Dts.TaskResult = (int)ScriptResults.Success;
    }

    #region ScriptResults declaration
    enum ScriptResults
    {
        Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
        Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
    };

}
}

With the above code, I'm not able to check if a file with today's date (FileName_20220309) exists.

Can someone help with how I can modify the above code to check if the string exists in the filename?

script-task etl ssis sql-server c#
2 Answers

I think you want something like this...

var files = DirectoryInfo(folderPath).GetFiles("*" + DateTime.Now.ToString("yyyyMMdd") + "*");

bool flag = files.Length > 0 ? true : false;

This will identify any files in a folder that have today's date in the yyyyMMdd format in them.

user_33252900

Using File.Exists(Filepath) need a complete path of the file instead of the file name. For example, File.Exists(@"C:\data_2022.csv");. Instead of using the FileExists() functions, you should enumerate the directory files and check if a file with the given name exists.

Consider using the following code:

#region Namespaces
using System;
using System.IO;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
#endregion

namespace ST_3692973debdd4531ac4eced28213e38f
{
[Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
public partial class ScriptMain : 
Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
    public void Main()
    {
        // TODO: Add your code here
        String Filepath = Dts.Variables["User::FolderPath"].Value.ToString()+Dts.Variables["User::FileName"].Value.ToString();
        String SearchString = Dts.Variables["User::FileName"].Value.ToString();
        if(Directory.GetFiles(Filepath,SearchString,SearchOption.AllDirectories).length > 0)
        {
            Dts.Variables["User::FileExistsFlg"].Value = 1;
        }

        MessageBox.Show(Filepath);
        MessageBox.Show(Dts.Variables["User::FileExistsFlg"].Value.ToString());

        Dts.TaskResult = (int)ScriptResults.Success;
    }

    #region ScriptResults declaration
    enum ScriptResults
    {
        Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
        Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
    };

}
}
user_70312300
Related