How can I retrieve a dynamic pdf file that located in a SharePoint or a network.

  • Ed7

    SSCrazy Eights

    Points: 8352

    How can I retrieve a dynamic pdf file that located in a SharePoint or a network and send out to maling list.

    The PDF file was pushed by another SSIS package every day.

    The PDF file has a date stamped and the date will update to current date.

    I created a package and added a Send Mail Taks.

    What kind of Control Flow task that I need to add into SSIS package that will check the most update PDF file?

    Thanks in advance for your help.

    -Edwin

  • Koen Verbeeck

    SSC Guru

    Points: 258927

    You could use a script task. In .NET, you have the FileInfo class that allows you to check the properties of a file. Just look for the most recent one.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Ed7

    SSCrazy Eights

    Points: 8352

    I created a simple package that consist of a Script Task connected to Send Mail Tak.

    There are three variables:

    dtFileDate DateTime 2/17/2012

    dtFilePath string

    dtSourceFolder string = \\sharedrive\ExpenseReports

    then script codes.

    When it executed, it did not send me the actualy report but only the link \\sharedrive\ExpenseReports

    Can you give me some hints.

    Thanks!!!

  • Koen Verbeeck

    SSC Guru

    Points: 258927

    What was the code that you used?

    I don't remember the exact code, but you can create a DirectoryInfo, and issue the method GetFiles (if I remember correctly). This method accepts a wildcard, so you could retrieve only *.pdf files. Store the results in an array, loop over them and retrieve the date using FileInfo. Keep the most recent one.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • rlswisher

    SSC-Addicted

    Points: 439

    You could use this to get you in the right area. It will get the files from a directory. I did it once before where I basically ordered by date in the string files coming in, and only processed the top one.

    ---------------------------------------------------

    Dim ftpFolder As String = Dts.Variables("User::PreRaw").Value.ToString()

    Dim ftpFiles() As String = System.IO.Directory.GetFiles(ftpFolder, "*.*")

    Dim ImportFolder As String = Dts.Variables("User::ImportedPreCert").Value.ToString()

    Dim oFSO As Object = CreateObject("Scripting.FileSystemObject")

    Dim ftpFile

    'loop through the folder and delete the files that already have been processed

    For Each ftpFile In oFSO.GetFolder(ftpFolder).Files

    If oFSO.FileExists(ImportFolder & "\" & oFSO.GetFileName(ftpFile)) Then

    'MsgBox(ftpFolder & oFSO.GetFileName(ftpFile))

    'oFSO.GetFileName(ftpFile).Delete(ftpFolder & "\" & oFSO.GetFileName(ftpFile), True)

    File.Delete(ftpFolder & oFSO.GetFileName(ftpFile))

    End If

    ---------------------------------------------------

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply