Dynamic Package Generation Sample issue with Excel Destination

May 16, 2010 at 6:42 PM

Hello,

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()      

destDesignTimeComp.ProvideComponentProperties()

=====================

 

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.

-Sandeep

 

 

 

 

Coordinator
May 17, 2010 at 3: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).

~Matt