Error 80040154 Using Microsoft.SqlServer.Dts.Runtime.Package
May 8, 2008
I am executing an SSIS package from VB.NET code. Works great in design mode. When I publish it (using Click-Once), it works great on development computer but fails on a customer computer:
Microsoft.SqlServer.Dts.Runtime.DtsPipelineException: Retrieving the COM class factory for component with CLSID {E80FE1DB-D1AA-4D6B-BA7E-040D424A925C} failed due to the following error: 80040154. ---> System.Runtime.InteropServices.COMException (0x80040154): Retrieving the COM class factory for component with CLSID {E80FE1DB-D1AA-4D6B-BA7E-040D424A925C} failed due to the following error: 80040154.
at Microsoft.SqlServer.Dts.Runtime.Package..ctor()
--- End of inner exception stack trace ---
at Microsoft.SqlServer.Dts.Runtime.Package..ctor()
My project references Microsoft.SQLServer.ManagedDTS.
The error occurs on the following line:
Dim objPackage As New Microsoft.SqlServer.Dts.Runtime.Package
Looking at various forum threads, it would appear that it's because either (1) DLL(s) are not registered on the customer machine, or (2) DLL(s) are missing from the customer machine. The first explanation doesn't seem right -- even though Microsoft.SQLServer.ManagedDTS is a COM dll, doesn't .NET provide a wrapper when it references it? I downloaded Process Explorer from Sysinternals.com to compare loaded DLLs on my dev box to those on the customer's box and while several dlls are loaded when the line is successfully loaded, all of them exist on the customer box.
Any help would be greatly appreciated.
View 17 Replies
ADVERTISEMENT
Oct 25, 2007
Hi,
I have de following code:
ConnectionManager adventureWorks = package.Connections.Add("OLEDB");
I´m wondering how to make a connection to a file in differents machines, FTP, excel file, HTTP, WebService instead of OLDDB, where can I find a reference for the constants that must be used in each case.
thanks.
View 1 Replies
View Related
May 17, 2006
The Execute method in Microsoft.SqlServer.Dts.RunTime.Package class has memory leak after each invokation. This following code demonstrates it.
Output from the program:
Allocated memory after 1 iteration(s) = 476316
Allocated memory after 2 iteration(s) = 546448
Allocated memory after 3 iteration(s) = 555008
Allocated memory after 4 iteration(s) = 563632
Allocated memory after 5 iteration(s) = 572232
Allocated memory after 6 iteration(s) = 580856
Allocated memory after 7 iteration(s) = 589480
Allocated memory after 8 iteration(s) = 598240
Allocated memory after 9 iteration(s) = 606816
Allocated memory after 10 iteration(s) = 615424
Allocated memory after 11 iteration(s) = 624000
Allocated memory after 12 iteration(s) = 632576
Allocated memory after 13 iteration(s) = 641152
Allocated memory after 14 iteration(s) = 649728
Allocated memory after 15 iteration(s) = 658352
Allocated memory after 16 iteration(s) = 666948
Allocated memory after 17 iteration(s) = 675760
Allocated memory after 18 iteration(s) = 684380
Allocated memory after 19 iteration(s) = 693008
Allocated memory after 20 iteration(s) = 701532
//-----------------------------------------------------------------------------
// The Execute method in Microsoft.SqlServer.Dts.RunTime.Package has memory
// leak. This program demonstrates it. The package invoked by this program has
// only a single 'Script Task' that does nothing.
//
// To compile, add referece to Microsoft.SQLServer.ManagedDTS.dll.
//
// csc /r:"C:Program FilesMicrosoft SQL Server90SDKAssembliesMicrosoft.SQLServer.ManagedDTS.dll" ExecPackage.cs
//
//-----------------------------------------------------------------------------
using System;
using System.Diagnostics;
using Microsoft.SqlServer.Dts.Runtime;
namespace Misc
{
/// <summary>
/// Programmatically executes SSIS package, then displays memeory usage
/// after each execution. The memeory usage goes up after each
/// Package.Execute() call, which indicates memory leak!
/// </summary>
static class ExecPackage
{
static void DisplayUsage()
{
Console.WriteLine(@"Usage: ExecPackage <pkgName>");
Console.WriteLine(@" Package <pkgName> resides in Package Store on localhost under File System");
}
static void Main(string[] args)
{
// Parse command line arguments.
if (args.Length != 1)
{
DisplayUsage();
return;
}
string pkgName = @"File System" + args[0];
// Programmatically execute the package several times.
Application app = new Application();
for (int i = 1; i <= 20; i++)
{
Package pkg = app.LoadFromDtsServer(pkgName, "localhost", null);
pkg.Execute(); // comment out this line, then allocated memory does not increase
// Process.Start("dtexec.exe", "/dts "" + pkgName + """);
pkg.Dispose();
pkg = null;
// Do garbage collection, then display memory usage
GC.Collect();
Console.WriteLine("Allocated memory after {0} iteration(s) = {1}",
i, GC.GetTotalMemory(true));
}
}
}
}
View 36 Replies
View Related
Feb 3, 2007
I have a very small project written in VB.Net 2005 using the SQL Server 2005 SSiS DTSx package.
I migrated a SLQ 2000 DTS package using the SQL 2005 Legacy tools and saved the package as a local .DTSx package on our file server.
I need to run the package from a clients PC.
I added the reference Microsoft.SqlServer.ManagedDTS so I could then use the Microsoft.SqlServer.Dts.Runtime so I can execute the commands:
Dim oApp As New Application
Dim oPkg As New Package
oPkg = oApp.LoadPackage(g_DTSx_Directory & "AOC copy Generic1 CSV to AOC_verify_file_1.dtsx", Nothing)
Dim oResults As DTSExecResult
oResults = oPkg.Execute
Ok. That works fine and is basically the entire app. It executes without a hitch in debug and as a compiled exe on my PC, but I have all the tools. So now I try and create a setup project for this and I use the setup wizard.
During the creation of the setup project I get a message that states:
The following files may have dependencies that cannot be determined automatically. Please confirm that all dependencies have been added to the project.
C:windowssystem32msxml6.dll
OK. The dll is part of the reference I mentioned above and I have no idea what other dependencies it may have.
How do I find this out?
Has anyone else created a project like this and experenced the same?
I am on a clean build running WinXP Pro with SP2 - VS2005 with SP1 and the SQL Server 2005 tools.
View 4 Replies
View Related
Oct 22, 2007
I am trying to launch a SSIS package remotely, and found this article http://technet.microsoft.com/en-us/library/ms403355.aspx#agent
however, i cannot add a reference to Microsoft.SqlServer.DTS.Runtime ? My Visual Studio 2005 interface does not show that as a possible reference when using "Add Reference"
How can I fix that?
Thanks
View 4 Replies
View Related
Oct 18, 2007
I'm trying to run a piece of code in BOL and it imports the dts.runtime class. The problem is when I do the import statement in my form, I get the green line. It can't find the class. The only class found inside SqlServer is Server.
Can anyone tell me why the DTS class isn't there. The box I'm working on has visual studio 2005, and Yukon sp2. And I think I'm running v.3 of the framework, so I should be good all the way around.
Thx.
View 4 Replies
View Related
Jul 18, 2006
Hi,
It states pretty clearly in the documentation for the Microsoft.SqlServer.Dts.Runtime.Wrapper namespace that "This assembly is a Primary Interop Assembly (PIA) for the namespace Microsoft.SqlServer.Dts.Runtime, which is the assembly Microsoft.SqlServer.ManagedDTS (in microsoft.sqlserver.manageddts.dll). The classes in this namespace should not be instantiated. Instead, use the classes found in the Microsoft.SqlServer.Dts.Runtime. "
And yet as I have found out earlier today and talked about here, sometimes it is absolutely necassary to use Microsoft.SqlServer.Dts.Runtime.Wrapper. Why is this? Why is there no class in Microsoft.SqlServer.Dts.Runtime that allows me to access the specific connection managers rather than using the generic ConnectionManager class?
-Jamie
View 3 Replies
View Related
Dec 21, 2006
Greetings,
I have an ASP.NET web form that calls out to a component which includes the following code which leverages the Application and Package classes in the Microsoft.SqlServer.Dts.Runtime namespace:
Application application = new Application();
Package package = application.LoadFromDtsServer(@"File SystemMyPackagesMyPackage", "MyMachine", null);
string id = package.ID;
Microsoft.SqlServer.Dts.Runtime.DTSExecResult result = package.Execute();
I am logged in to the app as DomainMe.
I have Windows Integrated Authentication configured in IIS and am using Windows Authentication in ASP.NET. I am using impersonation to impersonate a fixed identity, say DomainBob. This has the following effect:
WindowIdentity.GetCurrent() returns DomainBob as expected since the process is being impersonated by DomainBob.
Thread.CurrentPrincipal.Identity.Name returns DomainMe as expected since the ASP.NET application is using Windows Authentication.
The id variable successfuly is initialized to the GUID of the package, proving that the package name is resolving, however, from the ASP.NET application, the package fails.
Issuing the ? package.Errors[0] command at the command window provides the following information:
{Microsoft.SqlServer.Dts.Runtime.DtsError}
base {Microsoft.SqlServer.Dts.Runtime.DtsObject}: {Microsoft.SqlServer.Dts.Runtime.DtsError}
Description: "An OLE DB error has occurred. Error code: 0x80040E4D.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E4D Description: "Communication link failure".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E4D Description: "Shared Memory Provider: I/O Error detected in read/write operation [4]. ".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E4D Description: "Login failed for user 'MyMachine\ASPNET'.".
"
ErrorCode: -1071636471
HelpContext: 0
HelpFile: null
IDOfInterfaceWithError: "{8BDFE898-E9D8-4D23-9739-DA807BCDC2AC}"
Source: "Packages MyPackage"
SubComponent: "Connection manager "DataMart""
TimeStamp: {12/21/2006 2:18:55 PM}
For some reason, the ASPNET process account is being passed to the package!
This makes no sense, because clearly, DomainBob is impersonating the worker process and all downstream resources should be accessed via DomainBob. This includes SQL Server Database Engine and I would imagine SSIS as well. ASPNET should not come into play at all since I am impersonating.
I also tried using regular (non-fixed identity) impersonation (<identity impersonate="true" />), but again, SSIS is trying to authenticate ASPNET, instead of DomainMe as would be expected.
This is definetely isolated to ASP.NET applications, because making the same call from within a unit test succeeds. It would seem that this is a definete issue because the behavior is inconsistent with ASP.NET security principles.
Has anyone else seen this problem?
Any direction or suggestions very much appreciated.
Thanks,
Rick
View 13 Replies
View Related
Dec 21, 2006
I am struggling in calling an SSIS package programatically using the Microsoft.SqlServer.Dts.Runtime namespace.
I am succesfuly connecting to the package insofar as I am able to retrieve the package ID (GUID), but when I call package.Execute I get a 'login failed for user' error, which indicates a security problem.
My ASP.NET app is running as a domain user which has temporary 'SA' rights on the server where the package is hosted. In addition, I have set the protection level on the package to 'DontSaveSensitive'.
What am I missing to be able to execute the package remotely?
TIA,
Rick
View 1 Replies
View Related
Mar 3, 2006
when using the microsoft.sqlserver.dts.runtime to create packages programatically....
what object represents a data flow task?
View 1 Replies
View Related
Apr 17, 2007
i get the following exception HResult -2146233088
[Microsoft.SqlServer.Dts.Runtime.DtsRuntimeException] {"No description found"} Microsoft.SqlServer.Dts.Runtime.DtsRuntimeException
when i try to use this following code snippet
try
{
ConnectionManager objOLEDBConnection;
Microsoft.SqlServer.Dts.Runtime.Package objPackage = new Microsoft.SqlServer.Dts.Runtime.Package();
objOLEDBConnection = objPackage.Connections.Add("OLEDB");
}
catch(Exception ex)
{
ex.Message ;
}
could you please help me out with this ?
View 2 Replies
View Related
Jul 28, 2006
Hello, I got an exception when I try to load/run a SSIS Package programmatically on the server using remoting.
The client machine has no SQL Server 2005 component installed..
This is the error I get from my exe application:
Microsoft.SqlServer.Dts.Runtime.DtsPipelineException: Retrieving the COM class factory for component with CLSID {E44847F1-FD8C-4251-B5DA-B04BB22E236E} failed due to the following error: 80040154.
---> System.Runtime.InteropServices.COMException (0x80040154): Retrieving the COM class factory for component with CLSID {E44847F1-FD8C-4251-B5DA-B04BB22E236E} failed due to the following error: 80040154.
at Microsoft.SqlServer.Dts.Runtime.Application..ctor()
And this the Remote component:
<Serializable()> Public Class SSISComponent
Inherits MarshalByRefObject
Public Function LaunchPackage(ByVal sourceLocation As String, ByVal serverName As String, ByVal packageName As String, ByVal packageVariables As SortedList) As Microsoft.SqlServer.Dts.Runtime.DTSExecResult
Dim packagePath As String
Dim myPackage As Package
Dim integrationServices As New Application
' Combine path and filename.
packagePath = Path.Combine(sourceLocation, packageName)
Try
If integrationServices.ExistsOnDtsServer(packagePath, serverName) Then
myPackage = integrationServices.LoadFromDtsServer(packagePath, serverName, Nothing)
Else
Throw New ApplicationException( "Invalid package name or location: " & packagePath)
End If
If Not packageVariables Is Nothing Then
For Each de As DictionaryEntry In packageVariables
myPackage.Variables(de.Key).Value = de.Value
Next
End If
LaunchPackage = myPackage.Execute()
Catch ex As Exception
Throw
End Try
End Function
End Class
This is the code that call the remote component to run the package on the Server:
Public Class Test
Private Function LoadAndRunPackageRemotly(ByVal PackageName As String, ByVal PackageVariables As SortedList) As Boolean
Dim launchPackageService As New SSISComponent
Dim packageResult As Microsoft.SqlServer.Dts.Runtime.DTSExecResult
Try
packageResult = launchPackageService.LaunchPackage(Me.SSISPackageLocation, Me.SSISServerName, PackageName, PackageVariables)
If packageResult = DTSExecResult.Success Then
LoadAndRunPackageRemotly = True
Else
LoadAndRunPackageRemotly = False
End If
Catch ex As Exception
Throw
End Try
End Function
End Class
I would like to inform that everything run inside a thread because the Package do a lot of things...
When I build up the setup for the exe inside the Detected Dependencies there are several DLL related to SQL Server (Microsoft.SQLServer.DTSRuntimeWrap.dll etc..etc..) that I don't register ..
Tis could be the problem?
If yes, somebody can tell me which one I have to register ? as COM or COMRelativePath?
Some other hints?
This error could be also caused by wrong permissions on the ssis?
Thank you very much for any help...
p.s. To use sql server agent will be my last option!!
Marina b.
View 8 Replies
View Related
Aug 2, 2006
Hello,
I am currently getting the following error:
Microsoft VBScript runtime error '800a000d'
Type mismatch: 'QueryString'
/admin/equipment/addequipment.asp, line 128
This is when i try to submit a new database item in Dreamweaver. The DB I am using is SQL Server 2005.
My code is as follows:
Dim equipspec__urlid
equipspec__urlid = "1"
If (QueryString("ID") <> "") Then
equipspec__urlid = QueryString("ID")
End If
%>
<%
Dim equipspec
Dim equipspec_numRows
Set equipspec = Server.CreateObject("ADODB.Recordset")
equipspec.ActiveConnection = MM_intranet_STRING
equipspec.Source = "SELECT * FROM EquipSpec WHERE " + Replace(equipspec__urlid, "'", "''") + " = EquipSpec.ID"
equipspec.CursorType = 0
equipspec.CursorLocation = 2
equipspec.LockType = 1
equipspec.Open()
Would be greatful if anybody could help, let me know if you need more info.
Many thanks.
View 1 Replies
View Related
Jul 23, 2005
Hello All,I am getting the following error when attemping to open a table inSQL2kSP3a.________________________________________SQL Server Enterprise ManagerDatabase Server: Microsoft SQL ServerVersion: 08.00.0760Runtime Error: [Microsoft][ODBC SQL Server Driver]Invalid time format_________________________________________I cannot find it in sysmessages, or on the web.Any ideas about how to resolve this? And how it occured...Thanks,TGru*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 1 Replies
View Related
Mar 7, 2007
Hi,
I am trying to create a DTS package dynamically. I have taken all these steps i.e.,
1. SN.EXE -K c:DTS.KEY2.tlbimp.exe "C:program filesmicrosoft SQL Sever80ToolsBindtspkg.dll" /out:c:Microsoft.SQLServer.DTSPkg80.dll /Keyfile:c:DTS.KEY3.gacutil.exe -i C:Microsoft.SQLServer.DTSPkg80.dll.
Now when i compile the code, i didnt get any compilation error. But when execute the code it gives me a runtime error which goes as given below :
An unhandled exception of type 'System.InvalidCastException' occurred in DTSFactory.exe
Additional information: QueryInterface for interface Microsoft.SQLServer.DTSPkg80.CustomTask failed.
I am getting this error near the line : oCustTask = (DTS.DataPumpTask2)oTask.CustomTask;
The following is the code.
using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using DTS = Microsoft.SQLServer.DTSPkg80;
namespace DTSFactory
{
/// <summary>
/// Summary description for Form1.
///This is assuming that all steps have been taken in the following document:
///http://SQLDEV.NET/DTS/DotNetCookBook.htm
///SN.EXE -K c:DTS.KEY
///tlbimp.exe "C:program filesmicrosoft SQL Sever80ToolsBindtspkg.dll" /out:c:Microsoft.SQLServer.DTSPkg80.dll
///Keyfile:c:DTS.KEY
///gacutil.exe -i C:Microsoft.SQLServer.DTSPkg80.dll
///These steps are needed for interop with dtspkg.dll
/// </summary>
public class Form3 : System.Windows.Forms.Form
{
/// <summary>
/// Required designer variable.
/// </summary>
private System.ComponentModel.Container components = null;
private System.Windows.Forms.Button button1;
public DTS.Package2Class pkg = new DTS.Package2Class();
public Form3()
{
//
// Required for Windows Form Designer support
//
InitializeComponent();
//
// TODO: Add any constructor code after InitializeComponent call
//
}
private void Form3_Load(object sender, System.EventArgs e)
{
}
/// <summary>
/// Clean up any resources being used.
/// </summary>
protected override void Dispose( bool disposing )
{
if( disposing )
{
if(components != null)
{
components.Dispose();
}
}
base.Dispose( disposing );
}
#region Windows Form Designer generated code
/// <summary>
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
/// </summary>
private void InitializeComponent()
{
this.button1 = new System.Windows.Forms.Button();
this.SuspendLayout();
//
// button1
//
this.button1.Location = new System.Drawing.Point(0, 0);
this.button1.Name = "button1";
this.button1.Size = new System.Drawing.Size(40, 56);
this.button1.TabIndex = 0;
this.button1.Text = "button1";
this.button1.Click += new System.EventHandler(this.button1_Click);
//
// Form3
//
this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);
this.ClientSize = new System.Drawing.Size(648, 429);
this.Controls.Add(this.button1);
this.Name = "Form3";
this.Text = "Form3";
this.Load += new System.EventHandler(this.Form3_Load);
this.ResumeLayout(false);
}
#endregion
private void button1_Click(object sender, System.EventArgs e)
{
initpackage();
}
public void initpackage()
{
CreateConnections();
CreatePackageSteps();
DefinTasks(pkg);
pkg.Name="MyCSharpDTSTest";
pkg.Description = "CShart DTS Test";
object MIA=System.Reflection.Missing.Value;
pkg.SaveToSQLServer("MyServerName", "MyUserID", "MyPassword",
DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_Default, "","","",ref
MIA,false);
pkg.Execute();
pkg.UnInitialize();
pkg = null;
}
public void CreateConnections()
{
DTS.Connection txtConn,sqlConn;
txtConn = pkg.Connections.New("DTSFlatFile");
//label1.Text = label2.Text = "";
//for (int i=1;i<txtConn.ConnectionProperties.Count;i++)
// label1.Text = label1.Text + "
" + txtConn.ConnectionProperties.Item(i).Name;
//for text connection
//start
txtConn.ConnectionProperties.Item(1).Value = "C:\FlatFiles\1.txt";
txtConn.ConnectionProperties.Item(3).Value = 1;
txtConn.ConnectionProperties.Item(4).Value = "
";
txtConn.ConnectionProperties.Item(5).Value = 1;
txtConn.ConnectionProperties.Item(7).Value = "|";
txtConn.ConnectionProperties.Item(9).Value = 1;
txtConn.ConnectionProperties.Item(10).Value = 0;
txtConn.ConnectionProperties.Item(11).Value = """;
txtConn.ConnectionProperties.Item(12).Value = false;
txtConn.ConnectionProperties.Item(14).Value = 0;
txtConn.ConnectionProperties.Item(18).Value = 255;
txtConn.Name = "Text File (Source)";
txtConn.ID = 1;
txtConn.Reusable = true;
txtConn.ConnectImmediate = false;
txtConn.DataSource = "C:\FlatFiles\1.txt";
txtConn.ConnectionTimeout = 60;
txtConn.UseTrustedConnection = false;
txtConn.UseDSL = false;
//end
pkg.Connections.Add(txtConn);
sqlConn = pkg.Connections.New("SQLOLEDB");
//for (int i=1;i<sqlConn.ConnectionProperties.Count;i++)
// label2.Text = label2.Text + "
" + sqlConn.ConnectionProperties.Item(i).Name;
sqlConn.ConnectionProperties.Item(3).Value = true;
sqlConn.ConnectionProperties.Item(4).Value = "sa";
sqlConn.ConnectionProperties.Item(5).Value = "FCSUAT";
sqlConn.ConnectionProperties.Item(6).Value = "(local)";
sqlConn.ConnectionProperties.Item(19).Value = "DTS Designer";
sqlConn.Name = "Microsoft OLE DB Provider for SQL Server";
sqlConn.ID = 2;
sqlConn.Reusable = true;
sqlConn.ConnectImmediate = false;
sqlConn.DataSource = "(local)";
sqlConn.UserID = "sa";
sqlConn.ConnectionTimeout = 60;
sqlConn.Catalog = "FCSUAT";
sqlConn.UseTrustedConnection = false;
sqlConn.UseDSL = false;
pkg.Connections.Add(sqlConn);
}
public void CreatePackageSteps()
{
DTS.Step2 oStep = (DTS.Step2)pkg.Steps.New();
oStep.Name = "Copying Data from myTableName";
oStep.Description = "Copying Data from myTableName";
oStep.TaskName = "Copying Data from myTableName";
oStep.CommitSuccess = false;
oStep.RollbackFailure = false;
oStep.ScriptLanguage = "VBScript";
oStep.AddGlobalVariables = true;
oStep.CloseConnection = false;
oStep.ExecuteInMainThread = true;
oStep.IsPackageDSORowset = false;
oStep.JoinTransactionIfPresent = false;
oStep.DisableStep = false;
oStep.FailPackageOnError = true;
pkg.Steps.Add(oStep);
oStep = null;
}
public void DefinTasks(DTS.Package2Class package)
{
DTS.DataPumpTask2 oCustTask;
DTS.Task oTask;
oTask = package.Tasks.New("DTSDataPumpTask");
oCustTask = (DTS.DataPumpTask2)oTask.CustomTask;
oCustTask.Name = "Copying Data from myTableName";
oCustTask.Description = "Copying Data from myTableName to MyDestDB.myTableName";
DTS.DataPumpTask2 oDataPump = (DTS.DataPumpTask2)oTask.CustomTask;
oDataPump.SourceConnectionID = 1;
oDataPump.SourceSQLStatement = "SELECT `MyField` FROM myTableName";
oDataPump.DestinationConnectionID =2;
oDataPump.DestinationObjectName = "myTableName";
oDataPump.ProgressRowCount = 1000;
oDataPump.MaximumErrorCount = 0;
oDataPump.FetchBufferSize = 1;
oDataPump.UseFastLoad=true;
oDataPump.InsertCommitSize = 0;
oDataPump.InsertCommitSize = 500000;
oDataPump.ExceptionFileColumnDelimiter = "|";
oDataPump.ExceptionFileRowDelimiter = "
";
oDataPump.AllowIdentityInserts = false;
oDataPump.FirstRow = 0;
oDataPump.LastRow = 0;
CreateTaskTrans(oDataPump, oCustTask);
pkg.Tasks.Add(oTask);
oCustTask = null;
oTask = null;
}
public void CreateTaskTrans(DTS.DataPumpTask DatPump,
DTS.DataPumpTask2 CustTask)
{
DTS.Transformation2 oTransformation;
oTransformation =
(DTS.Transformation2)CustTask.Transformations.New("DTS.DataPumpTransformCopy");
oTransformation.Name = "DirectCopyXform";
oTransformation.TransformFlags = 63;
oTransformation.ForceSourceBlobsBuffered = 0;
oTransformation.ForceBlobsInMemory = false;
oTransformation.InMemoryBlobSize = 1048576;
oTransformation.SourceColumns.AddColumn("MyField",1);
oTransformation.DestinationColumns.AddColumn("MyField",1);
DatPump.Transformations.Add(oTransformation);
}
}
}
Can anyone help me in solving this?
Thanks in advance.
View 1 Replies
View Related
May 24, 2007
Hi,
I picked the Microsoft.SQLServer.ManagedDTS.dll from following folder:
C:WINDOWSassemblyGAC_MSILMicrosoft.SqlServer.ManagedDTS9.0.242.0__89845dcd8080cc91>
Similarly picked Microsoft.SqlServer.DTSRuntimeWrap.dll also.
I added it as reference in my .NET application.
When I execute the program I get below error:
Retrieving the COM class factory for component with CLSID {E44847F1-FD8C-4251-B5DA-B04BB22E236E} failed due to the following error: 80040154.
Any Clue?
Application executes from a machine where SQL Server 2005 is not installed.
How to get the RunningPackages information back to a client PC?
HV
View 8 Replies
View Related
Jul 1, 2005
I tried to create a package from a C# program, and I copied this from SQL server online book:
View 13 Replies
View Related
Nov 22, 2015
I am trying to obtain access to SQL Server Native Client OLE DB provider using :
hr = CoCreateInstance(CLSID_SQLNCLI11, NULL, CLSCTX_INPROC_SERVER, IID_IDBInitialize,
(void **) &PIDBInitialize);
and this fails with 80040154 error. I am using sqlserver 2012 and everything worked perfectly fine as long as my application was running on the same machine as sql server was installed but now that the app is deployed in a different environment in which the app runs on a separate server than the server which has the database and sql server I am failing with this error even though the sql server version on the target server is same as mine.
View 3 Replies
View Related
Sep 12, 2007
Hi,
I'm in the process of writing a fairly complex SSIS package that reads csv files and goes on to load the content into a SQL Server 2005 database.
I am testing the package by executing it from within a Nant script. My test process resets the data in the target database and then loads data from a known set of test data files. Because it's scripted I can be sure that SSIS is being asked to do the same thing, every time in a consistent way.
When I execute the package I frequently (although not always) get an error message dialog mid-ececution that reads:
Code Snippet
Microsoft Visual c++ Runtime Library
Runtime error!
Program: C...
The application has requested the Runtime to terminate it in an unusual way. Please contact the application's support team for more information.
When I click OK the VS Just-In-Time debugger tells me about an unhandled win32 exception in DTExec.exe [4696] and asks me if I wish to debug (which I do not).
No error is reported by SSIS itself, it literally just stops in its tracks.
I will appreciate any help that can be offered here
Nick
View 5 Replies
View Related
Dec 19, 2006
I am using VB.NET 2002 and we have set up a test environment running SQL Server 2005.
I am trying to add a reference to Microsoft.SQLServer.ManagedDTS.dll which is required to run a DTS/SSIS package from VB.NET.
However the dll does not show in my add reference listing so when I select BROWSE and select the actual DLL I get a rather long error message telling me that only 'dll' and COM components can be referenced.
The exact message is:
A reference to 'C:Programs FilesMicrosoft SQL Server90SDKAssembliesMicrosoft.SQLServer.ManagedDTS.dll' could not be added. This is not a valid assembly or COM component. Only assemblies with extension 'dll' and COM components can be referenced. Please make sure that the file is accessible, and that it is a valid assembly or COM component.
Any suggestions?
thanks
View 3 Replies
View Related
Dec 13, 2007
Hi,
I am running dts in Sql Server 2005 management studio from Management, Legacy and data Transformation Services.
Once the dts has run, I get this error message "Error Source : Microsoft Data Transformation Services (DTS) Package Error Description : Error accessing Windows Event Log."
Please help me
thanks in advance
Srinivas
View 1 Replies
View Related
Jan 16, 2008
I know there are already several bug submissions on this error, and I am sure the cryptoAPI team is working to resolve this issue.. However... Is it possible that the CAPICOM update (KB931906) could have contributed to this issue? In my instance, I installed SQLServer 2005 Express w/ Adv Tools SP2 after a freash XP install. I checked it out, and everything worked fine. I updated windows (online) and now it gives this error. In fact, I cannot even run the un-install because the setup utility also hits the following error (as taken from the log file):
Microsoft SQL Server 2005 Setup beginning at Wed Jan 16 12:47:35 2008
Process ID : 576
d:97352a1d146f8bca5542016500a05210setup.exe Version: 2005.90.3042.0
Running: LoadResourcesAction at: 2008/0/16 12:47:35
Complete: LoadResourcesAction at: 2008/0/16 12:47:35, returned true
Running: ParseBootstrapOptionsAction at: 2008/0/16 12:47:35
Loaded DLL:97352a1d146f8bca5542016500a05210xmlrw.dll Version:2.0.3609.0
Complete: ParseBootstrapOptionsAction at: 2008/0/16 12:47:35, returned false
Error: Action "ParseBootstrapOptionsAction" failed during execution. Error information reported during run:
Could not parse command line due to datastore exception.
Source File Name: utillibpersisthelpers.cpp
Compiler Timestamp: Wed Jun 14 16:30:14 2006
Function Name: writeEncryptedString
Source Line Number: 124
----------------------------------------------------------
writeEncryptedString() failed
Source File Name: utillibpersisthelpers.cpp
Compiler Timestamp: Wed Jun 14 16:30:14 2006
Function Name: writeEncryptedString
Source Line Number: 123
----------------------------------------------------------
Error Code: 0x80070002 (2)
Windows Error Text: The system cannot find the file specified.
Source File Name: cryptohelpercryptsameusersamemachine.cpp
Compiler Timestamp: Wed Jun 14 16:28:04 2006
Function Name: sqls::CryptSameUserSameMachine:rotectData
Source Line Number: 50
2
Could not skip Component update due to datastore exception.
Source File Name: datastorecachedpropertycollection.cpp
Compiler Timestamp: Wed Jun 14 16:27:59 2006
Function Name: CachedPropertyCollection::findProperty
Source Line Number: 130
----------------------------------------------------------
Failed to find property "InstallMediaPath" {"SetupBootstrapOptionsScope", "", "576"} in cache
Source File Name: datastorepropertycollection.cpp
Compiler Timestamp: Wed Jun 14 16:28:01 2006
Function Name: SetupBootstrapOptionsScope.InstallMediaPath
Source Line Number: 44
----------------------------------------------------------
No collector registered for scope: "SetupBootstrapOptionsScope"
Running: ValidateWinNTAction at: 2008/0/16 12:47:35
Complete: ValidateWinNTAction at: 2008/0/16 12:47:35, returned true
Running: ValidateMinOSAction at: 2008/0/16 12:47:35
Complete: ValidateMinOSAction at: 2008/0/16 12:47:35, returned true
Running: PerformSCCAction at: 2008/0/16 12:47:35
Complete: PerformSCCAction at: 2008/0/16 12:47:35, returned true
Running: ActivateLoggingAction at: 2008/0/16 12:47:35
Error: Action "ActivateLoggingAction" threw an exception during execution. Error information reported during run:
Datastore exception while trying to write logging properties.
Source File Name: datastorecachedpropertycollection.cpp
Compiler Timestamp: Wed Jun 14 16:27:59 2006
Function Name: CachedPropertyCollection::findProperty
Source Line Number: 130
----------------------------------------------------------
Failed to find property "primaryLogFiles" {"SetupStateScope", "", ""} in cache
Source File Name: datastorepropertycollection.cpp
Compiler Timestamp: Wed Jun 14 16:28:01 2006
Function Name: SetupStateScope.primaryLogFiles
Source Line Number: 44
----------------------------------------------------------
No collector registered for scope: "SetupStateScope"
00E2CFC0Unable to proceed with setup, there was a command line parsing error. : 2
Error Code: 0x80070002 (2)
Windows Error Text: The system cannot find the file specified.
Source File Name: datastorepropertycollection.cpp
Compiler Timestamp: Wed Jun 14 16:28:01 2006
Function Name: SetupBootstrapOptionsScope.InstallMediaPath
Source Line Number: 44
Failed to create CAB file due to datastore exception
Source File Name: datastorecachedpropertycollection.cpp
Compiler Timestamp: Wed Jun 14 16:27:59 2006
Function Name: CachedPropertyCollection::findProperty
Source Line Number: 130
----------------------------------------------------------
Failed to find property "HostSetup" {"SetupBootstrapOptionsScope", "", "576"} in cache
Source File Name: datastorepropertycollection.cpp
Compiler Timestamp: Wed Jun 14 16:28:01 2006
Function Name: SetupBootstrapOptionsScope.HostSetup
Source Line Number: 44
----------------------------------------------------------
No collector registered for scope: "SetupBootstrapOptionsScope"
Message pump returning: 2
Since the Microsoft.SqlServer.Express.SQLEditors utilizes a crypto wrapper, givin the error above... would the CAPICOM update have affected things?
Brian Nichols
View 1 Replies
View Related
Apr 3, 2008
I'm in a SSIS Package Design tab working on a data flow. It was working fine up to now, but all of a sudden I keep getting this error that says, "Microsoft Visual Studio has encountered a problem and needs to close." And it has that "Send Error Report" and "Don't Send" button at the bottom. This started happening when I tried to add a Data Viewer on a Data Flow Path. And now every time I do this to try to debug my data flow, it gives me this message and closes my project.
However, the package executes without any package errors, but I as I described above, I can't add a Data Viewer because it keeps giving me this message and closing my package.
View 6 Replies
View Related
Oct 23, 2007
I am exporting data to excel with ssis package.In my system it is working fine., but in server it is giving this error.All the connection string for database and excel file path are coming correct in run time with the help of package configeration.
one more thing is if we execute directly the package by double clicking in server it is working, but through my application it is giving this error.
An OLE DB error has occurred. Error code: 0x80040E09.
The ProcessInput method on component "Excel Destination" (22) failed with error code 0xC0202009. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.
Thread "WorkThread0" has exited with error code 0xC0202009.
someone help me.. thanks in advance.
View 4 Replies
View Related
Jan 9, 2008
Hi guys, need help again.
I'm running packages from C# code and thus I need to create an instance of SqlServer.DTS.Runtime.Application.
In my maching, creating the application instance doesn't even take 1 sec but when the application was deployed to our QA server creating the instance took 30 secs (always ... every single time with no exception).
this single code is taking the 30 secs:
Application integrationService = New Application();
Does anyone have any idea what I need to check or configure to make this faster?
View 5 Replies
View Related
Sep 14, 2007
I am working on a package that will sense a text file in a folder. From this text file I will be extracting the file name which will tell me what task I need to run. How can I create a package that, depending upon what the file name is, call a specific package. To be more clear, I will have 3 different flat files that is each in a standard form. Their names will be unique. To transform their data will require different ETL logic. I will be using a loop to constantly check to see if a file has been put into the directory. I just need to know how I can direct the input file to the proper ETL task.
View 3 Replies
View Related
Oct 18, 2005
Here I go again...thanks for your patience...
View 13 Replies
View Related
Feb 15, 2007
Hello,
I have three package variables that I need to have access to at runtime. All three variables have package scope. The first two, 'StartDate' and 'EndDate' are DateTime variables. The third is called FilePath and is a String variable.
I have taken the following steps;
1. Enabled package configurations
2. Set up all three variables as Parent Package Variables, and have targeted the 'Value' property for each.
3. In the properties of the solution, I have set AllowConfigurationChanges to True.
4. After the package was built, I ran the Package Installation Wizard from the Manifest.
I have done both File System and SQL Server installations. When I go to run the package, none of the three package variables are available for modification.
What am I doing wrong?
Thank you for your help!
cdun2
View 15 Replies
View Related
Dec 13, 2007
I am looking for a way to script out all tables within a SQL Server 2005 database using VB.NET. I am writing a backup utility and have been able to do stored procedures, the data within the tables, but am now stuck on scripting out the tables with their primary keys, indexes, etc. Sure, I could hard-code some stuff like "CREATE TABLE dbo" & strTable, but I am pretty sure there is a way to do it within VB.NET using SMO. So, any help that someone could offer is greatly appreciated.
Thanks
View 1 Replies
View Related
Jan 21, 2008
Stupid question. I'm trying to reference Microsoft.SqlServer.ManagedDTS, which allegedly is to be found in Program FilesMicrosoft SQL Server90SDKAssemblies. Except it isn't. Nor is it anywhere on the machine. I know that Integration Services is installed and running - my packages run just fine out of BIDS and via dtexec. Yet I can't find the stupid dll. Clearly I've missed something. Any ideas?
View 12 Replies
View Related
Jul 31, 2006
The files
Microsoft.SqlServer.Smo.dll
MicrosoftSqlServer.ConnectionInfo.dll
Microsoft.SqlServer.SmoEnum.dll
should be found in the C:Program FilesMicrosoft SQL Server90SDKAssemblies directory. However, they are not there. Any ideas why?
Thanks
Bill
View 1 Replies
View Related
Feb 6, 2006
64 bit Server 2003 with Sql 2005 64-bit
I can't seem to do a lot of things. Like... backup the database through the SQL agent.
I'm catching flak from the client who was told by a microsoft rep that SQL 2005 works like a charm....
It's too late to move away from SQL 2005, we've already migrated systems over to it, and now I'm stuck.
This error happens when trying to export to a flat file using Export Wizard(right click database and choose export):
===================================
An error occurred which the SQL Server Integration Services Wizard was not prepared to handle. (SQL Server Import and Export Wizard)
===================================
Exception has been thrown by the target of an invocation. (mscorlib)
------------------------------
Program Location:
at System.RuntimeMethodHandle._InvokeConstructor(Object[] args, SignatureStruct& signature, IntPtr declaringType)
at System.RuntimeMethodHandle.InvokeConstructor(Object[] args, SignatureStruct signature, RuntimeTypeHandle declaringType)
at System.Reflection.RuntimeConstructorInfo.Invoke(BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
at System.RuntimeType.CreateInstanceImpl(BindingFlags bindingAttr, Binder binder, Object[] args, CultureInfo culture, Object[] activationAttributes)
at System.Activator.CreateInstance(Type type, BindingFlags bindingAttr, Binder binder, Object[] args, CultureInfo culture, Object[] activationAttributes)
at Microsoft.SqlServer.Dts.DtsWizard.Step2.DataSourceComboBox_SelectedIndexChanged(Object sender, EventArgs e)
at System.Windows.Forms.ComboBox.OnSelectedIndexChanged(EventArgs e)
at System.Windows.Forms.ComboBox.WmReflectCommand(Message& m)
at System.Windows.Forms.ComboBox.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
===================================
Exception from HRESULT: 0xC0010014 (Microsoft.SqlServer.DTSRuntimeWrap)
------------------------------
Program Location:
at Microsoft.SqlServer.Dts.Runtime.ConnectionManager.get_Properties()
at Microsoft.DataTransformationServices.Controls.FlatFileConnectionManagerUtils.GetLocaleID(ConnectionManager connectionManager)
at Microsoft.DataTransformationServices.Controls.FlatFileManagerGeneralPage.LoadState(ConnectionManager connManager)
at Microsoft.DataTransformationServices.Controls.FlatFileManagerGeneralPage.set_ConnectionManager(ConnectionManager value)
at Microsoft.SqlServer.Dts.DtsWizard.FlatFileDestForm..ctor(DataSourceInfo info, Boolean bDest, WizardInputs inputs, WizardPage page)
===================================
Exception from HRESULT: 0xC0010014 (Microsoft.SqlServer.DTSRuntimeWrap)
------------------------------
Program Location:
at Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSPropertiesProvider90.get_Properties()
at Microsoft.SqlServer.Dts.Runtime.ConnectionManager.get_Properties()
View 40 Replies
View Related
Jun 27, 2007
Reinstalled SqlServer 2005 and SP2. Reinstalled VS2005 and SP1. I add a reference to Microsoft.SqlServer.Dts.DtsClient. It automatically adds a line to web.config
<add assembly="Microsoft.SqlServer.Dts.DtsClient, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845DCD8080CC91"/>
I still cannot do
using Microsoft.SqlServer.Dts.DtsClient;
I can do
using Microsoft.SqlServer.Server;
I am thinking a config file or something is missing/corrupt somewhere to prevent me from being able to correctly reference this dll. Can anyone help please?
View 4 Replies
View Related
