October 7, 2015 at 10:17 am
Hello,
I want to create an FTP process within SQL server so I can check FTP sites to see if their are any new files and download them to a specific directory within my network. Once they are downloaded, I will create an SSIS process to upload the data into staging tables.
First, is setting up FTP a reliable way to transact files?
Second, if I put my FTP parameters into a table, how do I loop the parameters into the variables? Do I create a CURSOR or is there a more efficient way to do this?
DECLARE @FTPServer varchar(128)
DECLARE @FTPUser varchar(128)
DECLARE @FTPPwd varchar(128)
DECLARE @SourcePath varchar(128)
DECLARE @SourceFiles varchar(128)
DECLARE @DestPath varchar(128)
DECLARE @FTPMode varchar(10)
-- FTP attributes. HOW DO I LOOP A TABLE TO PROCESS EACH FTP Site?
SET @FTPServer = 'ftpserver'
SET @FTPUser = 'username'
SET @FTPPwd = 'password'
SET @SourcePath = ''
SET @SourceFiles = '*.TXT'
SET @DestPath = 'c:\Temp'
SET @FTPMode = 'binary'
FTP TABLE will contain values for all fields above.
October 7, 2015 at 4:45 pm
Why not build the FTP portion of the process into the SSIS package? If you store the FTP parameters in a table, you can query the contents of that table in SSIS and store those in an object variable. You can then use a for each container to iterate through the recordset in the object variable and run the FTP process against each host.
You can then add any file processing data flows into the same for each container and you've just dynamically processed files from each host in your ftp host table.
Alternatively, you could consider using PowerShell to do the FTP process and SSIS to handle the files once they've been brought down to your network.
October 8, 2015 at 7:12 am
Thank you John! Using SSIS seems like the way to go! Can I do sftp using SSIS as well?
October 8, 2015 at 8:42 am
I don't think the FTP task allows for SFTP, but I'm not 100% on that. If it does not, a Google search for SSIS SFTP gives you a number of reliable options.
October 8, 2015 at 10:26 am
Hi,
I have created a successful FTP Task.
When you say, "you can query the contents of that table in SSIS and store those in an object variable. You can then use a for each container to iterate through the recordset in the object variable and run the FTP process against each host."
How do I create the Object Variables?
Also, to do the For Each Container, do I just drag the FTP Task within the For Each Container?
October 8, 2015 at 10:35 am
Here's a walkthrough that shows exactly what I'm talking about.
https://www.simple-talk.com/sql/ssis/implementing-foreach-looping-logic-in-ssis-/
This would require that you've stored all of your FTP connection info in a table.
October 8, 2015 at 11:19 am
Thank you very much!
I am at the point of adding the FTP Task to the Fro Each Loop.
What my end goal is to pull the FTP parameters FTP Server, FTP User, FTPPwd, FTTPPath that are in my table and have the task check for files for each site (we have multiple FTP sites we want to pull information from).
When I go into FTP Task Editor, I do not see how I can use these values to call different sites. It appears I can only "hardcode" an one FTPConnection. Do I need to do something different (e.g. create a Data Flow Task for each FTP Site?)
October 8, 2015 at 11:35 am
Right click the FTP task and go to Properties. This brings up the Properties window. Look under Misc for Expressions...click on the ellipse in the Expression value box. This should bring up the property expressions editor. Here you can select any property and create an expression for it. You'll notice that the expression editor allows you to use variables in the expressions. This allows you to take the variables that you've populated in your For Each loop and use them as the connection parameters. This will tie the FTP task to your variables that you've populated for that iteration of the For Each container. Sounds like you're close...
October 8, 2015 at 1:11 pm
Hi, So in my case I believe I will need multiple variables is this correct? I believe I will need variables for each FTP Server, FTP User, FTPPwd, FTTPPath.
My query looks like this:
SELECT VID, VendorName, FTPServer, FTPUser, FTPPwd, FTPPath
FROM dbo.FTPVendorInfo
WHERE (Active = 1)
For some reason when I try to add my variables to the expression, I am getting the error message, :Error at FTP Task [FTP Task]: The connection is empty. Verify that a valid FTP connection is provided.”
Also, when using expressions, which is the correct property to use?
Thanks again!
October 8, 2015 at 1:42 pm
You'll need a variable for each of the column names in your select list. So your initial query will load your object variable with the dataset results from the query. The For Each Container will be set up to enumerate through the object variable. Then, on the Variable Mappings tab of the For Each Container's setup, you'll assign the column values to your variables.
Then, the variables will be used in the expressions to configure your FTP Task. As far as which properties to configure, I looked at this in more detail and it's the FTP connection Manager that you'll want to configure with expressions. This is where you'll see things like user/password, FTP host, etc. When you set up your FTP task, it will force you to choose a FTP connection manager or create one, this is the connection manager that you'll configure with expressions/variables.
October 9, 2015 at 9:49 am
Thanks again for all your help this far! My apologies as I know this one is like pulling teeth.
I have attempted to configure the expression with the FTP Task Editor to parameterize my FTP variables. When I go to FTP Task Editor --> Expressions I have added:
Connection = @[User::FTPServer]
RemotePath = @[User::FTPPath]
How do I include the username and pwd to this?
October 9, 2015 at 10:48 am
It is the FTP Connection Manager that needs configured, not the FTP task. My last post discussed this. If you look at the expressions on the connection manager, you'll see username, password, ftp host, etc.
October 9, 2015 at 1:30 pm
Hi, When creating the FTP Connection Manager, I add the following fields to my ConnectionString expression:
@[User::FTPServer] + (DT_WSTR,10)@[User::FTPPort] + "." + @[User::FTPUser] + "." + @[User::FTPPwd]
When I click Evaluate Expression the return is: :21..
However, when I run the FTP Task after configuring the FTP Manager, I get the following message:
I get the error message:
[Connection manager "FTP Connection Manager"] Error: An error occurred in the requested FTP operation. Detailed error description: The server name or address could not be resolved.
When I hardcode my parameters I am able to connect to the FTP site.
Any ideas/suggestions?
October 13, 2015 at 12:02 pm
Hi,
I am hoping someone may be able to help me with this issue.
When creating the FTP Connection Manager, I add the following fields to my ConnectionString expression:
@[User::FTPServer] + ":21." + @[User::FTPUser] + "." + @[User::FTPPwd]
When I click Evaluate Expression the return is: :21..
However, when I run the FTP Task after configuring the FTP Manager, I get the following message:
I get the error message:
[Connection manager "FTP Connection Manager"] Error: An error occurred in the requested FTP operation. Detailed error description: The server name or address could not be resolved.
Any ideas/suggestions on what I need to do to get this to work? It seems as my variables are not properly passing?
October 13, 2015 at 2:19 pm
Hey Meatloaf...sorry for the delay, I was tied up Friday and out yesterday. So you've got two things that could be wrong here. Either you placed your expression on the wrong properties (which can be tested by hard-coding the values in as it appears you've done) or that your variables may not contain the values that you are expecting.
To test out your variable values, you'll want to set a breakpoint on the data flow task that contains your FTP connection. I'm assuming that this data flow task is inside of your For Each container. Set the breakpoint and execute the package. When the package stops at the breakpoint, look for a tab at the lower left corner of Visual Studios called Locals. Enter this tab and expand the Variables container. Inside here, you'll see the current values of all of your variables. You can scroll down through here to see what your variable values are set to for the variables that you are using inside of your expressions.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply