Package taking 100% of the CPU when it is opened for editing in visual studio

I have a package that I was able to edit a week before. But now it is consuming all CPU memory (100%) and not letting me to edit the package (When I try to edit that it says Visual Studio Is busy even after an hour waiting).

Even though I have not changed anything, the package is behaving like this.

I would appreciate any reply on this?

Thanks in advance

What has changed between two runs? Did you install anything on your machine? What is the packaged doing -- any dependancy on external resources?

Thanks,

Bob

|||Hello Bob,
Thanks for your interest.

No change between two runs and I have not installed/Uninstalled any thing.
All other packages are working fine but only that package is taking lot of time.
Today I able to open the package and even able to select the components(Transformations) but It takes lot of time to respond.Around 5 minutes it takes if I switch from one dataflow to another.

|||

You have to figure out what is causing this delay. Copy your package into an temporary file and start cutting it piece by piece.

There is not much help we can offer you based on the amount of information you provided. Try to nail down your issue and come back with a more specific question.

Thanks,

Bob

|||I have the same issue, was there ever a resolution to this?|||

Hello,

I found the problem after waiting thrice for around 30 minutes to get some control on to edit the package. In first two attempts I had to manually stop the visual studio as it was taking longer than 30 minutes. But in third attempt I could edit the package after 40 minutes of waiting and even then it was taking 2 minutes for a single click.

The problem was all my source connections were replaced By Destination connections. L

Description:

In the package I had two connections one pointing to a source database and another pointing to a destination database (this has different schema than source database).

So now all source DB connections are replaced by destination DB connections.ie Package was referring to a destination database connection but fetching the data from source database connection.

The following figure illustrates it best.

Sorry could insert a picture into the postL

Interesting thing here is the package is not giving any design time errors about referred view not present in the database it is referring to.

Due to some restrictions, I have not run the package to check whether it executes or not. But I did not get why this replacement has been done by itself.

Now I got everything corrected except a dataflow.

Another problem:

For the dataflow I left uncorrected, again the package is behaving in the same manner (consuming all CPU) when I try to fetch the data from a view present in the source database.

But this is not package specific. Whenever I tried to refer to this view from any other package then also same problem occurring. And this problem was not there in the week before run.

Package Runs in 1 minutes in Visual Studio but takes 5+ (sometimes hanges) as Job

I have an SSIS package that when run from Visual Studio takes 1 minute or less to complete. When I schedule this package to run as a SQL Server job it takes 5+ and sometimes hangs complaining about buffers.

The server is a 4 way 3ghz Xeon (dual core) with 8GB ram and this was the only package running.

When I look in the log I see that the package is running and processing data, although very very very very very slowly.

Has anyone else experienced this?

Thanks for your help

Marcus

You need to identify what the differences are between the two executions. It could be network latency, utilisation of the box by other processes, the user that you're running it as....anything.

What happens when you execute the package on the Xeon using dtexec.exe rather than thru SQL Server Agent. Do you get the same?

-Jamie

|||

I don't klnow what is going on with my system. The above package has ran find since I posted this message. I'm now having issues with stored procedure that is being ran from a job.

I can run the SP from Management Studio and it completes. The CPU does spike above 90% but it completes.

When I execute the same SP from job I get the following error and ALL connections to the database are killed.

Msg 64, Sev 16, State 1: TCP Provider: The specified network name is no longer available. [SQLSTATE 08S01]
Msg 64, Sev 16, State 1: Communication link failure [SQLSTATE 08S01]
Msg 10004, Sev 16, State 1: Communication link failure [SQLSTATE 08S01]

I have taken the SQL out of the SP and tried to execute it directly from within the job and it still fails wth the same issue.

I have no idea what is going on or where to start looking.

Here is a copy of the SP that I'm trying to run from a job.... Help... I'm glad it's a Friday

CREATE PROCEDURE [dbo].[sproc_LoadData_FactMessageOpen] AS

SET NOCOUNT ON
TRUNCATE TABLE [dbo].[FactMessageOpen]

