SSIS:Capture [FileCreationDate] and [FileSize] of .csv files in separate user variables,inside a ForEachLoop Container using Script Task (C#) in CFlow

Question

I have good experience in SSIS, but nil as far as Script Task and Script Component are concerned.

I have a situation in which I am loading many .csv files into a SQL Server destination table. I am using a For Each Loop Container.

I am loading these files, capturing the File Name, Row Count, and other information needed into various SSIS user variables.

I load some of these variables into an Audit Tracker table using an Execute SQL Task in the Control Flow, after the Data Flow Task (all inside a For Each Loop Container)

Upto this point, everything is fine.

Now our client wants to capture the FileCreationDate (i.e.Date Modified) and FileSize of each of these .csv files. I need to capture them in separate user variables.

Obviously I need help, because this cannot be done without some scripting.

In the Control Flow, inside the ForEachLoop Container, I have added a ScriptTask immediately after the Data Flow Task.

I have the following three user variables in the package:

1) User::Filename (string variable for each.csv files;this is fine, no issue)

2) User::FileCreatedDateTime (variable to capture the creation date of each .csv file; please let me know whether DateTime datatype will be okay)

3) User::FileSize (variable to capture the size of each .csv file; let me know whether string datatype will be okay)

Now, can anyone let me know the following settings inside the Script Task Editor ?

Script Tab :

Language as Microsoft Visual C# 2017 (disabled)

Entry Point: Main (Is this correct?)

ReadOnlyVariables: (What should I set ?)

ReadWriteVariables: (What should I set ?)

Expressions Tab:

What expressions should I set ? i.e. Property and Expression.

Edit Script:

What code should I type ? I referred to these two websites, but not able to get the right code

http://www.techbrothersit.com/2011/02/ssis-load-files-information-to-sql.html

SSIS Read file modification date

Can someone please give me the clear code and clarify the other doubts ?

Answer 1

Add Using System.IO; name space

For File CreationDate

DateTime creation_time = File.GetCreationTime(@"C:\test.txt");

Get file size

 long filesize = File.Length; 

write both variables to an output variable.

Answer 2

All of these components are readily explained in google. In fact I am going through google right now to refresh my memory and write this half baked answer.

Please go through this, debug my code, and post the correct code when you get it working. This is unlikely to work first try and you'll need to debug but you'll learn more if you actually do debug it.

Script Tab :

Language as Microsoft Visual C# 2017 (disabled)

I suggest you use C#, not VB as there are more examples online for C#. So leave as is

Entry Point: Main (Is this correct?)

This simply designates the name of the function that will be automatically called within the script task... which in the template is Main so leave as is

ReadOnlyVariables: (What should I set ?)

These are the variables which your script task needs to read (but not write back to). So this should have User::Filename in it. Use the UI to fill it in.

ReadWriteVariables: (What should I set ?)

These are the variables which your script task needs to read and write back to. So this should have something like User::FileCreatedDateTime,User::FileSize in it. Use the UI to fill it in.

Now when you hit the Edit Script.. button you'll get a template script.

Note

• This already has a Main method in it
• It has extensive instructions in the comments on how to read and write variables

(this is adapted from here and probably doesn't work without debugging http://www.techbrothersit.com/2011/02/ssis-load-files-information-to-sql.html)

Open out the namespaces region and add System.IO:

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

Go the main method at the bottom and edit it:

public void main()
{

// Get the file path from the SSIS variable
string FilePath = Dts.Variables["User::Filename"].Value.ToString();

// Use the FileInfo method from System.IO to get the file object
FileInfo file = new FileInfo(FilePath);

// Save file properties back to SSIS variables
Dts.Variables["User::FileCreatedDateTime"].Value = file.CreationTime;
Dts.Variables["User::FileSize"].Value = file.Length;

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

I note that this code is very similar to the second link you posted - if you are having problems with example code you need to explain why (i.e. specific error message)

You could also have a crack at this custom component https://archive.codeplex.com/?p=filepropertiestask but I strongly recommend never using custom components in your SSIS packages as they are usually a hassle down the line.