SSIS Design : Dynamic File name in HTTP location or File name with Date in HTTP Location

Scenario : In HTTP location the files are placed everyday with Date in the filename  and we need to get the files which are not loaded.
If the filename looks like
http://XXXXXXXX/html/kpn_services/kpn_services_20140522.csv

Implementation  : Step 1 : Create the Execute SQL task to get the list of all the file names which needs from HTTP location.

     1,1 you can get the dates from the Destination table where you store the data   ie get all the dates from today to  max(date)  in the destination table.


          declare  @FromDate datetime
declare @Todate datetime
DECLARE @HttpTable TABLE ( fullpath varchar(100), filename varchar(50) )
select @FromDate = max(date) from ODS_Schema.Unicast_level2
select @Todate = getdate()-1
while (@Todate > @FromDate)
begin
insert into @HttpTable (fullpath,filename)
select 'http://XXXXXXXX/html/kpn_services/kpn_services_'+ max(convert(varchar(10),@FromDate,112))+'.csv'  as fullpath, 'kpn_services_'+ max(convert(varchar(10),@FromDate,112))+'.csv' as filename
set @FromDate = @FromDate + 1
end
select * from @HttpTable

Store this resultset ie FileURL, filename in a Object datatype variable


1,2  Select the ForEachloop Container
     
     Double click on Container and set the properties as shown in below fig

 The above step will iterates among the number of files avaiable .

   1.3 In the ForEachLoop container Place a script task

we need to pass  3 variables 
 1. FileURL
 2. FileName
 3.Destination where the file needs to be placed.

Here is the complete script

Imports System

Imports System.DataImports System.MathImports Microsoft.SqlServer.Dts.RuntimeImports System.Net Imports System.IO
<System.AddIn.AddIn(
"ScriptMain", Version:="1.0", Publisher:="", Description:="")> _<System.CLSCompliantAttribute(
False)> _Partial Public Class ScriptMain
Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
Enum ScriptResultsSuccess = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure

End EnumPublic Sub Main()
''This routine will try to fetch all files since the LastFetched date and now() for one single source */'' Fetch the package vars for this source */Dim sHttpFilepath As String = Dts.Variables("FileURL").Value
Dim sDestPath As String = Dts.Variables("Destination").Value
Dim sFileName As String = Dts.Variables("Filename").Value
Dim oWC As WebClient = New WebClient()

TryoWC.DownloadFile(sHttpFilepath, sDestPath & sFileName)Catch ex As ExceptionSystem.IO.File.Delete(sDestPath & sFileName)
Dts.Events.FireWarning(
Nothing, "HTTP Conn", "File " + sFileName + " not present", Nothing, Nothing)
End Try
Dts.TaskResult = ScriptResults.SuccessEnd SubEnd
Class
 
This process will download all the files you need from Http location.