Unable to modify the Sql query in lookup component by programatically

• Sunday, March 03, 2013 6:55 PM

   

   
  

  0

  Hi all

  I am using the following coding snippet to modify the sql generated query on the lookup component by programmatically,

  the code executes without any exception, but the custom query has not set in the lookup component.

  CANC

  IDTSVirtualInputColumn100 virtualColumn = lookupVirtualInputColumns["ChartOfAccountId"];
              // Cache Type - Full = 0, Partial = 1, None = 2
              LookupDesignTimeComponent.SetComponentProperty("CacheType", 2);
              LookupDesignTimeComponent.SetComponentProperty("SqlCommand", "SELECT *  FROM test1");
                         
              IDTSInputColumn100 inputColumn = LookupDesignTimeComponent.SetUsageType(lookupInput.ID, lookupVirtualInput, virtualColumn.LineageID, DTSUsageType.UT_READONLY);
              LookupDesignTimeComponent.SetInputColumnProperty(lookupInput.ID, inputColumn.ID, "JoinToReferenceColumn", "testid");
  
              LookupDesignTimeComponent.AcquireConnections(null);
              LookupDesignTimeComponent.ReinitializeMetaData();
              LookupDesignTimeComponent.ReleaseConnections();
              LookupComponent.ValidateExternalMetadata = false;
  
              //LookupDesignTimeComponent.SetInputColumnProperty(LookupComponent.InputCollection[0].ID, LookupComponent.InputCollection[0].InputColumnCollection["ChartOfAccountId"].ID,
              //       "JoinToReferenceColumn", "ChartOfAccountId");
  
              LookupDesignTimeComponent.SetComponentProperty("SqlCommandParam", "select * from (SELECT *  FROM dbo.test1) [refTable] where ([refTable].[testid] =0 or  [refTable].[testID] = ?)");
  
              string ss = "#26;";
  
              String ParamProperty = String.Format(@"""Parameter0"",{{{0}}};", ss);
  
              LookupDesignTimeComponent.SetComponentProperty("ParameterMap", ParamProperty);
  
              LookupComponent.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(cnOLEDB);
              LookupComponent.RuntimeConnectionCollection[0].ConnectionManagerID = cnOLEDB.ID;
  

  CAN ANYBODY HELP ME

  Thanks

  Rama

  • Reply
  • Quote