Having trouble getting connections

I have a custom Data Flow Destination Adapter that is looking for a particular type of ConnectionManager. I want to check that this connection exists in the Validate method. I first checked the ComponentMetedata.RuntimeConnections, but it was was an empty collection. I am guessing that it gets populated at runtime. Is there anything available for to check at design time?

Another way of asking this would be, Is there a way to programatically select which ConnectionManager gets assigned to a RuntimeConnection? Normally, this is done on the Connections tab of the Advanced Editor.

Thanks,

Graham

You can assign a connection manager at design time to your adapter and check that it is the correct type in the Validate() method. You will need to let your component know that it is expecting a connection manager.

Allan

|||I realize that I can do that by opening the advanced editor on my destination task and selecting the connection; but I dont want to do it that way. I want to Programmatically look for a connection of a specific type. Below is a code snippet of what I tried to do, but that doesnt seem to work. The connection is not set.

//this takes place in the onload handler for my DestinationAdapter form
//connections is passed in from the UI class that implements IDtsComponentUI
ConnectionManager connMan = null;
foreach (ConnectionManager cm in connections)
{
if (cm.InnerObject.GetType() == typeof(ProfileConnectionManager))
{
connMan = cm;
}
}
profConnMan = ProfileConnectionManager.FindProfileConnection(connections);
dtsComponentMetaData.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(connMan);

|||Is there a reason you don't just get the value of ConnectionManagerType on the properties collection? Also, you're attempting to use a managed code idiom for a connection that may not be managed. Most of the connection managers are native. Is this a custom connection manager? I don't know about the ProfileConnectionManager.|||

You may also want to take a look at IDtsConnectionService.GetConnections and IDtsConnectionService.CreateConnection. Useful for writing UIs. They all work around the connection type that Kirk mentions.

|||ProfileConnectionManager is a custom connection that I have written. I want to allow a custom data flow destination adapter that I have written to look for a ProfileConnectionManager and if one is defined, add it to my CustomDestinationAdapter.ComponentMetaData.RuntimeConnections collection.

In normal situations, this is done at design time by openning the advanced editor and selecting the connection manager from a dropdown. In my situation, I am allowing only one ProfileConnectionManger to be created. So if it exists, I dont want the user to have to select it from the advanced editor; I want that to be done programmatically by me.

When you refer to properties collection, are you saying I should add a property to MyCustomDestinationAdapter.ComponentMetaData.CustomPropertyCollection and assign my custom connection as its value?

thanks,
Graham|||

I would use GetConnections, albeit in a UI, something like this-

foreach (ConnectionManager connectionManager in _dtsConnectionService.GetConnectionsOfType(connectionType))
{
comboBox.Items.Add(connectionManager.Name);
}

connectionType is the string that identifies the type of connection I want. For example if I want ADO.Net SQLClient connections I need to use-

"ADO.NET:System.Data.SqlClient.SqlConnection, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089";

It is a bit annoying that I have to use the fully qualified name, but that's what works.

The ConnectionManager also has a CreationName property which I use often for validation, and Kirk suggests a ConnectionType property, so using this you could loop as you where above, and check such a property to see if it is your connection. What you should not do is try and get a .Net Type object for the connection manager, as not all of them are .Net objects.

sql

Havent been able to execute a package on the server

Hello, I created a package on my machine, it deletes some files, then delete some rows, then copy files from a destination to a source and then process all those files and insert rows in a table, really simple.

When I click execute in VS 2005 it executes perfectly. (it took about 45 seconds because there are many files)

I connected to integration services and imported the package then I did the two following things.

1-Right click run package and it executes normally but it took less than 1 second, and when I saw the destination folder there were no files in there so it didnt do anything)

2. I created a job and on the first step I put to execute that package. I then executed the job and the same thing happens, it executed without errors but it took less than 1 send and when I saw the destination folder there were no files in there so it didnt do anything).

I noticed that the Integration services project has a property for creating a deployment utility, I changed this property to true but I dont know how to make the deployment utility.

Maybe the problem was that when I Imported the package,, the package was on another machine on my LAN?

Can the package on the server even see the files? The directories have to be the same, and the user account for the SQL Server service must have rights to that directory as well.

When you execute it on your machine, it's using your user account and currently accessible folders. When the package is promoted to the server, you will be using a different account.|||enable package logging to get more details of the execution. The package may be failing...|||

Hello, I finally could upload the package, and from the management studio interface I ran the package and it worked perfectly.

When I created a job, with one step only to execute that package, the job fails.

When I go to history it doesnt give me any details of what failed on the package or in the job

Date 24/01/2007 12:30:28
Log Job History (Carga datos ACH)

Step ID 1
Server ATLANTE\SQL2005
Job Name Carga datos ACH
Step Name Carga de datos de ach
Duration 00:00:02
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0

Message
Executed as user: ATLANTE\SYSTEM. The package execution failed. The step failed.

Maybe is the user that it tried to execute the package as?

|||

Luis Esteban Valencia Mu?oz wrote:

Hello, I finally could upload the package, and from the management studio interface I ran the package and it worked perfectly.

When I created a job, with one step only to execute that package, the job fails.

When I go to history it doesnt give me any details of what failed on the package or in the job

Try using SQL Server Agent CmdExec job step sub-system so that Dtexec can be employed. This will cause more detailed error messages to be stored in the job history. It is also useful to implement the job step log.|||

Hi Luis,

This could be due to the ProtectionLevel setting for the individual packages - that's my guess.

By default, these are set to EncryptSensitiveWithUserKey. This means as long as you personally execute the packages, your credentials are picked up and the packages execute in your security context. This is true even if you're connected to a remote machine, so long as you're using the same AD credentials you used when you built the packages. Does this make sense?

When the job you created executes, it runs under the SQL Agent Service logon credentials.

My understanding of the "Sensitive" in EncryptSensitiveWithUserKey may be flawed, but I cannot find a way to tell my SSIS package "hey, this isn't sensitive so don't encrypt it." Although this sometimes gets in the way I like this feature because it keeps me from doing something I would likely later regret. Anyway, my point is the Sensitive label is applied to connection strings and I cannot find a way to un-apply it (and I'm cool with that).

One of the first things an SSIS package tries to do (after validation) is load up configuration file data. This uses a connection, which (you guessed it) uses your first connection string. Since yours are encrypted with your own personal SID on the domain and this is different from the SID on the account running the SQL Agent Service, the job-executed packages cannot connect to the configuration files to decrypt them.

There are a couple proper long-term solutions but the one that is simplest to implement is to use the EncryptSensitiveWithPassword Package ProtectionLevel option and supply a good strong password. You will need to supply the password when you set up the job step as well. But this should allow the package to run without needing your security credentials.

Note: You will also need this password to open the packages in BIDS (or Visual Studio) from now on... there's no free lunch.

Hope this helps,

Andy