Need help with C# script

Question

Hello,

I am a sql developer working with SSIS packages that sometimes use the C# code and I am new to C# . All I am trying to do is in a folder with a bunch of csv files I need to move data from the ones that start with a specific name and move data from that csv file to a sql database table. Here is my code:

if (file.LastWriteTime > lastModified && Path.GetFileName(file.FullName).StartsWith("connect*", StringComparison.InvariantCultureIgnoreCase) == true && file.Extension == "*.csv")

I am trying to look for the file that starts with the string connect. The name of file looks like connectwithme_04052016_0910AM.csv for instance. The above code doesnt error out but does nothing.

Is there a way to handle this properly?

 

Answer 1

Remove the * from your StartsWith call. StartsWith looks for the exact string you specified and doesn't support wildcards. hence "connect*" would match any filename that starts with connect*. To match any file that starts with "connnect" then simply use that text instead.

Michael Taylor
http://www.michaeltaylorp3.net

Answer 2

Remove the * from your StartsWith call. StartsWith looks for the exact string you specified and doesn't support wildcards. hence "connect*" would match any filename that starts with connect*. To match any file that starts with "connnect" then simply use that text instead.

Michael Taylor
http://www.michaeltaylorp3.net

Yes. I just tried that and nothing happens. I meant the script task completes successfully like usual but doesnt go the dataflowtask which is the next in the flow. Same behaviour with or without star.

Answer 3

Also replace “*.csv” with “.csv”.

Seems that you are enumerating the files from a directory (perhaps using Directory.EnumerateFiles). Then maybe filter the names by specifying the corresponding pattern, such as: “connect*.csv”. Then only check the dates. If you are interested, show some details. A possible solution is:

var files =

       new DirectoryInfo( @"C:\MyFiles" )

       .EnumerateFileSystemInfos( @"connect*.csv" )

       .Where( f => f.LastWriteTime > lastModified );

Answer 4

Also replace “*.csv” with “.csv”.

Seems that you are enumerating the files from a directory (perhaps using Directory.EnumerateFiles). Then maybe filter the names by specifying the corresponding pattern, such as: “connect*.csv”. Then only check the dates. If you are interested, show some details. A possible solution is:

var files =

       new DirectoryInfo( @"C:\MyFiles" )

       .EnumerateFileSystemInfos( @"connect*.csv" )

       .Where( f => f.LastWriteTime > lastModified );

I am doing this in an SSIS package. All I am trying to do is read data from each csv file and load it to a table in sqlserver database and then once the load is done just delete the file from where it is read and go to next next one. Here is how my code looks like to read:

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.IO;

namespace ST_39db7767b6dd4ebf8bd01d16a1653821.csproj
{
    [System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {


        public void Main()
        {
            // TODO: Add your code here
            var directory = new DirectoryInfo(Dts.Variables["User::FolderPath"].Value.ToString());

            FileInfo[] files = directory.GetFiles();
            
            DateTime lastModified = DateTime.MinValue;

            foreach (FileInfo file in files)
            {
                if (file.LastWriteTime > lastModified && Path.GetFileName(file.FullName).StartsWith("connect", StringComparison.InvariantCultureIgnoreCase) == true && file.Extension == "*.csv")
                {
                    
                    lastModified = file.LastWriteTime;
                    Dts.Variables["User::FileName"].Value = file.ToString();
                    Dts.Variables["User::FileExists"].Value = "True";
                }
            }

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

Here is code to delete after data load:

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.IO;

namespace ST_0bf71741d19e4ed8b069e1bb557f68fd.csproj
{
    [System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {

        public void Main()
        {
            // TODO: Add your code here
            
            string destfile=Dts.Variables["User::FolderPath"].Value.ToString() +"\\"+ Dts.Variables["User::FileName"].Value.ToString();
            if (File.Exists(destfile))
            { File.Delete(destfile); }
                    
         

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

so my ssis package looks like Script1---> data flow--->script2

The issue here is script1 completes successfully but not going to data flow

Answer 5

As Viorel mentioned, you need to change the check for the extension to just be .csv. You also should make it case insensitive.

if (file.LastWriteTime > lastModified && 
    Path.GetFileName(file.FullName).StartsWith("connect", StringComparison.InvariantCultureIgnoreCase) &&
    String.Compare(file.Extension, ".csv", true) == 0)
                {

One thing that I notice about your script is that it will always get the newest file irrelevant of how many are actually there. Hopefully that is what you want. Note that you can probably accomplish a similar thing using the FileSystem task. But for questions related to SSIS you'd need to post in their forums.

If your data flow task is not running then you need to look at the condition that it uses (if any).  Based upon your script, you will never fail the call so your data flow would always run. You can set a breakpoint on the DF task and confirm it gets hit. If it doesn't and you've connected the Success pipe of the script to the DF task then your script has to be failing.

If the DF task gets hits then you can confirm the variables are set properly which would eliminate an issue with your script. Hopefully you have scoped the variable properly so that they are reset each time through your loop. I would recommend adding some calls to FireEvent to help verify that your script is setting the correct variable values. You can also step into your script and verify everything is working correctly.

One other thing that comes to mind is that you need to ensure that you added the variables to the ReadWrite collection in your script's properties. If you don't then it should probably generate an error but I've never tried it. You might want to look at the run log to ensure no warnings are generated either.