Script Component With MS.Office.Interop.Outlook Reference
Feb 14, 2008
HiHo,
I have a problem and hopefully some can help me. I googled already al lot but I haven't found the answer.
I would like to build a Source-Script Component with Acces to MS outlook (I use the Book "SSIS" from Kirk Haselden as a Guideline).
I downloaded and installed "Microsoft.Office.Interop.Outlook" .
I can find it the GAC.
I also installed VSTO 2005 and even run the VSTO 2005 checker, which shows that everything is ok.
But when I try to add the reference to my project it isn't there.
While adding a reference I only see a ".net" tab.
From what I found so far there should be "com" tab, where I should find "MS.Office.Interop.Outlook".
When I launch Outlook, it takes forever for the program to finally open. With any inbound email, it stops processing whatever is underway at the time....and frequently there is a 2-3 second lag between keyboard input and what appears on the screen. SQLserver is usually consuming upwards of 1-gb of memory....help. Mike
I am writing a custom dataflow transformation component and I need to get the name of the preceeding component.
I have been trying to find a way to get a reference to the Package object, MainPipe object or IDTSPath90 object (connecting to the IDTSInput90 of my component) from my component because I think from there I can get to the information I want.
I'm very disappointed that OWC has been "deprecated" ie. ripped out - see http://msdn2.microsoft.com/en-us/library/ms143509.aspx . This is a major step backwards as we have got our users used to pivottables - they allow them to restructure and group summary data the way they want to save us admins from providing every possible grouping combination. Aaarrrgghhhh!
I tried the code from the above but even after restarting the SSRS and IIS no joy. The Apress Pro SSRS 2005 book also complains about the deprecation, and offers an augmented code for the rsreportserver.config file but it still doesn't work:
I appreciate another thread mentions poss necessity of previous DLL however we don't have old installations and old DLLs surely shouldn't be nec. Any further help greatly appreciated.
Has anyone made the OWC work under SQL 2005? Why are MS making this awkward?
i got this error msg when installing 2005 SQL Server components only on my system:
SQL Server Setup cannot continue because an earlier version of Office Web Components is installed on this computer. Uninstall Office Web Components, and then run installation again.
i could not find any info on this error. any ideas?
Today we moved from 32bit to 64bit computing environment. 64bit Window server 2003 and SQL 2005 are now our playing ground. And 32 bit Office 2003 is also installed. Excel in Office program plays important roll in our mission.
One of our routine task is that step 1. get Excel data files from other websites and step 2. extract specific data from them and step 3. converting them into some type of data that can be compatible with SQL
Our problem occured in step 3. To convert data, we realised taht 64 bit excel driver, which we believe is not yet available, is required.
My questions are:
1. Is 64 bit excel driver (of office 2003 or office 2007) available now?
2. Is there any way OR IS IT POSSIBLE to use 32 bit excel driver with 64bit SQL 2005? If possible, please enlighten me.
I would appreciate it very much if anybody answer my questions.
I have created this c# dll for one of my packages and I was planning on calling it from the script component, but for some reason when I try to call it I get the following error.
Could not load file or assembly 'VRS.Utilities.Dates, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null' or one of its dependencies. The system cannot find the file specified.
I've dropped the dll file in the WINDOWSMicrosoft.NETFrameworkv2.0.50727 folder and it shows up when I go to add the reference however when I try to implement it I get the error.
Finding this forum really useful...I wonder if you could help me with this.
I've got a very simple script component and I just want to use a Dataset in it.
When I declare the Dataset i get a warning and then consequently an error. herers an image grab of whats happening:
http://www5.webng.com/hopelist/error.jpg
If you can't see the image please let me know.
Essentially VS gives me a hint to add a required assembly which it needs....but when I click to add I get a 'Visaul Basic Compiler has encountered a problem and needs to close' type error.
Anyone got any idea whats going on and why I'm having such a hard time just accessing a Dataset??
Hello, I have a 'ForEach Loop Container' that does a select from a table and I use Variable Mappings to map each row set result to Package Variables in SSIS. This works fine. However, in the Data Flow, I have a Script component in which I need to access the values of those variables that have been set in the ForEach Loop Container. How do I do this?
Perhaps I'm on the wrong track but I guess I need to access the ADO Object Source variable set in the ForEach Loop Container? Through my own experimentation, I know that accessing the VariableDispenser collection only returns whatever default values that happen to be assigned to the variables in the SSIS GUI (not the values assigned during each iteration of the ForEach Loop).
I am trying to reference a package level variable in a script component (in the Code) and am unable to do so successfully. I have it listed as a ReadOnlyVariables in the custom properties of the script component, however unable to reference it in the code.
I have a Reporting Services 2005 report that is emailed to users. I created/formatted the report based on how it looked in Outlook 2003 which all of our users had at that time. The report is wide, but just fit in the email window when it was full size. Now I have a user who has been converted to Outlook 2007 and he emailed me a copy of the report he received in Outlook 2007 and it looks like it reduced the column sizes; it does not fill the entire email window and wraps the wording on most lines because the column sizes have been reduced.
I've ran into trouble while creating a rather simple transformation script component (one input, one output). The only thing it has to do is test the values coming from it's input rows and set the values of the output rows according to some rules; something like:
Code Block
Public Overrides Sub InputBrowser_ProcessInputRow(ByVal Row As InputBrowserBuffer)
If Row.UserAgent.Contains("MSIE") Then 'test input Row.BrowserName = "Internet Explorer" 'set output End If
End Sub
This raises an "Object reference not set to an instance of an object." exception. Commenting out the input (Row.UserAgent) solves the exception, but I actually do need to test the contents of the input row (and by leaving only the output manipulation, the script won't reach it's end, the components remain yellow). What can I do about this?
I have a package that has a data lfow task. this task imports data from a db2 database (using the IBM Ole DB provider fro db2) and adds it to sql server database table. This package was created on the server. then though version control (using TFS source control) I check out the package on my local machine. and when I open the package I get the foll 3 errors.
Error 1 Validation error. Import Account Num from BMGP_BDR: DTS.Pipeline: The component metadata for "component "DataReader Source" (1113)" could not be upgraded to the newer version of the component. The PerformUpgrade method failed.
Error 2 Error loading BMAG Download Xref Tables - bmag.dtsx: Microsoft.SqlServer.Dts.Pipeline.ComponentVersionMismatchException: The version of component "DataReader Source" (1113) is not compatible with this version of the DataFlow. [[The version or pipeline version or both for the specified component is higher than the current version. This package was probably created on a new version of DTS or the component than is installed on the current PC.]] at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostCheckAndPerformUpgrade(IDTSManagedComponentWrapper90 wrapper, Int32 lPipelineVersion)
Error 3 Error loading BMAG Download Xref Tables - bmag.dtsx: The component metadata for "component "DataReader Source" (1113)" could not be upgraded to the newer version of the component. The PerformUpgrade method failed.
I have a package which reads an Access file from a folder. My connection manager to this file is .NET providers for OledbMicrosoft Jet 4.0 OLE DB Provider.
Package works from my computer. But when I execute it on the server as a SQL Agent job, I get
The component metadata for "component "DataReader Source" (1) could not be upgraded to the newer version of the component. The PerformUpgrade method failed.
I copied the mdb file to a folder on the server which my packages have no problem reading data from.
My packages run under the same domain account as defined in proxies.
I am trying to load a project under VS2005 under Windows Vista that references an ADOX wrapper that is looking for version 2.8.0.0. The reference fails because the library is not registered.
I thought maybe installing MDAC 2.8 would solve my problems, but when I run the installer for that, it briefly puts up a dialog that it is extracting files, then it disappears.
I am trying to load a project under VS2005 under Windows Vista that references an ADOX wrapper that is looking for version 2.8.0.0. The reference fails because the library is not registered.
I thought maybe installing MDAC 2.8 would solve my problems, but when I run the installer for that, it briefly puts up a dialog that it is extracting files, then it disappears.
Assembly Load Trace: The following information can be helpful to determine why the assembly 'Interop.ADODB' could not be loaded.
=== Pre-bind state information ===LOG: DisplayName = Interop.ADODB (Partial)LOG: Appbase = file:///e:/inetpub/wwwroot/SAPTRainingLOG: Initial PrivatePath = binCalling assembly : (Unknown).=== LOG: Policy not being applied to reference at this time (private, custom, partial, or location-based assembly bind).LOG: Post-policy reference: Interop.ADODBLOG: Attempting download of new URL file:///C:/WINDOWS/Microsoft.NET/Framework/v1.1.4322/Temporary ASP.NET Files/saptraining/8932fe97/1bed5ea1/Interop.ADODB.DLL.LOG: Attempting download of new URL file:///C:/WINDOWS/Microsoft.NET/Framework/v1.1.4322/Temporary ASP.NET Files/saptraining/8932fe97/1bed5ea1/Interop.ADODB/Interop.ADODB.DLL.LOG: Attempting download of new URL file:///e:/inetpub/wwwroot/SAPTRaining/bin/Interop.ADODB.DLL.LOG: Policy not being applied to reference at this time (private, custom, partial, or location-based assembly bind).LOG: Post-policy reference: Interop.ADODB, Version=2.6.0.0, Culture=neutral, PublicKeyToken=null
Version Information: Microsoft .NET Framework Version:1.1.4322.2300; ASP.NET Version:1.1.4322.2300
this thread is actually to help another guy out in a seperate thread lol...I am not a COM expert but I do have a basic understanding of it from a dev perspective. I am trying to get COM interop via VS2005 to work from a sql2005 sp_OACreate call. below is the code. I can replace my custom object with say a sqldmo reference and IT WORKS! So I was thinking maybe security issues, but even when I run sql under an admin account it does not work. and i am connecting via sa for the code.
Also, I have tested the COM object via windows script host/.vbs file: dim oOjbect set oObject = CreateObject(myCOMObject.Math) msgbox oObject.Add(1,1). And the object appears to be listed correctly in the registry under HKEY_Classes
Imports System
Imports System.IO
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Imports Microsoft.VisualBasic
Imports System.Diagnostics
Public Class Math
Public Function Add(ByVal iFirstNum As Integer, ByVal iSecondNum As Integer)
What is the proper method of using the Excel.Interop assemblies with SSIS? I am using SQL Server 2005 Integration Services (VS 2005) against the .NET Framework v2.0.50727 with the Microsoft.Office.Interop.Excel.dll assembly.
I find that the referenced instance of Excel in my SSIS package is not always released from memory as expected. I'm not sure that I have a problem per se, but I am interested in knowing the Microsoft reccommended method of using the Office Interop assemblies with SSIS.
For this thread, "working" code is defined as observing the appearance and disappearance of the Excel.exe image in the Task Manager as it is created and destroyed in the SSIS package.
The following Sript Task code (between stars) works as expected in that the image of Excel can be seen created and removed from the Task Manager list of processes:
************************************************
Imports Microsoft.Office.Interop.Excel
Public Sub Main()
Dim mobjExcelApp As Microsoft.Office.Interop.Excel.Application
Dim mobjWorkbook As Microsoft.Office.Interop.Excel.Workbook
Dim objSheet as Worksheet
Dim strExcelFilename as string = "C:TempMyExcelfile.xls"
Dim strSheetname_ValidSamples as String = "Sheet1"
mobjExcelApp = New Microsoft.Office.Interop.Excel.Application
the code will work fine with no errors, however, the Excel image is not removed from the task list. I have found that the instance will disappear upon waiting (several minutes or more).
I am using vs 2010 to write my dtsx import scripts.I use a script component as a source to create a flat file destination file.Everything have been working fine,but then my development machine crashed and we have to install everything again.Now when i use the execute package utility to test my scripts i get the following error:
Error system.NullReferenceException: Object refrence not set to an instance reference.
In PreExecute section TextReader = new system.io.streamreader(" file name") In the CreateNewOutputRows: dim nextLine as string nextLine = textReader.ReadLine
[code]...
is there something which i did not install or what can be the error?
I applied the following info and created an SSIS package that allows COM control of Excel:
unable to add reference to Microsoft Excel in VSA
The package runs locally, and can be executed successfully after it is migrated to the server, but both of these processes use local memory space (I think). When I try to run the package via a job, it fails with the following error:
Executed as user: ALEREsql_admin. ...on 9.00.3042.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 10:34:04 AM Error: 2008-04-28 10:34:06.73 Code: 0xC001405F Source: Description: Failed to decrypt an encrypted XML node because the password was not specified or not correct. Package load will attempt to continue without the encrypted information. End Error Progress: 2008-04-28 10:34:09.34 Source: Data Flow Task Validating: 0% complete End Progress Error: 2008-04-28 10:34:09.45 Code: 0xC0202009 Source: msr_uhc_pc Connection manager "Excel Connection Manager" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040154. An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040154 Description: "Class not registered". End Error Error: 2008-04-28 10:34:09.45 Code: 0xC020801C Source: Data Flow Task... The package execution fa... The step failed.
I have one SSIS package which is written in Visual studio business intelligence tool. For that SSIS packages i have scheduled a job from SQL server management studio 2005. I mean i have scheduled a job in SQL server agent. This job which i have scheduled contains 6 SSIS packages and the other 5 SSIS packages executes successfully but this only fails giving sone com.Interop exception.
But it is failing giving some com.interop exception. Not sure what type of error is this?
It give following type error:
Microsoft (R) SQL Server Execute Package Utility Version 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 11:00:00 PM Error: 2008-03-27 23:00:00.81 Code: 0x00000000 Source: Execute DTS 2000 Package Task Description: System.Runtime.InteropServices.COMException (0x80040427): Execution was canceled by user. at DTS.PackageClass.Execute() at Microsoft.SqlServer.Dts.Tasks.Exec80PackageTask.Exec80PackageTask.ExecuteThread() End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 11:00:00 PM Finished: 11:00:00 PM Elapsed: 0.579 seconds. The package execution failed. The step failed.
I get the same error when i try to execute the package from Visual studio Business Intelligence tool.
Can you please help me out as to what is this "System.Runtime.InteropServices.COMException" exception occuring when scheduling or executing the job.
As painful as it is proving out to be, I am trying to create a single package that is vendor neutral between SQL Server and Oracle.
At first, I thought that as long as I was going OLEDB in and out, and I ensured that my SQL syntax was generic enough, I'd be OK.
Well, not exactly. I am finding there is more to this. Specifically, metadata gets generated differently based on the source/targets.
For example, on an OLE DB Source task, datatypes will vary (i.e. NUMBER in Oracle vs Int in SQL). The job here is to pick the common denominator for the SSIS specific type. Time consuming, but doable, I think.
Another issue is on an OLE DB Desitnation. If you choose Table for Data Access Mode, this value gets written to the dtsx XML file. So, even when both RDBMS have the same schema (which is an obvious prereq) if choosing SQL Server "dbo.DimTable" will get written to the file and for Oracle "SCHEMA.DIMTABLE" will get written.
So, I am am wondering, what is the best way to address this?
My inital thought was using a dtsConfig file (which I am already using extensively) and set the target table names accordingly. This approach would have the added benefit of allowing for post-deployment configuration changes should a table name change, for example.
This section of the dtsx file shows the values of interest:
Code Snippet <component id="138" name="OLE_DST_DimTable" componentClassID="{E2568105-9550-4F71-A638-B7FE42E66922}" description="OLE DB Destination" localeId="-1" usesDispositions="true" validateExternalMetadata="True" version="4" pipelineVersion="0" contactInfo="OLE DB Destination;Microsoft Corporation;Microsoft SqlServer v9; (C) 2005 Microsoft Corporation; All Rights Reserved; http://www.microsoft.com/sql/support;4"> <properties> <property'>http://www.microsoft.com/sql/support;4">http://www.microsoft.com/sql/support;4"> <properties> <property id="139" name="CommandTimeout" dataType="System.Int32" state="default" isArray="false" description="The number of seconds before a command times out. A value of 0 indicates an infinite time-out." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">0</property> <property id="140" name="OpenRowset" dataType="System.String" state="default" isArray="false" description="Specifies the name of the database object used to open a rowset." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">"ORASCHEMA"."DIMTABLE"</property> <!-- More Properies --> </component>
Ideally, I'd like configuration time control over the name attribute of the component element (highlighted) so that I can set the value of the property element with the OpenRowset attribute type (also highlighted). This way, presumambly as long as datatypes were generic enough, the mapping would just work.
But, in walking through the dtsConfig tree, I don't see these elements or attributes exposed.
I would sincerely appreciate any suggestions on how to accomplish this.
In a Data Flow, I have the necessity to use a SSIS variable of type €œObject€? inside Script Component and assign to it the content of 'n' variables of string type. On exiting from the script the variable of type object should contain something like in the following lines: AAAAAAAAAAAAAAAAAAAAAAAAAAAAA BBBBBBBBBBBBBBBBBBBBBBBBBBBBB CCCCCCCCCCCCCCCCCCCCCCCCCCCCC DDDDDDDDDDDDDDDDDDDDDDDDDDDDD €¦€¦€¦€¦€¦€¦€¦. €¦€¦€¦€¦€¦€¦€¦. On exiting from the data flow I will use the variable of type Object in a Script Task, by reading each element in a cyclic fashion. Is there anyone who have experienced something like this? Could anyone provide any example of that? Thanks in advance!
Hi all I'm into a project which uses a lot of views for joining 2 or more tables. Using the MERGE component in SSIS will be a huge effort coz it only has 2 inputs and I gotta SORT the input too. Isnt it possible to have a VIEW like component that joins more than 2 tables and DOESNT need sorting?? (I've thought about creating views in database engine but it breaks my data floe in SSIS and is'nt a practical solution)
No idea where this bug crept in from. Have been using SSIS for 1.5 years now without hitting this problem.
I had a script component opening an XML document and parsing it using XPATH. I added some code that uses StreamReader / Streamwriter (closing one stream before starting the other). The code works without issue in my C# app.
And it ran without issue 2-3 times in SSIS. Then suddenly after running my package again, the script component says it completes successfully, yet nothing happens. I set a breakpoint on the first line of code - it never hits it. I add a msgbox as the first line of code - and it never displays.
I then close my package / exit out of ssis ... and then re-open it. When i open my script component, all of my code is GONE. All references that I added are gone.
I tried adding the streamreader/writer process to a dll I created from my c# app ... and added the DLL to the package -- same result.
I can reproduce this on 2 different computers.
Anyone experience this problem ? Any idea how to stop it ? Or debug it ?
Here is a slimmed down code sample of what causes the error :
Public Class ScriptMain Public Sub Main() Try Dim xmlDoc As New XmlDocument xmlDoc.Load("c:ulkasync_86281519_20070628045850225_4.xml") MsgBox("xmlLoaded") --this doesn't display once the package starts "acting up" Catch ex As Exception MsgBox(ex.Message) UpdateXML("c:ulkasync_86281519_20070628045850225_4.xml", ex.Message) End Try Dts.TaskResult = Dts.Results.Success End Sub Private Sub UpdateXML(ByVal fileName As String, ByVal message As String) Try Dim invalidChar As String = message.Trim().Substring(message.Trim().IndexOf("0x"), 4) Dim rd As StreamReader = New StreamReader(fileName) Dim xml As String = rd.ReadToEnd() Xml = Xml.Replace(invalidChar, String.Empty) xml = xml.Replace("", String.Empty) xml = xml.Replace("<![CDATA[<![CDATA[", "<![CDATA[") xml = xml.Replace("]]>]]>", "]]>") MsgBox("replaced") rd.Close() Dim wr As StreamWriter = New StreamWriter(fileName) wr.Write(xml) wr.Close() Dim xdoc As XmlDocument = New XmlDocument() xdoc.Load(fileName) Catch ex As Exception UpdateXML(fileName, ex.Message) End Try End Sub End Class
I have a SQL 7.0 backend for a new DB of mine. This is the first time I have developed a SQL backend from scratch, so the problem may be my design. The problem may lie with my Access frontend (I have tried both 97 and 2k), but I am inclined to think not considering the behavior. On only one table (the master table no less), I cannot add records during business hours. I can add records to other tables and edit records on this table though. This troublesome table has a field indexed as unique that is not the PK (don't know if that has any bearing, but it is the only table in this DB that has that property). The adding records problem appears to be network traffic related because sometimes records can be added at lunchtime and definitely after hours. I currently only have 3 connected users. I had these problems with only one connected user, so the DB itself is not being strained by traffic. I often have more traffic on the DB after hours to avoid the problems. I have worked on the connection and network traffic angle for awhile, when I stumbled upon the bizarre part. If I add a record via a stored procedure and run this SP on the SQL query analyzer on the server (no network is involved) it is still slow to add the record (30 minutes or more). Needless to say this would equate to an failure on the front end. The same query will run in 0-3 seconds after hours! Does anyone have any clue how this could happen? I am stumped. I do have a budget for a consultant if I need one, but I need to find someone who has a chance to fix this problem. If you don't know the answer but can refer someone in the DFW area, I am in your debt.
Using SSIS, every month/week I have to generate serveral excel files. I need to find a way to automatically format these excel files (Bold, Headings in Different color etc.). Is there a way to integrate MS Office excel task in SSIS?
Help! My network support staff just upgraded my MS Office products from Office 2003 to Office 2007, and now I no longer get the "F5 = Run" option in Microsoft Visual Studio to run any of my SSIS packages that ran just fine yesterday, before the upgrade.
The "Debug" option does not occur in the menu choices across the top of the screen. (They are File, Edit, View, Project, Build, Data, Format, SSIS, Tools, Test, Window, Community, and Help -- no Debug.)
I store my packages in the File System. I can run many of them just fine, even today, with the Execute Package Utility. My problem occurs when I try to run them in Visual Studio.
Even if I make a simple, new package that moves a table from Access 2003 to SQL Server 2005, I see no Debug menu selection option when I am finished with the new package.
I figure it must be something simple -- I just don't know where to find it in the Visual Studio menu.
Most of the packages I am trying to run do not access Office files: they access dBase 5 files (database); they run processes on SQL Server 2005 servers. I realize that I may have to make some adjustments to connections to Access databases, should such databases be upgraded to Office 2007.
