SSIS package configurations - newbie question

Question

SSIS package configurations - newbie question

cafescott

SSCarpal Tunnel

Points: 4724
More actions
April 17, 2012 at 7:47 am

#260152

Hello,
I have a simple SSIS package that does the following:
1. Reads a view of Adventure Works data,
2. Writes a CSV with this data to a network location,
3. FTPs the CSV from the network to a FTP server.
4. Send emails upon success or failure.
Having got this far I have been trying to figure out how to store the FTP credentials in a SQL server table. It seems that most of the examples people are offering of Package Configurations use config files or environment variables. I don't wish to use either.
I have created variables to store the data that I'd like to read from the table. I have created a Package Configuration specifying SQL server and what values I wish to store. The wizard created a table (SSIS_Configurations) with this information.
Unfortunately, I can't figure out how to get the FTP connection manager in SSIS to read my variables (which I am assuming get populated by the SQL server table). The FTP connection manager is a simple dialogue with no apparent way to indicate that my user name and password should come from variables.
(btw, I am dismayed at the lack of documentation there is out there concerning Package Configurations for newbies.)
Even though this is technically a Visual Studio question I am hoping that someone out there can shed some light on this. Thanks!

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

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

HowardW One Orange Chip Points: 29109 More actions · Answer 1

There are a few options here.

If you're using variables because you want to reference the value of the variable in multiple locations (e.g. a servername etc. that may be used for a connection string and various other things), then you need to use the Package Configurations to set the value of the variable, then create an expression on each control flow item/connection manager that you want to pick up that variable.

For example, right click on your FTP Connection Manager and go to properties, click the elipse for Expressions, then find the right property (ServerPassword I think) and set it's value to the variable. Then the Variable is assigned its value from the configuration and the connection manager from the expression.

If you just want to use a particular configuration in one place though, the simple way to do this is to ditch Variables entirely and simply add the value of the FTP Connection Manager's Password property to the configuration file. Then it will pick it up directly from the config.

A 3rd option is to completely ditch the built in Package Configurations and just assign variables from any arbitrary SQL table by using an execute SQL task and output parameters to assign your variable as you wish (obviously also using an Expression to assign that value to a connection manager/control flow item)

cafescott SSCarpal Tunnel Points: 4724 More actions · Answer 2

Thanks for the reply, Howard! When I get a chance I'll take a look at what you wrote in detail.

cafescott SSCarpal Tunnel Points: 4724 More actions · Answer 3

Howard, thanks again for the tip. I'm using an Execute SQL task and using it with a table I created on the SQL server.

I've successfully created an expression that connects the FTP connection manager to the user name variable which the SQL task fills. However, I'm perplexed as to how to set the FTP password using the same method. I don't see the password as a system variable (unlike the System::UserName).

Can the FTP password be assigned to the FTP Connection manager? If so, what is the name of the object that gets assigned the variable that I created?

thanks.

HowardW One Orange Chip Points: 29109 More actions · Answer 4

Ignore system variables. They don't have anything to do with your FTP task username. The values aren't stored in variables, they're stored in the properties of the connection manager (this is an entirely different thing), so you have to create your own variable, assign it in the Execute SQL Task, then use an expression on the Connection Manager.

However, I've steered you slightly in the wrong direction here (I've just tried myself) as you can't set the value of the serverPassword property of an FTP task in an expression - this is because it's deemed "sensitive" by SSIS.

So, you can't easily use an arbitrary table to set the password property without breaking out into a script task. You could set the UserName, but it's not much good without the password, so I'd recommend going back to Package Configurations (storing them in SQL Server).

Edit your Package Configuration, and in the objects list, drill down to your FTP Connection Manager and you'll see the ServerUserName and ServerPassword properties. Tick these and they'll appear in the config table and read the values from there.

cafescott SSCarpal Tunnel Points: 4724 More actions · Answer 5

Howard, thanks again. I got the FTP connection manager to read the user name and password from my config table via 'Package Conguration'. I have verified that both properties are being set.

thanks!