How to get files everyday from FTP but automated

Question

Post reply

How to get files everyday from FTP but automated

Lookup_BI

SSCrazy

Points: 2964
More actions
November 8, 2007 at 8:45 am

#119377

Hi, I am stuck with one thing. I have to download FTP files which are .csv extensions everyday to my desktop and then from there move it to SQL server. Everyday, the file name will be the same but the date changes accroding to that day's date. Example is: ABC_yyyymmdd.csv. The yyyymmdd part only change everyday accordingly to the current date. How do I do this? Please can someone help. Some said I can use ActiveX script in SSIS, but I have no idea how to use this task. HELP!!!!!!
[font="TimesNewRoman"] “I haven't failed, I've found 10,000 ways that don't work”........Thomas Alva Edison[/font]

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

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

Tommy Bollhofer SSChampion Points: 14940 More actions · Answer 1

Create a a new string variable to store the file name (varFileName) and populate the variable with a script task - i.e.

Option Strict Off

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Runtime

Imports System.IO

Public Class ScriptMain

Public Sub Main()

'

Dim strDate As DateTime = DateTime.Now

Dim strFileName As String

strFileName = Dts.Variables("varFileName").Value.ToString & "_" & + _

CDbl(Dts.Variables("varRowCount").Value.ToString) & "_" & + _

DateTime.Now.ToString("yyyyMMddhhmmss") & ".CSV"

'Write strFileName to the varFileName variable

'Dts.Variables("varFileName").Value = strFileName

'For Debugging Purposes

System.Windows.Forms.MessageBox.Show(strFileName)

Dts.TaskResult = Dts.Results.Success

End Sub

End Class

You can then use an FTP task to retrieve the file using the "varFileName" variable.

Tommy

Follow @sqlscribe

Lookup_BI SSCrazy Points: 2964 More actions · Answer 2

Thanks a bunch there. But I have not at all used the Script task before. Can you walk me throught a step by step process. I will do it accordingly.

[font="TimesNewRoman"] “I haven't failed, I've found 10,000 ways that don't work”........Thomas Alva Edison[/font]

Tommy Bollhofer SSChampion Points: 14940 More actions · Answer 3

It's pretty straight forward. Create a new SSIS package. From the toolbar in BIDS or VS 2005 select the SSIS dropdown menu, and the variables option. Create a new string variable called varFileName. From the toolbox menu, drag the script task component on to the control flow. Right-click the component and select the script tab. Under ReadWriteVariables type: varFileName. Click on the Design Script radio button. Copy and paste the script above into the editor. Modify the following line: