November 21, 2006 at 1:46 pm
I had the same problem with my FTP Task. The problem is that the package views the FTP password as "sensitive information" and the default setting is "EncryptSensitiveWithUserKey". This setting will only allow the user name that created the package to run it successfully.
When you create the job step to execute the package, the package is not running under your user key.
The workaround that I was able to implement was to change the encryption to "EncryptAllWithPassword". Right above that property, change the PackagePassword by clicking on "..." and inputting a password. Now, whenever you open the package, you will need to have a password in order to edit it. To ensure that this functions properly, close the package after changing the setting, re-open it to edit and it should prompt you for the password. Enter in the password for the FTP connection and then re-save it and close.
Within the job step, when you try to edit the command line, it should prompt you for the package password. Once you enter the package password, it should add the following switch to the command line "/DECRYPT <password>", where <password> is your package password. The only issue here then is that the command line is not encrypted, but I satisfied for now to have the package working properly as a job.
I was able to successfully execute my package, which includes an FTP upload, with no problems from the SQL Server Agent account by using this methodology.
December 4, 2007 at 7:58 am
How do you pass an FTP connection manager password in a configuration file?
December 4, 2007 at 9:52 am
Ahmet Akin (6/29/2006)
The Package works really fine, when i run my Package local, the flatfiles had been sent successfully to the FTP-Host.
But as i start the Package from the Management Studio, i get the same failure message as described above.
Do i also have to type in the password in theconnection string (connection manager)?
If package is deployed on destination server then Protection level must be set to EncryptAllWithPassword and password must be set for this package. Next this package must be deployed on this server with suppling with above password.
December 4, 2007 at 7:00 pm
Dear all, i had the same problem with the FTP Task not working. I realised that when the FTP Task does a listing, the unix box returns not only the file name but the timestamp as well.
Eg,
Problem 1
directory on the unix box
/files
File1.trg
File2.trg
When listing from FTP Task: remote directory = /files
"10:00 File1.trg"
"12:00 File2.trg"
Now, when the ftp task tries to get these files, its not found, or throws some error.
Problem 2
Passwords. Using a batch file to script the FTP is not a good solution, because at the end of the day, your password is clearly visible in plaintext in the script. Not a good solution. Using the package configuration file (*.dtsConfig) is useless as it too stores passwords in plaintext (till now, ssis hasn't implemented encrypted configuration files). Don't even think of using enterprise library with SSIS, you'll run into major headaches.
Solution
I've since given up on using the FTP task for my FTP, but we can still use SSIS's FTP plumbing and with some extra coding, make a properly platform independent and secure FTP function.
I've removed the error checking and variable assignment to make things easier to read.
Steps:
1. Create a FTP Connection Manager in your SSIS Designer 'RightClick in Connections > New Connection... > FTP'.
2. Create a variable to store the password.
Setup the package to use configuration file to store the password variable (and other information you need).
3. Fill in the FTP information as needed. 3.
4. Write a simple .net application that does string encryption. I won't put code for this here. You can google how to do this easily.
5. Use this tool to navigate the xpath of the dtsConfig file to password variable's value and encrypt it. This way, the password is not plaintext on the dtsconfig file.
6. Create a script task to take the password variable's (as readwrite) value and 'decrypt' it, remember to use whatever method was used to encrypt it in your separate .net app. Assign the decrypted password back to itself.
6. Create a script task to do the FTP functionality.
Public Sub Main()
'TODO: assign variables here...
dim password as string = dts.variables("vPassword").value.tostring 'the decrypted password
'Get instance of the connection manager.
Dim cm As ConnectionManager = Dts.Connections("FTPConnMgr")
'Set the password property to the decrypted password
cm.Properties("ServerPassword").SetValue(cm, password)
'create the FTP object that sends the files and pass it the connection created above.
Dim ftp As FtpClientConnection = New FtpClientConnection(cm.AcquireConnection(Nothing))
'Connect to the ftp server
http://ftp.SetWorkingDirectory(remoteDir) 'set the remote directory
Dim files(0) As String
files(0) = fileToGet 'eg. File1.trg
'Get the file
http://ftp.ReceiveFiles(files, localDir, True, True)
' Close the ftp connection
Dts.Events.FireInformation(0, context, "File " + fileToGet + " retrieved successfully.", Nothing, Nothing, True)
Dts.TaskResult = Dts.Results.Success
End Sub
So there you go. An ssis package that decrypts a encrypted password on a dtsconfig file, decrypts it at runtime, and doesn't use batch files that expose the password.
If you'd like to know how to do a batch get of all files in a directory, i can show in another post. Whenever you need to change the password, just use that separate .net app to modify the dtsconfig file. Nothing needs to be done on the package.
December 19, 2007 at 3:59 pm
Gunaseelan,
Thanks! That code snippet helped me solve an issue I had using the FTP Task to receive a file from a Tandem (HP Nonstop) server.
The FTP task wouldn't let me specify the path because it didn't start with a "/", so I used the Script you posted, popped in my path to the http://ftp.SetWorkingDirectory, and away it went. Script task is your friend 🙂
Rob
December 19, 2007 at 6:03 pm
You're welcome. Glad to be of help!
December 19, 2007 at 6:23 pm
Most of the problems people are experiencing with using the FTP task here have nothing to do with the FTP task settings. I suggest that everyone look up the protection levels for SSIS packages.
December 28, 2007 at 12:10 pm
If I remember correctly, SSIS has a bug when it comes to 'put'ting data on a UNIX server. The last I heard from MS is that a fix will be in the service pack...Now, when does the service pack come out?
February 8, 2008 at 3:15 pm
Hi, I having that problem now. I am creating a SSIS package from VS 2005, but i 'd already tried the Properties advise but it didn't work, it keeps showing me the Password was not allowed.
Please help this is for a very important project.
February 10, 2008 at 7:10 pm
How about giving some more details.
1 What properties are you setting on the connection manager?
2 Have you checked if your username has been blocked on the unix server due to past connection failures?
3 Can you connect with the same username /password with your usual windows FTP program?
4 Is the FTP Connection manager being configured using config files?
thanks.
February 10, 2008 at 7:19 pm
Are you trying to connect to a UNIX FTP server? If so, I've heard that there's a problem when you try to "put" files. "Get" works fine, but nothing where writing to the server; this is apparently a known bug.
Make sure that you enter the password for the server, change the encryption to either EncryptAllWithPassword or EncryptSensitiveWithPassword, enter a password for the solution and save the solution BEFORE you try to run.
February 11, 2008 at 7:02 am
Hi,
Thank you for your replies.
Well, here are my answers:
1. It is not a Unix FTP server it is a Windows server.
2. The user is not blocked.
3. Yes I use the properties of the Connection Manager, but the Encryptation stuff doesn't appear in the properties window.
4. The connection manager is not configured with a config file. How I do that, and where I can put .net Code to read it?
February 11, 2008 at 7:06 am
The encryption is one of the solution properties.
February 11, 2008 at 7:09 am
Hi folks,
I haven't read in detail everything thats been said in this topic, but here is what I do to set the FTP password at runtime. I store the password in a variable and set the variable through a configuration. I always set my package encryption to DontSaveSensitive, so that other developers and accounts can execute the package provided they have access to the config tables in SQL Server.
[font="Courier New"]Public Class ScriptMain
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Public Sub Main()
Dim FTPConnectionManager As ConnectionManager
'Set variable to an existing connection manager
FTPConnectionManager = Dts.Connections("FTP Server")
'Set connection manager property "ServerPassword"
FTPConnectionManager.Properties("ServerPassword").SetValue(FTPConnectionManager, Dts.Variables("FTPPassword").Value)
Dts.TaskResult = Dts.Results.Success
End Sub
End Class[/font]
Also, just wanted to add, that if you need full FTP functionality in your SSIS packages (Like ability to move a file from one FTP folder to another), you can use a script component implementing the wininet.dll
Kindest Regards,
Frank Bazan
February 11, 2008 at 1:51 pm
Hi everyone,
Finally, it worked, but I am fighting with a new issue.
Let me explain the picture.
I receive a Flat file from a FTP server (the last issue), then i should import the information to a Table where the record will be send to Stored procedure.
One of the field in the Flat File it is a Date, and when i try to insert the record the error message says:
The Column OrdDate cannot be inserted because the convertion between DT_Date and DT_TIMESTAMP is not supported.
It is importan to say that the column in the table is a datetime value and no a time stamp.
Please advise.
Viewing 15 posts - 16 through 30 (of 49 total)
You must be logged in to reply to this topic. Login to reply