Official CozyRoc Blog

December 21, 2007

FTP over SSL in SSIS

Filed under: SSIS, Tips — COZYROC @ 9:15 am

We have implemented SSIS script task sample demonstrating FTP over SSL operations. It depends on FtpWebRequest for FTPS support and Script Task Plus for UI implementation. You may check it here

Advertisements

November 13, 2007

Implementing custom SSIS Control Flow Task in C++

Filed under: SSIS, Tips — COZYROC @ 11:41 pm

This topic is a work in progress. It will stay this way until Microsoft SSIS product team decides to document their native interfaces for third-parties to use. If you want to develop custom SSIS task in C++, you have to load yourself with patience and be ready to spend some time in the debugger and with Reflector. The undocumented native interfaces for implementing SSIS Control Flow Task can be found by opening the COM type library information in the DTS.dll library. The following interfaces represent roughly the base class Task from which all custom SSIS task must inherit:

There is one more interface IDTSComponentPersist90, which has to be implemented for serializing your task. After you are done implementing these four interfaces, you have to make your task visible to the SSIS framework. This is done by including additional information in the registry for your COM object. The additional registry keys under the specific task CLSID are:

  • DefaultIcon – Must point to an icon resource located in your DLL. Should have the format <your library>.dll,0 where 0 is my guess the index of the icon resource.
  • Programmable – I’m not sure if this is mandatory, but I saw other stock SSIS tasks using it.
  • Implemented Categories – Should contain {CD0DB111-958E-440D-9111-7D70D1490063} subkey. Categorizes your COM object as SSIS task?
  • DTSInfo – Various additional SSIS task metadata.
    • Description – task description.
    • ResourceFile – string resources containing additional task descriptions. Should have the format <your library>,10,20 where 10, 20 are string resource ids.
    • TaskContact – information describing where the SSIS originated from, copyright info, etc.
    • TaskType – Describes task compatibility. For SQL 2005, it should say DTS90.
    • UITypeName – Task user interface type reference.

After you compile and open an SSIS package, you should be able to browse and find your custom SSIS Control Flow Task listed.

p.s.

We haven’t spent time yet developing custom SSIS Data Flow Components, but you may check the type library in the DTSPipeline.dll library.

July 2, 2007

SSIS project (dtsx) serialization hack

Filed under: SSIS, Tips — COZYROC @ 3:13 pm

If you are interested in SSIS project deserialization, here is a brief summary of the steps you need to follow to install your own deserialization routines (please have Reflector in hand because most of the details below are undocumented):

1. Make sure you have reference to the following libraries:

  • Microsoft.DataTransformationServices.Design.DLL
  • Microsoft.DataWarehouse.VsIntegration.dll
  • Microsoft.DataWarehouse.DLL

3. Install handler for application.Events.SelectionEvents::OnChange event.
4. Check selected item is of type ProjectExt.
5. Install your own ISettingsService service provider class. You can get access to the service container and current ISettingsService service provider with the following code:

Hierarchy projHierarchy = project.GetType().InvokeMember(
   "ProjectHierarchy",
   BindingFlags.GetProperty | BindingFlags.NonPublic | BindingFlags.Instance,
   null,
   project,
   new object[] { } ) as Hierarchy;
IServiceContainer serviceContainer = (IServiceContainer)projHierarchy.GetService(
   typeof( IServiceContainer ) );
ISettingsService ssOld = (ISettingsService)serviceContainer.GetService(
   typeof( ISettingsService ) );

6. Override ISettingsService::GetSetting method and check:

  • settingContext is “Serialization\SerializableComponents”
  • scope is SettingsScope.Machine
  • settingName is “dtsx”

If you have a match, you have to return a type to a dummy class whose only purpose is to provide attributes, used by the infrastructure internally. The default implementation is called PseudoPackage. Attribute your new dummy class with DesignerSerializer attribute and parametrize it with your own serialization class. It has to inherit from DtrDesignerSerializer class.

7. Override DeserializeComponent and SerializeComponent. These are the methods, which will be called for each SSIS project task during serialization. Voila! Now you are the master of the universe.

Create a free website or blog at WordPress.com.