locked
Get File Properties in SSIS RRS feed

  • Question

  • How to monitor the properties of dynamically changing flat files in ssis...?? 
    Monday, March 12, 2012 10:46 AM

All replies

  • Hi

    Am trying to load data from flat files to sql tables using for each container and in between this process i would like to read corresponding file properties to one sql table just for information and am using script task to read all these properties but it is not working as expected.

    Can i get any help regarding this..??

    Tuesday, March 13, 2012 5:45 AM
  • http://www.sqlservercentral.com/Forums/Topic1265063-147-1.aspx

    MCTS, MCITP - Please mark posts as answered where appropriate.

    Wednesday, March 14, 2012 12:06 PM
  • Hi,

    Please check this

    http://sqlage.blogspot.com/2011/02/load-files-information-to-sql-server.html

    Thanks

    Aamir


    http://sqlage.blogspot.com/

    Wednesday, March 14, 2012 8:02 PM
  • And permit me to add my $0.02:

    I have used the File Properties Task before with a huge success in a similar scenario.

    Highly recommend it.


    Arthur My Blog

    Thursday, March 15, 2012 6:15 PM
  • Check this link and check step 2 you will see code that reads the File properties something like

        Public Sub Main()
            '
            ' Add your code here
            '
            Dim LastSlashPosition As Integer
            Dim strPointer As Integer
            Dim strLen As Integer
            Dim strFilePath As String
            Dim strFileName As String
            Dim strLenFullFileName As Integer
            Dim strFileExtension As String
            Dim strPathAndFileName As String = Dts.Variables("uVar_CurrentFileName").Value.ToString()
            If File.Exists(strPathAndFileName) Then
                'FileDate = File.GetLastWriteTime(strPathAndFileName)
                'File.GetAttributes(strPathAndFileName).GetName()
                strLen = Len(strPathAndFileName)
                LastSlashPosition = strPathAndFileName.LastIndexOf("\")
                strPointer = strLen - (LastSlashPosition + 1)
                strFilePath = Left(strPathAndFileName, strLen - strPointer - 1)
                strFileName = Right(strPathAndFileName, strPointer)
                strLenFullFileName = Len(strFileName)
                strLen = strFileName.IndexOf(".")
                strFileExtension = Right(strFileName, (strLenFullFileName - strLen - 1))
                strFileName = Left(strFileName, strLen)
                Dts.Variables("uVar_PathFileName").Value = strPathAndFileName
                Dts.Variables("uVar_Path").Value = strFilePath
                Dts.Variables("uVar_FileName").Value = strFileName
                Dts.Variables("uVar_FileExtension").Value = strFileExtension
                Dts.Variables("uVar_DateCreated").Value = File.GetCreationTime(strPathAndFileName)
                Dts.Variables("uVar_DateModified").Value = File.GetLastWriteTime(strPathAndFileName)
            End If
            Dts.TaskResult = ScriptResults.Success
        End Sub


    Sincerely SH -- MCITP 2008, MCTS 2008 & 2005 -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).

    Thursday, March 22, 2012 1:49 PM