Script Task: File Properties

This script is a skeleton of what is possible to retrieve using the System.IO namespace in an SSIS Script Task. It assumes that several variables exist in SSIS, and have been marked as ReadOnly and ReadWrite in order for the Script to pass values back to the package:
ReadOnly: User::FileName (String)
ReadWrite: User::FileExists (Boolean), User::FileDate (DateTime), User::FileAttribute (Boolean), User::FileCanOpen (Boolean)

' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime

Public Class ScriptMain
  Public Sub Main()
            If (Not System.IO.File.Exists(Dts.Variables("User::FileName").Value)) Then
                Dts.Variables("User::FileExists").Value = False

                Dts.Variables("User::FileExists").Value = True

                Dim fileInfo As System.IO.FileInfo
                fileInfo = New System.IO.FileInfo(Dts.Variables("User::FileName").Value)

                Dts.Variables("User::FileDate").Value = fileInfo.CreationTime ' Or another date property
                Dts.Variables("User::FileAttribute").Value = fileInfo.Attributes & FileAttribute.ReadOnly ' Or another attribute property

                    Dim fileStream As System.IO.FileStream = System.IO.File.Open(Dts.Variables("User::FileName").Value, IO.FileMode.Open)
                    Dts.Variables("User::FileCanOpen").Value = True

                Catch ex As Exception
                    Dts.Variables("User::FileCanOpen").Value = False
                End Try
            End If
        Catch ex As Exception
            Dts.TaskResult = ScriptResults.Failure
        End Try

        Dts.TaskResult = ScriptResults.Success
  End Sub
End Class

Last edited Sep 18, 2009 at 3:37 PM by toddmcdermid, version 1


No comments yet.