June 23, 2011 at 6:35 am
Hi Guys
Im quit new at this so need a bit of help with my problem.
I need to write a script in SSIS 2008 that gets a list of all the filenames on an FTP site and adds them to a table in my database.
Any help would be much appreciated thanks
June 23, 2011 at 9:05 am
http://www.mssqltips.com/tip.asp?tip=1641
Did you try seaching the internet?
June 23, 2011 at 11:21 pm
Iv come across that topic, but thats SSIS 2005, is there not much difference?
June 24, 2011 at 8:11 am
jmlsauls (6/23/2011)
Iv come across that topic, but thats SSIS 2005, is there not much difference?
I suspect any differences would be minor if they exist at all. If you run into any issues let us know.
June 27, 2011 at 2:12 am
So i managed to get the list of filenames from the ftp that i need to download. Is there a way to specify that only those filenames get sent in an ftp task?
June 27, 2011 at 9:24 am
See this for some ideas. Scripting sounds like it would give you the most control.
You could probably call your package multiple times using DTEXEC passing it a variable (the filename) to the ftp task.
Or the brute force/overkill method, download everything and then parse out the files you need. This is clearly the least elegant solution.
June 27, 2011 at 9:50 am
jmlsauls (6/27/2011)
So i managed to get the list of filenames from the ftp that i need to download. Is there a way to specify that only those filenames get sent in an ftp task?
Why do you want to FTP the Files that you loaded in the Table if you loaded all of the files in the FTP folder?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
June 28, 2011 at 6:23 am
The problem is if i download all the files from the ftp it takes 30min at the moment. I have a table with all the filenames which i have downloaded already and i just want to download the new files which will be added every month. So thats why i got the filelist from the ftp and compare it to my table and those which are not in the table should be downloaded from the ftp site.
June 28, 2011 at 1:17 pm
Can you delete or move the files after you download them? Then you would just have the new files to download.
Otherwise it sounds like you would need scripting or run the package multiple times passing the file you want to download each time.
June 28, 2011 at 1:25 pm
June 28, 2011 at 1:35 pm
As was suggested the files would be moved from the location that you are transferring them from.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
June 29, 2011 at 6:24 am
IV copied some of my code in the script i have this far
Dim sMsg As String
Dim oleDA As New OleDbDataAdapter
Dim dt As New DataTable
Dim col As DataColumn
Dim row As DataRow
Dim sMsg As String
Dim cm As ConnectionManager = Dts.Connections.Add("FTP")
'Setup Ftp connections
cm.Properties("ServerName").SetValue(cm, "10.131.8.65")
cm.Properties("ServerUserName").SetValue(cm, "w03")
cm.Properties("ServerPassword").SetValue(cm, "w03225")
Dim ftp As FtpClientConnection = New FtpClientConnection(cm.AcquireConnection(Nothing))
'Get filenames from table in database
For Each row In dt.Rows
For Each col In dt.Columns
sMsg = sMsg & _
row(col.Ordinal).ToString & vbCrLf
Next
Next
'Msbox just do check if the filenames are coming through
MsgBox(sMsg)
'Everything works up until this point but i cannot seem to get the 'Recievefiles task to only return the filenames found in sMsg
http://ftp.ReceiveFiles(sMsg, "C:\SSIS", True, False) 'where my problem lies
Dts.TaskResult = ScriptResults.Success
End Sub
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply