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

Question

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

Ed7

SSCrazy Eights

Points: 8516
More actions
February 16, 2012 at 8:37 am

#251739

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

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

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

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 1

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: 8516 More actions · Answer 2

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: 259085 More actions · Answer 3

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 More actions · Answer 4

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

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