The window application calls the Remotable object, call the specific method and the package is loaded and run successfully.
When I studied the .NET Remoting there is also Server that expose the Remotable Object.
In this case the Server is SSIS Service but I would like to know witch port is used, which method( singlecall or singleton ) is used and so on!
where I can find this info?Someone knows some resource to advice?? or I'm working with SSIS not on a remote way?
So i have about 80 different SSIS PAckages that load different text files into tables. each package has a config file that contains password, server name and user id. I want to forward these packages to a use who will execute them. So instead of giving the user 80 packages to execute manually, how do i put these packages into one parent package so that the user has to only execute this parent package instead of executing 80 different packages NB. package exec is manually. (we will automate later) so this is a temp measure.
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.
When running an integration services package from a windows service I get the "Object ... has been disconnected or does not exist at the server." exception after aproximately six minutes of execution.
This is *not* my windows service failing. I can loop indefinately while tracing to a log file within the service and it will run forever. While calling the mypackage.execute(...) method however, after six minutes (give or take) the exception is thrown...
my code looks something like this: <code> dim foo as Microsoft.SqlServer.Dts.Runtime.Application mypackage = foo..LoadPackage(strimportPkgFilename, pkgevents) results = myPackage.Execute(Nothing, Nothing, pkgevents, Nothing, Nothing) </code>
<error> A first chance exception of type 'System.Runtime.Remoting.RemotingException' occurred in mscorlib.dll Exception in: frmMyForm.DoImports Message: Object '/b76f98a0_5bd9_49d8_a524_eeb49d55b303/bqbhkjnaofq_ifr_cwz+srid_1.rem' has been disconnected or does not exist at the server. </error>
oddly, this same code works perfectly if I run it within a windows form application no matter how long it takes.
It also runs fine if the package can complete in under six minutes.
There are many Errorcodes which SSIS uses to represent errors, warnings etc.
List of errorcodes are at: http://msdn2.microsoft.com/en-us/library/ms345164.aspx
Now, in my SSIS package, I have defined error handler(OnError) and warning handler(OnWarning) to handle the events. Which logges them to a flat file with some formatting and condition checks including addional information about faulty row.
I dont want to log the exact error messages sent by SSIS as they are too much technical and not understanadble by normal user. I want them to be replaced with some custom message which I 'l pick from some resource file by matching the ErrorCode. One can consider this resource file as collection of key(ErrorCode), value(Custom Error Message) pairs.
In .NET we normally use resource files(.resx) to achive the same task. I found no way to use such resource files in SSIS.
Is there any way by which I can implement this functionality?
I dont want to implement custom logging mechanism as it does not fit into my application needs. Please guide.
I've run into a problem with SSIS packages wherein tasks that write or copy files, or create or delete directories, quit execution without any hint of an error nor a failure message, when called from an ASP.NET 2.0 application running on any other machine than the one where the package was created from. By all indications it appeared to be an identity/permissions problem.
Our application involves a separate web server and database server. Both have SQL Server 2005 installed, but the application server originally only had Integration services. The packages are file system-deployed on the application server, and are called using Microsoft.SqlServer.Dts.Runtime methods. For all packages that involve file system tasks, the above problem occurs.
When the above packages are run using the command prompt (either DTEXEC or DTEXECUI) the packages execute just fine. This is expected since we are using an administrative account. However when a ShellExecute of the same command is called from ASP.NET, the same problem occurs.
I've tried giving administrative permissions to the ASPNET worker process user to no avail.
I have likewise attempted to use the SQL Server Agent job approach but that approach might not be acceptable for our clients since it means installing SQL Server 2005 Database services on the application server.
I have read the relevant threads in this forum, namely http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1044739&SiteID=1 and http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=927084&SiteID=1 but failed to find any solution appropriate for our set up.
We manage some SSIS servers, which has only SSIS and SSIS tools installed on them and not the sql server DB.
SSIS packages and configuration files are deployed on a NAS. We run the SSIS packages through DTEXEC by logging in to the server.
We want to allow developers to run their packages on their own on the server, but at the same time we dont want to give them physical access on the server i.e we do not want to add them into RDP users list on server properties. We want them to allow running their packages remotely on the server.
One way We could think of is by using powershell remoting and we are working on that. But is there any other way or any tool already present for the same.
I am new to SSIS and am looking for any good websites, screencasts, podcasts, etc on the subject to help me learn. What would you guys recommend? All links welcomed.
I am already a big fan of Jamies site (http://blogs.conchango.com/jamiethomson/) and this forum but am looking for more resources.
Update: Before someone says I dont pay attention... I am aware of the post at the top of this forum with some links by Phil Brammer. I am simply looking for additional resources.
Does anyone here have a favorite site or set of sites with resources on SSIS custom component (tasks, transforms, log providers, etc.) development? I've been searching around to try to find this on my own, but so far I've had little luck.
Darren - I've noticed that you always seem to have the most insightful responses to questions related to SSIS .NET development (such as your recent response to evaluating expressions in a custom component) so I am particularly interested in anything that you have to share.
I have a big problem and i'm not able to find any hint on the Network.
I have a window2000 pc, VS2005,II5 and SQLServer 2005(dev edition)
I created an SSIS Package (query to DB and the result is loaded into an Excel file) that works fine.
I imported the dtsx file inside my "Stored Packages".
I would like to load and run the package programmatically on a Remote Scenario using the web services.
I created a solution with web service and web page that invoke the web service.
When my code execute: Microsoft.SqlServer.Dts.Runtime.Application.LoadFromDtsServer(packagePath, ".", Nothing)
I got the Error: Microsoft.SqlServer.Dts.Runtime.DtsRuntimeException: The package failed to load due to error 0xC0011008 "Error loading from XML. No further detailed error information can be specified for this problem because no Events object was passed where detailed error information can be stored.". This occurs when CPackage::LoadFromXML fails.
The error message doesn't help so much and there is nothing on the www to give me and advice....
Hi!I have installed Sql Server 2005 Express on my machine. I want to access this server from another machine in LAN.Also installed Sql Server 2005 on another machine. Allow Remote Connections is selected with Sql Server Surface Area Configuration.Please suggest the correct solution. (in detail) Thanks in advancemilind
I'd recently posted a question about using SQL CE as a database server for a multi-user desktop app. I did some development and tested it, and it seemed to work fine. What I did was:
1. create a remoteable object that used SqlCe classes to perform read and write operations to an encrypted CE database.
public class RemData : MarshalByRefObject
{
public DataSet GetData()
{
//Read data }
public int AddData(DataSet data)
{ //Write data } }
2. hosted this object in a Remoting Server
TcpServerChannel channel = new TcpServerChannel(props, bp);
// Register the channel with the runtime remoting services
So, basically the CE DB is running in-proc with this Remoting Server. This is hosted on a regular P2 1GB box.
3. created client WinForms app to connect to this object through remoting with url tcp://myserverip/RM_RemData and distributed this client EXE to various machines within the intranet to execute the GetData and AddData methods
This seems to work perfectly fine and super fast, and i was also concurrently executing the above methods in loops of 100.
So what I don't understand is why most of the posts I read about multi-user scenario here and on the web are always discouraging people to only use CE for single-user desktop? As long as I use the SQL CE ONLY as a Data Store and all logic into my data layer such as the Remotabe Objects, will this be a feasible option for around 10-20 Users since CE allows 256 Connections anyway?
My other questions are with regards to programmatically Import/Export to and from CSV and Excel..is this supported or anything planned?
Would appreciate a detailed response..my product hangs in balance as i need some closure on this
I have an SSIS package that imports data. It's working fine except I've been told that the Load_Date field needs to have the same date for each record in the table (the table gets truncated and then loaded each time the SSIS package runs). I created the table with a default of getdate() for Load_Date but I've been told not to do it that way and to have a variable in SSIS to populate Load_Date to make sure it's the same for every record. I've set up a variable in SSIS. I made it a datetime type of variable. It defaults the value to the current date (at that time) but doesn't let me change it to getdate(). I think the way to use the variable is to have a derived columns step that uses the variable Load_Date and then in mappings, set the column equal to the variable. But how do I set the variable equal to getdate() so it can be used for each row?
I've created an SSIS package that loads data from source to destination, using Lookup and conditional Split to check New rows and changed rows for one table.
Now I want to take this father by loading data for multible table more that 100. I did it in T-SQL using dynamic sql and cursor.
Has anyone attempted to perform a load test with SSIS? I am trying to saturate(100% processor utilization) a HP integrity rx4640 server with 60GB of ram and 4 processors, windows 2003 and Sql server 2005 in order to to build a sizing document?
We are in the process of converting our existing incremental loads from DTS to SSIS.
Currently we get all the data for the past month into temp tables in the warehouse, compare with key fields add the new rows and update changed rows. All this is done using Execute SQL task.
Is there a better way to implement the incremental logic using SSIS any new objects that be used to avoid too much SQL codes? Performance is very important and we do a lot of aggregation after the load for the reports to run faster so that we can meet customer SLA's.
We have around 20 tables that needs to be loaded 4 have large amount of data between 20 and 40 million rows out of which we will be brining over around 100 thousand during each incremental run. The other tables have less than 100,000 rows so does not hurt truncating and reloading the entire table.
How to extract and Load Data using ADO.NET in SSIS.i hope to extract data we have DataReader source .but how to load (Insert) data with ADO.NET ?.and is ADO.Net quicker than OLEDB ?
Hi everyone. I'm trying to figure out how to run an incremental load into a Staging table.
At this point I'm not trying to Conditional Split it between "New" and "Changed" records... just the load.
The logic in my head says that after each load, you can take the most recent "modified" date/time and store that in an incremental load table. That way, next time you run an incremental load, you just have to look up that "modified" date/time, and only load the source records with a "modified" date/time later than the record in your incremental load table. Does that plan sound feasible?
I think so far my problem is that my source is on an ADO.NET connection, and my incremental load table is on my SQL Server. So when I do my load from the ADO.NET database, I cannot read the data from the incremental load table.
Is my logic flawed? Any help would be appreciated.
I have created SSIS (.dtsx) files and have stored in different servers. Now my query is I want to move all dtsx files from filesystem to Sqlserver2005 database how should i do it.
I need to create the ssis package in business intelligence developement studio i am need to sqlserver 2005.When i opened the BID studio i am not able to see the integration services packages type.. Please help the steps to design the package.
I have experience of using the 2000 in dts designer mode.
I upgraded to Microsoft SQL Server 2005 Service Pack 2 and now when I run the master SSIS package( that has several packages in it), all the packages run twice.
After removing SP2, they work fine. Any ideas how to make this work with SP2?
I am writing a vb application that is supposed to let the users set the connection string for the datasources in the package. After new connection strings are entered the application is supposed to run 8 packages in a certain order, but I haven't been able to set a new connection string successfully. Is there a way to programmatically modify the connection string of a package's datasource? (the packages are moving data from a D3 database to sql server 2005)
Here is what I have tried so far:
A. Dim pkgLocation As String Dim app As Application = New Application() pkgLocation = "c:Package1.dtsx" Dim pkg As Package = app.LoadPackage(pkgLocation, Nothing) Dim myConns As Connections = pkg.Connections
MessageBox.Show(myConns(0).ID.ToString) Dim myConnMgr As ConnectionManager = myConns(0) Dim connProperties As DtsProperties = myConnMgr.Properties
I am connecting to a DB2 mainframe to pull data into SQL 2005. Very simple import. SSIS package works fine on 32 bit. However, once deployed to the 64 bit machine, I get "invalid product license" on the Acquire Connection method.
I've worked with IBM support. I have the correct version of the DB2 Connect client installed. The license is there and in the right place. I can connect to the mainframe from the 64 bit server using the DB2 client tools. I just can't seem to execute the package from Integration Services or run a job in SQL Server that executes the package.
According to BOL, the package should automatically detect the 64 client I installed. It and the 32 bit client I developed with share the same name/id.
I read in Kirk Haselden's book "Microsoft SQL Server 2005 Integration Services" that if SQL Serfver 2005 and 2000 are installed on the same machine as seperate instances then you can view the SQL Server 2000 DTS packages in 2005 Management Studio under the Management tree, Legacy, Data Transformation Services node.
But in my case, I am not able to see DTS packages in Management Studio. Is there a property or a setting that we need to configure for that?