INSERT INTO [dbo].[FactMessageOpen] (

[DateID],[TimeID],[MailingID],[ProfileID],[MessageFormatID],[TransactionDateTime])
SELECT
(SELECT DateID FROM dbo.DimDate WHERE DateAK = CAST(CONVERT(varchar(10),X.TransactionDateTime,101) AS smalldatetime)) AS DateID,
(SELECT TimeID FROM DimTime WHERE Hour12 = LTRIM(SUBSTRING(CONVERT(varchar(50),X.TransactionDateTime,100),CHARINDEX(':',CONVERT(varchar(50),X.TransactionDateTime,100))-2,2)) AND Minute = SUBSTRING(CONVERT(varchar(50),X.TransactionDateTime,100),CHARINDEX(':',CONVERT(varchar(50),X.TransactionDateTime,100))+1,2) AND NoonFlag = SUBSTRING(CONVERT(varchar(50),X.TransactionDateTime,100),CHARINDEX(':',CONVERT(varchar(50),X.TransactionDateTime,100))+3,2)) AS TimeID,
(SELECT MailingID FROM dbo.DimMailing WHERE MailingAK = X.MailingAK) AS MailingID,
(SELECT ProfileID FROM dbo.DimProfile WHERE ProfileAK = X.ProfileAK) AS ProfileID,
(SELECT MessageFormatID FROM dbo.DimMessageFormat WHERE MessageFormatAK IS NULL) AS MessageFormatID,
X.TransactionDateTime
FROM
(
SELECT
ER.TEMPLATE_ID AS MailingAK,
ER.EMAIL_ADDR_ID AS ProfileAK,
MIN(ER.ACTION_DT) AS TransactionDateTime
FROM
TEST.MDB.EMAIL_RESPONSE ER
JOIN
TEST.MDB.ACTION_SUB_ACTION_LOOKUP AL
ON
ER.ACT_SUB_ID = AL.ACT_SUB_ID
WHERE
ACTION_CD = 'G'
GROUP BY
ER.TEMPLATE_ID,
ER.EMAIL_ADDR_ID
) AS X
ORDER BY
DateID,
TimeID

|||Please share your connection strings... Could it be that the user running the job (the user assigned to the SQL Server service account) doesn't have access to the remote server/database?|||90% cpu utilization seems like too much. I'm getting the impression that you haven't examined the query execution in SQL Server Profiler. Correct?|||

Hi

I actually ran the query through SQL Profiler and it didn't come back with any recommendations. I alos looked at the explain plan and every node is using an index.

does the query look poorley written?

thanks

Package instantiation

If i open a package inside Visual Studio 2005, what does this action mean? In another word, does it mean that, the package gets instantiated (i.e. a package object is created ) ?

If so, the package object should resides in the memory while the package is in the open mode.

In general, the answers to your questions are yes and yes. It is not clear, though, why you ask this. Is there something you have trouble with or just trying to understand the moving parts?

Thanks,

Bob

|||

Hi,

I want to monitor SSIS packages running on system,I tried it with using RunningPackages class but it gives me running packages when I call method of RunninPackages class,

Can I monitor SSIS package execution continuously ? from where I get running package object

Thanks,

Omkar.

|||The question is related to the multiple instances in the memory. If i have opened the package inside IDE and I may want to execute the same package using other approaches such as invoked from C#.NET application or dtutil etc. Therefore each individuate application has its own package instance inside the process address space. And there should be mulitple loading for pakcage.... Please comments if this is wrong.|||

Well, it depends on what your package does. Your package may have some external dependencies that could cause contention. You will have different instances of the package object in different processes, but they still may share some resources/services.

Thanks,

Bob

Package hanging on OLE DB Command with large result sets

I have an SSIS package (SQL 2005 SP2 and Visual Studio SP1) that does the following:

OLE DB Source --> Conditional Split --> OLE DB Command #1 --> OLE DB Command #2

The source reads from database A. Each row is variable-width and up to several KB wide, including two ntext columns.

Command #1 executes a stored proc in db A, using a bunch of inputs and two output parameters.

Cmd #2 executes an update in db B, using the two output params from cmd #1 as inputs.

When the rowset size is small, around 500, everything works fine.

However, when the rowset size is larger, around 5000, SSIS hangs when trying to execute cmd #2. The profiler shows that none of the cmd #2 updates are ever executed. No error messages are produced, and the connection never times out -- it just hangs forever.

If I replace the cmd #2 updates with a simple select, everything works fine. If I replace it with a stored proc that does an update, it hangs.

