Dynamic Package Generation Sample issue with Excel Destination

May 16, 2010 at 7:42 PM


I would first like to thank you for the wonderful sample for dynamic SSIS generation.

I am using SQLServer 2005 sp3 in Visual Studio 2005 and .Net Framework 2.0. I am trying to export data from SQLServer Source to Excel Destination.

I am using the following to setup the destination connection.


       packageGeneration.DestProvider = New ExcelConnectionTypeProvider(True, "c:\test2.xls",  "Sheet1", "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\test2.xls;Extended Properties=""EXCEL 8.0;HDR=YES"";", True, String.Empty, String.Empty)


The test2.xls file is getting created successfully along with first row with the column header information. However i am getting the following error.



System.Runtime.InteropServices.COMException: Exception from HRESULT: 0xC0048021   at Microsoft.SqlServer.Dts.Pipeline.Wrapper.CManagedComponentWrapperClass.ProvideComponentProperties()   at FileUploadLibrary.ExcelConnectionTypeProvider.AddDestAdapter(IDTSPipeline90 pipeline, ConnectionManager destConnMgr, IDTSDesigntimeComponent90& destDesignTimeComp) in C:\test\Excel.vb:line 105   at test.SimplePackageGenerator.ConstructPackage(Boolean createNew) 


The code in the Public Overloads Overrides Function AddDestAdapter method is as follows. I am unsure which property is being setup incorrectly. Unfornately the error message does not provide any clue to the problem location.


 Dim destComp As IDTSComponentMetaData90 = pipeline.ComponentMetaDataCollection.[New]()      

destComp.ComponentClassID = "Excel" 'EXCEL_DEST_GUID        

destComp.ValidateExternalMetadata = False        

destComp.Name = "Dest"      

destComp.Description = "DESt"        

destDesignTimeComp = destComp.Instantiate()      




I tried the following link http://blogs.msdn.com/mattm/archive/2009/08/03/looking-up-ssis-hresult-comexception-errorcode.aspx
to setup the error message but it seems that DtsConvert.GetExtendedInterface is not available in SQLServer 2005.

Please advise for any ideas to resolve the issue. Thanks in advance.






May 17, 2010 at 4:54 AM

Hi Sandeep,

The sample will only work with 2008 - you'll probably also need to modify the GUIDS used for the source and destination components.

Take a look at the DtsConvert class. Instead of "GetExtendedInterface", there will be "ToXXX90()" calls (ex. ToConnectionManager90).