Resources About Load/Run SSIS Packages Using .NET Remoting.
May 22, 2006
Hello,
I don't have a specific problem, I'm looking about resources because this is the tipical case when something is working but I don't know why ..
On particularly I have to load and run a dtsx package from a window application and the package is located on the server.
I decided to use .NET Remoting.
I developed my "Remotable Object" which contains:
package = Microsoft.SqlServer.Dts.Runtime.Application.LoadFromDtsServer()
package.Execute()
etc....etc...
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?
Thankx
Marina B.
View 13 Replies
ADVERTISEMENT
Jan 25, 2013
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.
View 2 Replies
View Related
Feb 13, 2006
Is there any information around what the SSIS packages are doing in the first 5-10 seconds of execution, and ways to speed this process up?
View 3 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
Sep 12, 2007
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.
Any suggestions?
Mark
View 2 Replies
View Related
Apr 2, 2008
Hello,
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.
View 2 Replies
View Related
Jan 9, 2007
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.
Anybody got any idea on how to go about this?
View 33 Replies
View Related
Apr 22, 2015
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.
View 4 Replies
View Related
Jan 17, 2008
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.
Regards,
View 3 Replies
View Related
Dec 13, 2007
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.
Thanks in advance, everyone!
View 6 Replies
View Related
May 19, 2006
Hello,
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....
Is it a SSIS problem???
Thank you for any help!!
Marina B.
View 10 Replies
View Related
Oct 6, 2006
Hi,
I have a 3-tier app that is currently running on a simple single server setup. However, I have to adapt it to run on the following setup:
1) Client .NET app on client systems with remoting connection to...
2) Host .NET app with a DAL connecting to ...
3) SQL Server 2005 mirrored
Using mirroring, if the principle SQL server goes down it switches automatically to the mirror server.
My question relates to my connections. What do I need to do to make sure the Client and Host apps follow SQL?
There are two setups I have in mind:
1) Host app + SQL on same box
2) Host app on its own server + SQL on its server (+mirror)
With these scenarios:
Setup 1 - SQL fails but server continues - Host app has to connect to mirror - clients continue to connect to Host on principle server.
Setup 1 - Whole server fails - Host on mirror server connects to mirror SQL and clints now have to locate Host on mirror.
Setup 2 - SQL's server fails so mirror switches in. Only Host app has to re-direct. Clients connect to same Host app.
This is new to me, so if anyone can advise, or direct me to the right info.
View 3 Replies
View Related
Jun 2, 2005
who else can help me? My sql needs database remoting copy.
View 1 Replies
View Related
Mar 15, 2007
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
View 2 Replies
View Related
Jan 29, 2004
i am using sqlserver 2000,if so how,thanks.
View 2 Replies
View Related
Nov 1, 2007
Hi,
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
ChannelServices.RegisterChannel(channel, false);
RemotingConfiguration.RegisterWellKnownServiceType(
typeof(RemData ), // The type to register
"RM_RemData", // The objectURI
WellKnownObjectMode.SingleCall
);
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
Thanks,
CP
View 3 Replies
View Related
May 3, 2007
How can I access data to SQL server 2005 using Windows form application,ADO.net and .Net remoting?
Can anybody help me? please...
View 5 Replies
View Related
Oct 3, 2007
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?
Thanks
View 2 Replies
View Related
Mar 4, 2008
Hi All
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.
How can I achive this using SSIS.
View 1 Replies
View Related
Sep 14, 2006
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?
View 1 Replies
View Related
Jan 18, 2007
Hi,
I have a little experience with SSIS (but a lot with DTS) and none with CRM 3.0.
Is it possible to use SSIS to import flat files into CRM 3.0 ?
And how ?
I read something that CRM 3.0 uses SQL server 2000. Should I upgrade it SQL server 2005 ?
Any suggestion where to read about this ?
Thanks in advance
Constantijn Enders
View 15 Replies
View Related
Oct 19, 2006
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.
Any assistance is appreciated.
Thanks!
View 6 Replies
View Related
Nov 8, 2006
Hi,
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 ?
Thanks
Jegan.T
View 5 Replies
View Related
Apr 29, 2008
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.
Thank you very much!
View 4 Replies
View Related
Apr 24, 2006
Hi,
Is there a way to reuse existing Configuration Files in SSIS Packages?
I have two packages with the same connection managers, properties and variables.
I want to reuse the Config file of the 1st package in the second one. While building the packages it gives error like
“Config file already exists cannot recreate”.
View 1 Replies
View Related
Jun 8, 2007
Hi All,
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.
Please help
Regards
Hassan
View 2 Replies
View Related
Oct 16, 2007
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.
Thnks for your help in advance.
View 1 Replies
View Related
Mar 14, 2008
Hi,
iam New to Packages. So Please give me the guidelines to Learn Packages.
Thanks for Advance
View 3 Replies
View Related
Mar 12, 2007
Hi,
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?
View 2 Replies
View Related
Aug 24, 2006
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
connProperties(0).SetValue(myConnMgr, "notsupposed=towork;")
MessageBox.Show(myConnMgr.ConnectionString)
B.
ConMgr = p.Connections.Add("OLEDB")
ConMgr.ConnectionString = "Provider=SQLNCLI.1;Data Source=192.168.0.233; User ID=sa; Initial Catalog=Rmt_New"
ConMgr.Name = "SqlDatabase"
ConMgr.Description = "Sql Connection"
Any help would appreciated.
View 1 Replies
View Related
Feb 28, 2006
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.
Am I missing something? Any hints?
View 3 Replies
View Related
Feb 7, 2007
Where are SSIS packages stored and how can I see them in SQL Server Management studio?
View 6 Replies
View Related
Sep 15, 2006
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?
Thanks.
View 2 Replies
View Related