The work-around I came up with was to create a new table in db B, and do inserts into the table, but unless I'm missing something, this still seems like a bug...

Do you have the RetainSameConnection property set to true on the OLE DB connection manager you are using? Try that if not.

Package Fast in VS but slow when deployed to server

I have a package which completes in 3 minutes when ran from Visual Studio but when deployed to SSIS (same machine) it rans for more than 2 hours. What could be the reason for this?

Could you describe the package content and what does it do?|||

The package retrieves data from a source via a data flow task. The result is returned to the control flow as a recordset object. I have a ForEach container in which I am looping through the recordset. For each record, I am checking if corresponding records exists in another data source via SQL Task. If it exists I use another SQL Task to update it. If not, I add it via a third SQL task.

|||I never figured out what was causing this. Ended up redesigning the whole package and removing the foreach.sql

Package Fast in VS but slow when deployed to server

I have a package which completes in 3 minutes when ran from Visual Studio but when deployed to SSIS (same machine) it rans for more than 2 hours. What could be the reason for this?

Could you describe the package content and what does it do?|||

The package retrieves data from a source via a data flow task. The result is returned to the control flow as a recordset object. I have a ForEach container in which I am looping through the recordset. For each record, I am checking if corresponding records exists in another data source via SQL Task. If it exists I use another SQL Task to update it. If not, I add it via a third SQL task.

|||I never figured out what was causing this. Ended up redesigning the whole package and removing the foreach.

Package fails when I use ODBC connection (Fails on SQL Server Agent, OK in Visual Studio)

I did a small package with only one ODBC connection (Merant 3.70 32-Bit Progess). This package runs well in Visual Studio and fails when runs by SQL Server Agent.

Configuration:

SQL Server Agent on a 32Bit server.

The ODBC connection configuration in available on System DSN on this server.

The user of Server Agent have full access (Admin).

Connect Manager Provider: ".Net Providers\Odbc Data Provider"

SQL Server version: 9.0.3042

Error Message:

