.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Accessing ActiveX Component functionality from SSIS

Posted By:      Posted Date: September 02, 2010    Points: 0   Category :Sql Server
Hi I have my SSIS Package, from which I need to access the functionality of an external application.  The external application have an API exposed in form of ActiveX Component.  I have to access that ActiveX components. Now, how to have that ActiveX components exposed to my SSIS package? What are the steps to access that ActiveX component ? Request you to reply at the earliest as this is a critical requirement.  Please note that I am very new to SSIS technology.  

View Complete Post

More Related Resource Links

how to access ActiveX DLL (.Net) functionality from SSIS package

Can anyone share the code or functionality to access the Active X DLL (.net com) from SSIS package.  What are the steps and prerequisities to do so...

Find and replacement SSIS Custom component issues

Hi All, I developed one custom component in SSIS  Find and Replacement I register the dll and copy and paste in to Pipleline component. but it's not visible in the Toolbox while choose Items Please suggest why it's not comming.. using   System; using   System.Collections.Generic; using   System.Linq; using   System.Text; using   Microsoft.SqlServer.Dts.Pipeline; using   Microsoft.SqlServer.Dts.Design; using   Microsoft.SqlServer.Dts.Runtime.Wrapper; using   Microsoft.SqlServer.Dts.Pipeline.Wrapper; using   System.Data.OleDb; using   System.Data.SqlClient; using   System.Windows.Forms; using   System.Runtime.InteropServices; using   System.Data; using   System.Collections; using   Microsoft.SqlServer.Dts.Runtime; using   Microsoft.SqlServer.Server; namespace   FindAndReplace { [ ComVisible(true)] [ DtsPipelineComponent ( DisplayName = "Find and Replace", Description = "Finding Find and replace", IconResource = "Microsoft.Samples.SqlServer.Dts.Find and Replace.ico", ComponentType = ComponentType.Transform )]   class FindAndReplace :PipelineComponent {   #region   ProvideComponentProperties   /// <summary>   /// </summary>   public ove

Output parameters in OLEDB Command component - SSIS 2008

I have a package that I developed in SSIS 2005 and recently ported to 2008. Everything runs great except an OLE DB Command component that calls a stored procedure that uses output parameters. When I try to run this, I get this error: Error: 0xC0202009 at Data Flow Task, OLE DB Command [100]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "Syntax error, permission violation, or other nonspecific error". Error: 0xC004701A at Data Flow Task, SSIS.Pipeline: component "OLE DB Command" (100) failed the pre-execute phase and returned error code 0xC0202009. When I do the same thing in SSIS2005, it works fine. So I setup a new package with 1 data flow. In that data flow, I added a source component, an OLE DB Command, and a destination.  I then tested this with a stored procedure that took no parameters. It worked fine. I then tested it with a stored procedure with 1 parameter defined as OUTPUT. This generated the error.   Is there an issue with output parameters in an OLE DB Command in SSIS 2008?   Thanks!http://bobp1339.blogspot.com

Custom SSIS Data Flow Component Not Showing in Toolbox or GAC

Hello - I have created a very simple data flow component for SSIS (Actually, I am following this example:  http://www.microsoft.com/downloads/details.aspx?familyid=1C2A7DD2-3EC3-4641-9407-A5A337BEA7D3&displaylang=en).  However, when I register the DLL to the GAC, I am unable to find the assembly in C:\Windows\Assembly - even though the GACUTIL says "Assembly Registered Successfully".  Furthermore, after copying the DLL to the PipelineComponents folder for SSIS (C:\Program Files (x86)\Microsoft SQL Server\100\DTS\PipelineComponents), it does not show in the "Choose Items . . ." dialog box of SSIS.   I am running SQL Server 2008 Dev edition, Visual Studio 2010 with .NET 4.0, and Windows 7 Enterprise 64-bit edition.  Any assistance/thoughts would be appreciated. Thanks!

SSIS multiple data flows accessing the same raw file

I have multiple data flow tasks accessing the same raw file at virtually the same time.  I would like to know if the data flow tasks lock the raw file during read?  Can multiple data flow tasks read the same raw file at the same time.  I'm currently developing with a very small data set, but I would like to know if I should be concerned with the possibility of the data flow tasks competing for access to the same raw file. Thanks.

SSIS script component bugs at around 10k rows

I'm trying to load a dimension table from a tab delimited file to a parent-child table. I have made a script like this: public override void Input0_ProcessInputRow(Input0Buffer Row) { String level0 = null, level1 = null, level2 = null, level3 = null, level4 = null, level5 = null; while (!Row.EndOfRowset()) { if (Row.Description_IsNull || Row.Description == "") { if (!Row.NextRow()) break; continue; } if (!Row.Level0_IsNull) { Row.ID = Row.Level0; Row.ParentID = null; level0 = Row.Level0; Row.Description = Row.Description; } else if (!Row.Level1_IsNull) { Row.ID = Row.Level1; Row.ParentID = level0; level1 = Row.Level1; Row.Description = Row.Description; } else if (!Row.Level2_IsNull) { Row.ID = Row.Level2; Row.ParentID = level1; level2 = Row.Level2; Row.Description = Row.Description; } else if (!Row.Level3_IsNull) { Row.ID = Row.Level3; Row.ParentID = level2; level3 = Row.Level3; Row.Description = Row.Description; } else if (!Row.Level4_IsNull) { Row.ID = Row.Level4; Row.ParentID = level3; level4 = Row.Level4; Row.Description = Row.Description; } else if (!Row.Level5_IsNull) { Row.ID = Row.Level5; Row.ParentID = level4; level5

SQL Server 2008 Script Componant Error - [SSIS.Pipeline] Error: component "Script Comp Name" (48) fa

Hi All,   I am facing one strange issue in SSIS 2008. I have developed one SSIS package which is importing excel data into SQL Server 2008. This Package contains some Scirpt componants. This package is working fine on Windows XP machine but when I am trying to run on Windows 2003 server it gives me error "[SSIS.Pipeline] Error: component "SCR RDSTableRelation" (48) failed the post-execute phase and returned error code 0x80004002". Surprising thing is that if on windows server 2003server  if I drag a new script componat and paste the previous script code itself then it works fine. Even if I copy-paste the existing script componant and give source-destination connectino to this new script componant then also it works.

Activex component not showing up in toolbox


I wish to have an ActiveX component on my webpage. When I choose Item and add the COM object, the control is not added on the toolbox? How Can I manually add the activeX component.

accessing package level variables in a Script Component in 2008 version


how to access the package level variables within a script component in SSIS 2008

How to generate an excel and send it as email attachment in SSIS 2005 Script Task Component?


I am using a "Script Task" component. I am trying with following three simple steps:

1) Obtain the excel content in HTML

Dim reportInHtml As String = GenerateHTMLReport()

2) Generate an excel with below code:

Dim message As MailMessage

message = New MailMessage(fromAddress, toAddress, subject, String.Empty)

Using memoryStream As MemoryStream = New MemoryStream()

        Dim contentAsBytes As Byte() = Encoding.UTF8.GetBytes(reportInHtml)
        memoryStream.Write(contentAsBytes, 0, contentAsBytes.Length)
        memoryStream.Seek(0, SeekOrigin.Begin)

        Dim contentType As ContentType = New ContentType()
        contentType.MediaType = "application/ms-excel"
        contentType.Name = "Excel_Report.xls"

        Dim excelAttachment

SSIS Custom Destination Component colum mapping issue


Hi ,

I am facing the below issue.

I am developed a custom destination component.I have custom UI also to set some properties.

Once i save the component and do the mapping in the Show Advance Editor Colum Mapping.This is working fine.

The problem starts Next.If I edit the component and do some changes  and say Ok ,In the output colum mapping all the mappings which i have already been set is getting removed.Then again i have to remap it.Ho to keep the existing mapping if we edit the component?

need help to solve this issue.

Bug when using SSIS OLE DB Destination Component using the IBM DB2 v9.# driver

I thought I woud pass on some helpful information with a problem we been experiencing writing to DB2.

It seems that the SSIS OLE DB destination component experiences problems using the IBM DB2 Driver during validation when writing to large DB2 tables ~ 4 million rows. Usually results in a "out of memory" error. We have open a case with Microsoft.  

Until this can be fixed there are several alternatives 
1. If you want to use the OLD DB Destination Component create a view on the Table and add a Where 1=2 so no records return 

2. Use the OLE DB Command component  .. Insert into table (col1,col2) values (?,?)  - this is actually 2 x faster

3. CozyRoc has a excellent DB2 Destination custom component that uses IBM Db2 driver bulk copy API it's 20x faster - very impressive! http://www.cozyroc.com/ssis/db2-destination

hope this helps..


Creating 64-bit interop dlls from an ActiveX component

Hi there,

I have generated a 64-bit version of an old ActiveX component. I now need to create the appropriate interop dlls to be able to use this component from some managed code. Here's what I've tried:

> tlbimp myDll.ocx /out:myDllInterop.dll /machine:x64

This generates the error:
TlbImp : error TI0000 : A single valid machine type compatible with the input type library must be specified

From this error, it appears that I might not have a 64-bit activeX component. But when I check it using DependencyWalker, it says it IS a 64-bit dll. Is it possible that the typelib info inside the ocx is only 32-bits. If so, how can I verify that? OleView doesn't give me enough info about the bit-ness of the tlb. And more importantly, how can I force a 64-bit typelib info to be embedded inside the ActiveX control?

I've also tried aximp, but it doesn't seem to have any option to specify a target machine type at all

Do I have to write my own tlbimp/aximp using the TypeLibConverter class? Can anyone point me in the right direction please?

ActiveX component not displayed in WPF


Hello guys,

I need to display a PDF file inside of my application in Adobe's ActiveX coponent. In addition, I need to put it in my custom control. I use WindowsFormsHost to do this:

<WindowsFormsHost x:Name="WinFormsHost" Margin="1">
  <ctrl:PDFViewer x:Name="pdfViewer" />

And this is the source code of my PDFViewer class (there is a AxAcroPDF named axAcroPDFView contro

Accessing a flat file/folder via SSIS that is out of my domain


I'm trying to copy a file to a desitnation in an SSIS package that requires a username and password in order to connect. I am doing this in VB Script task and have tried this:


Dim ServerShare, UserName, Password As String
Dim NetworkObject, FSO As Object
ServerShare = \\someserver\sharename$

SSIS are there component to use query and then transform the table


Are there any components that I can use for query for instance after the flat file source is set up to grab all the values into table(column, column) then using the query to filter it out to transform data??


For instance my query look like this


                      LEN(CHGCURRQ1), 1) = '0' THEN CONVERT(int, SuBSTRING(CHGCURRQ1, 1, 8) + '0') WHEN SUBSTRING(CHGCURRQ1, LEN(CHGCURRQ1), 1)

                      = '{' THEN CONVERT(int, SuBSTRING(CHGCURRQ1, 1, 8) + '0') WHEN SUBSTRING(CHGCURRQ1, LEN(CHGCURRQ1), 1) = 'A' THEN CONVERT(int,


                      8) + '2') WHEN SUBSTRING(CHGCURRQ1, LEN(C

Error while trying to assign a value to a Read Write variable in SSIS package script component



       I am trying to develop a SSIS package which will read the records from the flat file and insert them into a destination table. I have some validations written in script component. I have declared two Read Write variables with package level scope. when i try to assign a value to the variable in the script component and run the package, the package throws me an error "The collection of variables locked for read and write access is not available outside of PostExecute".


What should be done to over come the problem please help me on this regard 




ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend