none
How to load FileName and File Size into Audit table RRS feed

  • Question

  • Hi All,

    I have one Flat File  SOURCE and OLEDB Destination which I'm Loading Properly in mean while I want to load Data into Audit table for Tracking purpose.

    Package :

    Variables :

    Here I want to capture file Name from Folder Path and File Size . I have used script Task

    Using System.IO;

    From there I'm loading into Dbo.FileInformation table using Execute SQL Task.

    INSERT INTO dbo.FileInformation(Filename,FileSize,Rcnt)VALUES(?,?,?)

    and Parameter mapping also done Properly

    After executing the package my destination table is loading like below

    I need to get output like this  :

    Suggest me How I need to pick filename and File Size using Script task


    • Edited by mohan1111 Monday, June 17, 2019 8:38 PM
    Monday, June 17, 2019 8:28 PM

All replies

  • Hi mohan1111,

    You can get the c# source code for SSIS Script Task here:

    SSIS: Capturing the [File Creation Date] and [File Size]

    Monday, June 17, 2019 8:50 PM
  • Hi Yitzhak,

    I want File name and File size but in the example they are showing only for File size. Can you please suggest me on this

    Tuesday, June 18, 2019 2:44 AM
  • Hi mohan1111

    The following code will be helpful:

    Dts.Variables["FileName"].Value = System.IO.Path.GetFileName(Dts.Variables["FileName"].Value.ToString());

    Please refer to Using SSIS to Capture the File Name .

    Best Regards,

    Mona


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Tuesday, June 18, 2019 3:10 AM
  • Hi mohan1111,

    Just add one more line:

    // Get file name without fully qualified path
    Dts.Variables["User::FileName"].Value = fileInfo.Name;

    Tuesday, June 18, 2019 4:16 AM
  • Hi Yitzhak ,

    I have implemented this code and getting proper filename and coming to File size I'm getting error

    see the code :

    public void Main()
      {
                // TODO: Add your code here
                string strpath = Dts.Variables["FileFullPath"].Value.ToString();
                FileInfo objFI = new FileInfo(strpath);
                Dts.Variables["FileName"].Value = objFI.Name;
                Dts.Variables["FileSize"].Value = objFI.Length;


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

    Tuesday, June 18, 2019 6:32 AM
  • Hi Mona,

    Can you please check my below reply

    Tuesday, June 18, 2019 6:32 AM
  • And which error message do you get?

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Tuesday, June 18, 2019 6:44 AM
  •    at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor)
       at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments)
       at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
       at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams)
       at Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript()
    Tuesday, June 18, 2019 7:52 AM
  • Hi mohan1111,

    Please share the following:

    • Entire Script Task source code.
    • Script Task settings, to see what SSIS variables are passed to it.
    Tuesday, June 18, 2019 3:37 PM