Running Multiple Instances Of The Same Package In Parallel
Feb 13, 2006
In my application code I am trying to invoke multiple threads in which each thread is loading an instance of the same SSIS package and would initialize the package variables with different values and execute the different instances in parallel. In each thread - after the package execution has completed successfully - I read that instance's SSIS package variables to get result information from that Instance run.
When I load the same package in different thread using LoadFromSqlServer() method
- does the code create multiple instances of the SSIS package and load the distinct instances in each of the thread
- Will the Package Execution ID be different for the different instances?
- Are the package level variables instance safe?
Can we execute multiple instances of the same SSIS package simultaneously?? If yes, how? If no, what is the work-around to simulate such a functionality?
I have a package that is doing some file transformation (Text, XML, and Excel) job based on a variable value. This package is called by a Parent package, where I am calling this package parallel through a script Task. So there are three parallel script task and all variables are local to script task.
In Script Task I am assigning value to child package variable using following code.
We have a DTS package that is submitted every minute by a SQL Agent job (2000). We see instances where before the first job is completed, the second one starts running.
Has anyone encountered this type of behavior before?
I have a website running under Windows Server 2003 which I am trying to deploy Reportin Services to using a custom security extension using Forms authentication. There is also another website which is just a Report Server running on this IIS 6.
On my development machine, XP with IIS 5.1, the Website with Reporting Services using forms authentication works fine, shares cookies for authentication and all that jazz. but trying to configure a second instance of Reporting Services on Server 2003 is not behaving.
Is there some trickery or details involved with running two instances of Reporting Services?
Also in the Configure Report Server tool it is failing and so not initializing on the last step of the Database Setup
Setting Connection Info for Reporting Server
ReportServicesConfigUI.WMIProvider.WMIProviderException: An error occurred when attempting to connect to the report server remote procedure call (RPC) end point. Verify that the Report Server Windows service is running, and then retry the operation.
---> System.Runtime.InteropServices.COMException (0x800706B3): The RPC server is not listening. (Exception from HRESULT: 0x800706B3) --- End of inner exception stack trace --- at ReportServicesConfigUI.WMIProvider.RSReportServerAdmin.ThrowOnError(ManagementBaseObject mo) at ReportServicesConfigUI.WMIProvider.RSReportServerAdmin.ListReportServersInDatabase(RSReportServerInfo[]& serverInfos)
I am running multiple instance of the same application. This application is connecting to the database and running stored procedures using ADODB (all code examples are taken from msado15).
The problem is that somehow these two applications are sharing something either with the connection or commands.
For instance if the two instances are in the following function at the same time then they both thow an SEH exception:
The exception that occurs is: "First-chance exception ...: 0xC0000005: Access violation reading location 0x00000068."
And afterwords when they go to release the command an error occurs on First-chance exception at 0x4de4120c in IpsEngine.exe: 0xC0000005: Access violation reading location 0xcccccccc.
Code Snippet
inline void _bstr_t::Data_t::_Free() throw() {
if (m_wstr != NULL) { ::SysFreeString(m_wstr); } if (m_str != NULL) {
The command and connections are not static and there should be completely seperate instances of these for each instance of the application. Does anybody know why this may be happening. Any help would be appreciated. Thanks in advance.
I'm looking at installing 2008R2 and 2014 side by side, then using Mirroring to provide HA for the 2008R2 instance and AoHA for the 2014 instance. I'd be using the same two physical servers for both the Mirroring pair and the AoHA pair.
My client has a server running MS 2003 Server for Smal Business Server, and it has SQL Server Express 2005 installed (I believe it is installed 3 times, as part of SharePoint, Veritas Backup Exec and ACT! applications).
I am trying to implement an application that uses SQL Server 2000, but the Server 2003 SBS does not support that version. So I need to launch an additional instance of SQL Server Exp 2005.
To do so, do I need to download and install SQL Server Express 2005 software again? Or can I just run SQLSVR.exe from an existing instance, but with a different instance name (sqlservr.exe -s<InstanceName>?
The reason I need another instance is that these existing instances limit DDL statements.
I am new to this and compare it to launching MS Word multiple times - you don't need to reinstall Word to run it multiple times.
Is launching from the command line a bad idea - and if it is, what is the better way to ensure that it launches under the local system context a service?
Thanks in advance for any help and insights. It is much appreciated.
I have several sequence containers in one package that fire off execute package tasks. I would like each of the sequence containers to start at the same time when the job starts running. However when I set them up to do that, i get an error that the variable cannot be read because it is locked. I have the variables setup as readonly so not sure why they are being locked. When I run the package and have each sequence container fire off after the previous one ends it runs fine.
After hitting limitations in the SQL CLR world that bar us from invoking COM objects we are forced to use windows services to read the messages off the Service Broker Queues. Unfortunately we loose the auto activation feature in the Queues, but we can still read messages and perform the SQL work under one transaction.
We are going to attempt to take N messages simultaneously from the Queue, though N instances of a windows service. If the messages send to the queue are one message per conversation, will we be able to achieve having N readers take messages off simultaneounsly?
Thank you very much,
Lubomir
P.S. if anyone has a better approach to obtaining the message in "out of sql code" or invoking external (not assemblies stores in SQL server) code libraries, that would be etremely nice to hear. I have thought about invoking a web service through CLR, but that is probably too much overhead - MSMQ seems much more appealing than a web service;
I have a SQL Server 2000 instance running on a Windows Server 2003 box with 4 processors. SQL Server is configured to use all 4 processors, and use all available processors for parallelism.
I have created a simple DTS package which has 2 "execute external process" tasks with no precedence constraints between them. There are no connections required or defined for the two tasks (sequential processing is forced on tasks sharing connections). The DTS package properties have the "limit the number of tasks to execute in parallel" set to 4.
However, despite the above configuration, the two steps are never executed in parallel, but always sequentially.
Does anyone have any ideas as to why these tasks are not being executed in parallel?
Does an UPDATE statment lock the entire table or just the rows that will be affected by the UPDATE?
I ask because -
Can I run UPDATE statements in parallel on the same table on the same column. The need for doing this is because the table is a large fact table. I plan to execute the same UPDATE statements on different time sections of the data to expedite the processing.
If the UPDATE statment lock the entire table then I cannot run an UPDATE in parallel. If the UPDATE statement just locks the rows that will be affected then maybe I can because rows affected will be different for each UPDATE.
I try to find out how many jobs where run in parallel on my server in an interval of time. For example: between 1:00 AM and 2:00 AM there were MAX 66 jobs that run in parallel and MIN 4 jobs. I am not sure if I can find this info out from a system view or I need to play with sysjobhistory view.
I'm expecting to run 3 isloated version of the package with in first version VARA=1 VARB=0 VARC=0 second version VARA=0 VARB=1 VARC=0 third version VARA=0 VARB=0 VARC=1 but it doesn't seem like doing that the maxconcurrent variable is set to 40 to be on the safe side.
when I run I get
first version VARA=1 VARB=0 VARC=0 second version VARA=1 VARB=1 VARC=0 third version VARA=0 VARB=1 VARC=1
I have a system of SSIS packages in which several packages perform the same lookup on the same table. E.g., i have PackageA, PackageB and PackageC all doing a lookup on TableA. All of these packages are spawned by the same PackageD and run frequently. In some cases, there is an issue with concurrency on these lookups. I get the following exception :
" The ProcessInput method on component "LKP Lookup SecurityID" (6658) failed with error code 0xC004702C. 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.
"
The hex code of this exception corresponds to the following description : "DTS_E_BUFFERNOTLOCKED. This buffer is not locked and cannot be manipulated." That's as much as i could find on this.
My suspision is that the SSIS engine somehow figures that the lookup in these distinct packages is the same one and builds a shared version of the lookup table in memory. Then there is some sort of a multi-threading issue in accessing this shared memory which leads to the exception above.
Has anyone experienced this? Can someone shed some light on this?
I have done a search and have read some of the posts, but am left more confused than before. I am fairly new to SSIS. Here is my situation and what i am trying to accomplish.
I have a package that has a sequence container, in which there are multiple SQL tasks (about 20) running in parallel. I have checkpoints enabled, and FailPackageOnFailure enabled as well. If the package fails, when i re-run the package it will run the last task as well as all the other tasks. What I am looking to accomplish is when the package is re-run, have the SQL tasks that failed ran and not the previous successful tasks.
I think the best way would be via disabling tasks on successful completion of a task, where it writes the name of the SQL task to a temp table, but I am skeptical.
Can anyone point me in a direction to help me accomplish what I am looking for please.
I have data flow tasks, one which validates the import file and one which processes the import file if the validation passed. The validation runs and pushes the three row types to three different recordset destinations. When I enter the processing data flow task, I have three parallel trees processing each recordset saved in the previous task. I'm using a script component to generate the rows which are then sorted and merged with the production database to find existing records. Based on this, I split to an OLE DB command (running an UPDATE command) or OLE DB destination (to simply insert the records.)
In this particular case, all records are being updated and nothing is being inserted new. Two of the three trees will complete the sort but hang on the merge, split, and OLE DB command components. The other will do the same but also hang on the split.
In another case, I truncated each destination table before running the package and the package runs fine.
Are toes being stepped on in the data flow task and causing a deadlock?
Update: I removed the sort transformation and sorted the rows before pushing them to the recordsets and I still get the same results.
I have stumbled on a problem with running a large number of SSIS packages in parallel, using the €œdtexec€? command from inside an SQL Server job.
I€™ve described the environment, the goal and the problem below. Sorry if it€™s a bit too long, but I tried to be as clear as possible.
The environment: Windows Server 2003 Enterprise x64 Edition, SQL Server 2005 32bit Enterprise Edition SP2.
The goal: We have a large number of text files that we€™re loading into a staging area of a data warehouse (based on SQL Server 2k5, as said above).
We have one €œmain€? SSIS package that takes a list of files to load from an XML file, loops through that list and for each file in the list starts an SSIS package by using €œdtexec€? command. The command is started asynchronously by using system.diagnostics.process.start() method. This means that a large number of SSIS packages are started in parallel. These packages perform the actual loading (with BULK insert).
I have successfully run the loading process from the command prompt (using the dtexec command to start the main package) a number of times.
In order to move the loading to a production environment and schedule it, we have set up an SQL Server Agent job. We€™ve created a proxy user with the necessary rights (the same user that runs the job from command prompt), created an the SQL Agent job (there is one step of type €œcmdexec€? that runs the €œmain€? SSIS package with the €œdtexec€? command).
If the input XML file for the main package contains a small number of files (for example 10), the SQL Server Agent job works fine €“ the SSIS packages are started in parallel and they finish work successfully.
The problem: When the number of the concurrently started SSIS packages gets too big, the packages start to fail. When a large number of SSIS package executions are already taking place, the new dtexec commands fail after 0 seconds of work with an empty error message.
Please bear in mind that the same loading still works perfectly from command prompt on the same server with the same user. It only fails when run from the SQL Agent Job.
I€™ve tried to understand the limit, when do the packages start to fail, and I believe that the threshold is 80 parallel executions (I understand that it might not be desirable to start so many SSIS packages at once, but I€™d like to do it despite this).
Additional information:
The dtexec utility provides an error message where the package variables are shown and the fact that the package ran 0 seconds, but the €œMessage€? is empty (€œMessage: €œ). Turning the logging on in all the packages does not provide an error message either, just a lot of run-time information. The try-catch block around the process.start() script in the main package€™s script task also does not reveal any errors. I€™ve increased the €œmax worker threads€? number for the cmdexec subsystem in the msdb.dbo.syssubsystems table to a safely high number and restarted the SQL Server, but this had no effect either.
The request:
Can anyone give ideas what could be the cause of the problem? If you have any ideas about how to further debug the problem, they are also very welcome. Thanks in advance!
I have multiple SQL 2000 servers that hold data which I retrieve using a single SQL stored procedure. Unfortunately, I don't know how to retrieve this data asychronously (in parallel).
For example, I retrieve data from two linked servers using the following:
select * into #temp from openquery(SomeLinkedServer,'exec BigQueryHere') select * into #temp2 from openquery(OtherLinkedServer,'exec BigQueryThere') . . lots of manipulation of the temporary tables to get what I want, etc. . .
The problem I have with this is that there is no reason why the first query should have to finish before the second query begins (serially) because these are on separate servers. Is there a way to execute these so that they run at the same time?
In Management Studio Express, one instance of Sql Express is running, with User Instances. This in indicated by a green circle with a white arrow head.
At the same time I'm connected to an instance of Sql Express on a Small Business Server. This server instance just has a white circle, but is also running. Is this normal.?
I think this is a question for the specialists among us.
Can I use one general reporting server (installed on instance MAINREPORTING) for multiple customers who all have their own sql instance (CUST1, CUST2, CUST3, ..) I would use UserAuthenciation on the reportserver url to display the specific reports customers can use.
Is this possible, and what do i have to take care off concerning installation and/or configuration (especially on the reporting side) ?
I'm pretty new to SSIS but I've managed to cobble together a number of individual packages to refresh SQL tables from a 3rd-party database. Â Now, what I'd like to do is have a single package that I can use to invoke each of the individual ones. Since it will run on a quad, I'd like to invoke them such they'll run in parallel.
We are planning to upgrade vom SQL 6.5 to SQL 2000 and SMS 1.2 to SMS 2.0. We want to first migrate SMS 1.2 to 2.0 with the SQL 6.5 database. Afterwards we want to install SQL 2000 as a second instance on the sql 6.5 server and migrate from there our databases. Is this the best way to do this? Does anybody have experience with running two instances of different SQL-Versions on one server? and what kind of trouble can I expect to run into?
I want to enumerate all the RUNNING SQL instances[in a mixed env where SQL 2000 and SQL2005 are deployed] using SQL DMO. But any of the following methods would not help
I've made a query like the one in msdn (SELECT * FROM __InstanceCreationEvent WITHIN 10 WHERE Targetinstance ISA "CIM_DirectoryContainsFile" and TargetInstance.GroupComponent= "Win32_Directory.Name="e:\\temp""). I have 20 similar tasks for watching in different folders, but when there are too much tasks in parallel, it doesn't work anymore. I change the numbers of executables to 128 (in the general properties of the package (to test)) but it doesn't seems to work.
I don't understand why it works when there are only 1 or 2 (6 seems to be the maximum) tasks and not if there are more than 6.
Could you help me with this issue?
Configuration : Windows Server 2003, SQL Server 2005, SSIS, Sql Server Agent
How to get the list of instance of SQL Server 7.0/2000 running on the local machine inside my domain... I need to prepare the list of all sql instances.. pls help if possible to find details using sql query.
When i have multiple sql server instances running on one server, how to check the names of that instances with out connecting to sql server management studio.
i have sql server 2005 express running. during install, i had issues with a system dll that i found answers online and resolved. i am still very green, but i know much more now than i did at the time. i believe that as a result of the partial then failed installs i have multiple instances either fully or partially installed. when i am in the sql server mgmt express environment, it lists two sql engines. i only ever use one, and would like to safely remove the second without damaging or corrupting anything done with the other instance. my reasons for wanting to do this are twofold. first, i would like to free up the drive space taken up by the second instance. (i am a stickler for clean harddrive mgmt and maintenance). second, now that i have used the software and am somewhat more familiar with it, i would consider installing a second, intentional instance so that i may attempt to better customize some of the install-time settings and configurations. can anybody give me any pointers/ guide me in the direction of information on how to SAFELY go about this? thank you in advance for any help.
I created a second SQL2000 instance on a Windows 2000 server. The strange thing is that I can create an ODBC connection to the default instance but cannot for the new named instance. If I try from a client that has SQL installed on it, I can see both instances. Any idea why this is happening?
We're trying to decide if we should use a single large instance of SQL Server or multiple instances on the same server. This is for a dev environment.Have to limit max RAM per instance so no instance grabs it all.There will be I/O contention and CPU contention between instances since the different instances won't coordinate with each other.It limits the available RAM for In-Memory tables..It sets lower limits to the amount of RAM available for any given query, causing artificial "contention" even when there's nothing else running at the time.
According to the Task Manager, on my SBS 2003 Server, I'm running multiple instances of SQL. How do I determine which instance(s) are required to maintain server stability, and which are not. My Server resource use continues to be a perennial problem. Please advise.