Executed as user: TEKCON\tcadmin. ...ion 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 16:50:33 Error: 2007-06-11 16:50:33.62 Code: 0xC0047062 Source: Data Flow Task DataReader Source [1] Description: System.Data.Odbc.OdbcException: ERROR [HYC00] [MERANT][ODBC PROGRESS driver]Optional feature not implemented. ERROR [HY000] [MERANT][ODBC PROGRESS driver]msgOpen: unable to open message file: PROMSGS ERROR [IM006] [MERANT][ODBC PROGRESS driver]Driver's SQLSetConnectAttr failed. ERROR [HYC00] [MERANT][ODBC PROGRESS driver]Optional feature not implemented. ERROR [HY000] [MERANT][ODBC PROGRESS driver]msgOpen: unable to open message file: PROMSGS ERROR [IM006] [MERANT][ODBC PROGRESS driver]Driver's SQLSetConnectAttr failed. at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode) at System.Data.Odbc.OdbcConnectionHandle..ctor(OdbcConnection connection, OdbcCon... The package execution fa... The step failed.

I created a .bat file with this instruction and It's run well:

dtexec /f "C:\Program Files\Microsoft SQL Server\90\DTS\Packages\Integration Services Project\testcom.dtsx"
pause

Why it's not running with SQL Server Agent?

Hi Daniel,

I've not seen this with ODBC sources before but I don't use a lot of ODBC sources. Based on your description of the problem I suspect the privileges on the SQL Agent account. If possible, log into an SSIS development workstation with the SQL Agent account credentials and try the package in Visual Studio. If it executes, then my suspicion is incorrect.

Hope This Helps,
Andy

|||

Thanks Andy,

I tried your suggestion: I logged into the SSIS development workstation with the same user than the SQL Server Agent. It's running with Visual Studio but fail in the SQL Server Agent.

Bye!

Daniel|||What type of step are you using in SQl Server gant job? Did you try using CmdExec type with the same command line you indicated in your previous post?|||

Hi Daniel,

You may want to try the following test: Change the package ProtectionLevel property to something containing the word "Password" (EncryptAllWithPassword, EncryptSensitiveWithPassword) and supply a nice strong password in the PackagePassword property. Save the changes.

Execute the package using the DTExecUI utility. You will have to supply the password to do so. If this succeeds, schedule the package execution using a SQL Server Integration Services Package job step type. Again, you wll need to supply the package password.

If this does not work, please copy the error and paste it in your response.

Hope this helps,
Andy

Package executing error

I wrote package using Integration Services and tried execute it on Visual Studio 2003 (Visual Basic). Package was loaded, but execute method has failed without any error messages. When I tried to run it on Visual Studio 2005, execution was successful.

Codes:

Dim App As DTSLib.IDTSApplication90 = New DTSLib.Application

Dim p As DTSLib.IDTSPackage90 = App.LoadPackage("d:\temp\Package.dtsx", True, Nothing)

p.Execute()

What can be the reason of the trouble?

Thanks,

Alexander

Only one .NET runtime can live in one process. Visual Studio 2003 uses .NET 1.1, and SSIS requires .NET 2.0 - so SSIS fails if the process has already loaded .NET 1.1.

You can use config file to change the .NET version used by applications developed with .NET 1.1 and force them to use .NET 2.0, but it will break Visual Studio debugging.

So it is highly recommended to use Visual Studio 2005. In Visual Studio 2005 you can also use managed API for SSIS - in Microsoft.SqlServer.ManagedDTS assembly, which is more convinient than using interop from DTSLib.

OWC, Pivot & "OR" queries

this is probably a very easy question but I can't seem to find an answer...

Is there any visual way of building a query in OWC that includes a couple of conditions included as 'OR'?

basically every dimension I drop becomes a condition but they all get added as 'AND' & I can't seem to have a way to do 'OR' - even cube browser in BI studio doesn't have this option...

You can do OR between members of the same hierarchy (hopefully on the same level). Just select multiple members from the same hierarchy.|||

if it was the same dimension it'd have been easy, but it's a bit complicated since it's a role playing dimension & so even though it's the same hierarchy & same level it becomes 2 different dimensions.

something like this: DateOpened.Year = 2000 or LastRenewalDate.Year = 2000?

|||You cannot do it in OWC. You will have to write your own MDX queries to do it.

overvue

i want to build an xml based, driven web site.......... with sql......... in c#.

However i looked into visual web developer over a couple of weeks and

liked some of the display tools......then when i went back into c# i noticed

some of the display tools werent available and vis versa.

Also in vis web dev there does not seem to be the "under the bonnet"

dataset...person binding source etc.

The question is which way should I go?

Can I build and refine a sql in c# and then drop it into vis web dev or am I

just making a lot of work for no reason.

I just want to start correctly and not end up in some sort of swamp.

I don��t know if I got your ight, but the drag and drop functionality is mainly available through the GUI designer of the webforms / controls. After dragging and dorpping the controls on the form the code behind will automatically produced and can be changed afterwards.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Jens.... thanks again

have included this time the exceptions .....is this any help.

Overflow Error: Visual Web Developer

I am using Visual Web Developer to design an interface to query an Access Database. Everything seems to work fine, but when I select the most intensive option I recieve the following error. As I said, everything seems to work fine, unless the user selects all the options I have made available. How do I resolve this issue without limiting the user?

Overflow

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.OleDb.OleDbException: Overflow

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[OleDbException (0x80040e57): Overflow] System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult) +177 System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult) +194 System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult) +56 System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) +105 System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior) +91 System.Data.OleDb.OleDbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) +4 System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +139 System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +140 System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) +83 System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +1659 System.Web.UI.WebControls.AccessDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +58 System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments, DataSourceViewSelectCallback callback) +13 System.Web.UI.WebControls.DataBoundControl.PerformSelect() +140 System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +68 System.Web.UI.WebControls.GridView.DataBind() +5 System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +61 System.Web.UI.WebControls.CompositeDataBoundControl.CreateChildControls() +67 System.Web.UI.Control.EnsureChildControls() +97 System.Web.UI.Control.PreRenderRecursiveInternal() +50 System.Web.UI.Control.PreRenderRecursiveInternal() +171 System.Web.UI.Control.PreRenderRecursiveInternal() +171 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5729

I can post my code as well. It's three group bys, two sums divided by one another on a 30mb Access Database.

Thanks!

It seems like this would be better posted to an Access or ASP.NET forum.