CManagedComponentWrapper.AcquireConnections Is Not Working In Case Of Remote Server

Hello Every one,

here to food for SSIS gurus,



I am preparing SSIS package programmetically using C#, in a web wethod, to perform fuzzy lookup and other transformations in to sql server data.



now situaltion is like this,



there are three system (independent m/cs)



m/c 1 :- webserver

m/c 2 :- sql database (sqlserver)

m/c 3 :- client (it can be any where , we are using onc click deplyment)



Now case is like this.



A user sitting at m/c no. 3 (a thin client) has one-click-deployed application, clicks the button "Create SSIS Package" , which sends this message to webserver (i.e. m/c 2 ) where C# code constructs the package programmatically,

there are three component inside package

1. oldedb Source (a sql server table -- sqlserver is at m/c no 3)

2. fuzzylookup component

3. oledb destination (a sql server table -- sqlserver is at m/c no 3)

Now 1 and 3 component of the package need to connect to sqlserver for initialization there metadata from actual tables.



here is code :

public IDTSComponentMetaData90 oledbDest = null;

oledbDest = dataFlowTask.ComponentMetaDataCollection.New();

oledbDest.Name = "Destination";

oledbDest.ComponentClassID = "DTSAdapter.OLEDBDestination.1";

CManagedComponentWrapper srcDesignTime = oledbDest.Instantiate();

srcDesignTime.ProvideComponentProperties();

oledbDest.RuntimeConnectionCollection[0].ConnectionManagerID = package.Connections[connName].ID;

oledbDest.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(package.Connections[connName]);



srcDesignTime.SetComponentProperty("AccessMode",0);

srcDesignTime.SetComponentProperty("OpenRowset",tableName);



// Tries to connect to sqlserver hosted at different m/c

srcDesignTime.AcquireConnections(null);

srcDesignTime.ReinitializeMetaData();

srcDesignTime.ReleaseConnections();





now the 3rd last line which says srcDesignTime.AcquireConnections(null);



tries to open the connection to the selserver (in order to get information abt actual table and its columns) which is installed at another m/c, and this is possible cause of error (i think !!)



there are two cases in one case i am getting an error and in second case it is working just fine..



case 1. If i use (local webservice) web service to create package it works.

case 2 if i use published (on local IIS ) webservice to create package, It fails (at the code srcDesignTime.AcquireConnections(null); )



so i think problem is related to some authorization, may be i am wrong !!1





Please help me.



thanks in advance

Pradeep



(I am really looking for a reply for MR. Jamie Thomson )