.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Post New Web Links

SSIS lookup slow

Posted By:      Posted Date: September 07, 2010    Points: 0   Category :Sql Server
 
Hello,   Ik have a SSIS package which does a lookup for a WorkID in a employee_work table. The lookup is based on date and employeeID. It then inserts the correct workID in an sick leave fact-table. The lookup table has "only" 20,000 rows, it's indexed. The fact table is about 4,000,000 rows. This is the lookup  query: select TOP(1) * from    (SELECT WerkID, werk_start_KEY, ms120_obj    FROM dbo.DimMedewerker) [refTable] WHERE [refTable].[ms120_obj] = ? and [refTable].[werk_start_KEY] <= ? ORDER BY  [refTable].[werk_start_KEY] DESC But whe I run the package it only processes about 35000 rows, the it takes a time, then it processes the next 35000 rows, etcetc. How can I make it go Faster? At the moment it takes about 1.5 hours


View Complete Post


More Related Resource Links

SSIS Lookup slow

  
Ik have a SSIS package which does a lookup for a WorkID in a employee_work table. The lookup is based on date and employeeID. It then inserts the correct workID in an sick leave fact-table. The lookup table has "only" 20,000 rows, it's indexed. The fact table is about 4,000,000 rows. This is the lookup  query: select TOP(1) * from    (SELECT WerkID, werk_start_KEY, ms120_obj    FROM dbo.DimMedewerker) [refTable] WHERE [refTable].[ms120_obj] = ? and [refTable].[werk_start_KEY] <= ? ORDER BY  [refTable].[werk_start_KEY] DESC But whe I run the package it only processes about 35000 rows, the it takes a time, then it processes the next 35000 rows, etcetc. How can I make it go Faster? At the moment it takes about 1.5 hours

SSIS lookup slow

  
Hello,   Ik have a SSIS package which does a lookup for a WorkID in a employee_work table. The lookup is based on date and employeeID. It then inserts the correct workID in an sick leave fact-table. The lookup table has "only" 20,000 rows, it's indexed. The fact table is about 4,000,000 rows. This is the lookup  query: select TOP(1) * from    (SELECT WerkID, werk_start_KEY, ms120_obj    FROM dbo.DimMedewerker) [refTable] WHERE [refTable].[ms120_obj] = ? and [refTable].[werk_start_KEY] <= ? ORDER BY  [refTable].[werk_start_KEY] DESC But whe I run the package it only processes about 35000 rows, the it takes a time, then it processes the next 35000 rows, etcetc. How can I make it go Faster? At the moment it takes about 1.5 hours

SSIS Lookup Transformation Issue

  
I am having a strange issue, In my data flow i have a Lookup Transformaton which will match on key columns of the fact and is followed by a condetional split that would deside if it should insert (old db destination) or go to update (oledb command) or ignore if no change. I have packages running for the last 1 year with the same logic. But in the recent packages I am experiencing a problem.  example: Key columns for join are - type_no (varchar 16) with all numeric values except one record wihh ' '(space) in it  and type_cd (decimal(18,0)) with values(0,1,2,3,4,5) It worked fine when I test the package. After couple of day running in schedule I get integrity violation and huge file with failed records which are supposed to be blocked at the condetional split as they are already in the fact. When i add a data viewer what i found is for all the llokup columns its having nulls (no match found). Workaround that is working for me for now is - I select full cash and say ok in the lookup transofrmation and again open it and set it back to no cash. Then it starts working as expected. Did anyone come accross this kind of issue? is the some standard that I have to follow to make sure this doesnot happen again  

SSIS - Lookup

  
Hi, I am using Lookup component in my SSIS workflow. I am comparing a string. Is there a way to ignore the case while comparing?

Slow startup of SSIS package if started using a SQL Server job

  
Hello all,   We have noticed in our environment slowness when starting SSIS packages from SQL Server jobs. I did a quite detailed study on when the slowness actually occurs and what are the consequences. Here are the results.   The SSIS package execution is slow if all the following is true: The package is started from a job. If started directly as a SSIS package, the execution is fast. The job is running on a 64 bit Windows Server (SQL Server 2005 SP2). The SSIS package and the job are either on the same server or on different servers (the second server is SQL Server 2005 SP1). If the job is run on a 32-bit workstation (Windows XP SP2) the execution is fast (the SSIS package still being on the server). The package contains tasks. §         If there are no tasks, just an empty sequence container, the execution is fast. §         If a package that has no tasks has logging into the database configured, the execution is fast. §         Slowness has been verified with A) a package having a single Execute SQL statement and B) a package having a Send Mail task. It doesn't seem to matter which user account is used on when running the job.   The slowness happens in several locations, e.g. (there are also others, at least the following have been verified) There is exactly 30 seconds lag between starting the job (as seen from job history) and when PreValidate (as seen in the sy

SSIS 2K5 - Deploy package with C# - Really Slow

  

Hi,

For a DataWareHouse project, I did an WIX  install manager to Create à DataWareHouse, Deploy SSIS package and Deploy a OLAP cube.

When It come to deploy (load) dtsx package, it takes about 40-60 secondes to load each package (I have around 50 packages so its really too long)

This is the code i'm using :

	    DirectoryInfo di = new DirectoryInfo(session["INSTALLLOCATION"] + @"Sources");
        FileInfo[] rgFiles = di.GetFiles("*.dtsx");
        Microsoft.SqlServer.Dts.Runtime.Application app = new Microsoft.SqlServer.Dts.Runtime.Application();
        foreach (FileInfo fi in rgFiles)
        {
          string pkg = session["INSTALLLOCATION"] + @"Sources\" + fi.Name + "";
 		 Package p = app.LoadPackage(pkg, null); //THIS TAKE 40-60 SECONDE TO EXECUTE       

          

SSIS Dataflow Lookup failure using a Synonym

  

In a dataflow, I am trying to use a Lookup Transformation to join to a Synonym.  The preview button displays data, by clicking the Columns button results in "The Lookup transformation method, ReinitializeMetadata, failed."

 


SSIS packages run slow under Execute Package Utility

  

I have developed a dtsx package to load data from xml load table into a dozen of flat tables. I found the SSIS is running slow (almost 10 times slower) under Execute Package Utility than running under visual studio IDE.

Does anyone have same experience?

 


SSIS 2008 - Fuzzy lookup warming index takes too much time

  

Hello All,

We are using Fuzzy lookup transformation in our SSIS package.

We have selected option to store and maintain index for first time. And for subsequent executions, we use option to use existing index. So it will not create new index each time. We are doing fuzzy lookup on single source table with single column. The source table contains more than 2.5 million rows.

So while executing the package, although its not taking time to build complete index. But still it takes considerable time and uses large memory whenever we execute it.

We have also set the WarmCache option to true. Although all data is loaded into memory and removed after execution.

Is there any other way so we can keep the data in memory, so for subsequent execution, it gets result directly from memory?

Please let me know if you have any suggestion? Its urgent.

 

Thanks in advance.



Darshan Vyas

SSIS - Lookup Percentages values change

  

Good day, i need help please.

The problem i have is that a percentage colume in my table, when i sum the total = '100.0000000'

This fine, but when i insert in SSIS - Lookup & i preview the data in Lookup task the Total = '100.072' which makes my calculations incorrect.

is there something i need to do to keep it in its original sum - but i view my decimal places in advanced properties decmal(38,12) so this should be fine.

Is in the query that i have to perhaps force it to be 100.0000, how?   

Can somebody please direct me on what i should do

Regards

 


SSIS - Lookup Task (Duplicate Row)

  

Good day,

I'm having difficulty with a lookup task.

Firstly in my Lookup my query contains a "DISTINCT" and my cols match source as only some records fail, Source also has a distinct so no Duplicate there either.

I also added on error to display error records to a dataview. Now the lookup code does exist not sure why he is failing

The Lookup transformation found duplicate key values when caching metadata in PreExecute. This error occurs in Full Cache mode only. Either remove the duplicate key values, or change the cache mode to PARTIAL or NO_CACHE.

Is the records locking each other as source & lookup read from same table with diff filter criteria to gether with (NOLOCK) specified.

Please Assist!


Adding exception table to incremental Load with SSIS Lookup task and conditional split

  

Hello,

I have built an incremental load ETL Process to load some flat files with an SSIS Lookup and Conditional Split. I only have one path in the conditional split and that is for New Records.

I have two questions:

1.       For the new records path, I have to check to see if a record exists and I don’t have a single key that is unique, therefore, I have to use a multiple keys to make the record unique.

 

Below is what I have put in the conditional transformation editor for my first output condition:

 

SSIS - Data Flow Task Reads Fast But Importing the Output,Update & Deleting Extremely Slow & runs lo

  

Good day, I need help please.

In my source I read about 4mil records in a matter of seconds But Importing the Output to the Same Table takes about an 1 hour to update.

My Source Read is the Same Table to which i'm Importing, Updateing,Deleting

Importing: Table Lock & Check Constraints are checked

Please any ideas & suggestions will be great.

Thank You


SSIS Lookup - Mismatched sqlcommand & sqlcommandparam Property Values

  

I had an issue recently and I'd like to solicit feedback as to whether this is an SSIS flaw:

In the lookup component, when you select the Partial or No Cache option, a property in addition to the sqlcommand property becomes available called sqlcommandparam. Originally this package was developed using Lookups pointing to a dimension on "schema_A" and we wanted to change the reference to "schema_B". Now, obviously the sqlcommandparam property became available because you can elect to use the Advanced SQL option in the component. However, I never chose to do that. So, long story short, I go into this package to change the schema reference in the Lookup's query. It changes the sqlcommand property value to be "select value from schema_B.table" but it didn't change the corresponding value in the sqlcommandparam property. That property remained "select value from schema_A.table". SSIS never threw an error to say the schema's didn't match. And the Lookup worked as designed, picking up values from the table on schema_B. Not until we finally decommisioned schema_A did I get an error at runtime that said "Table or view does not exist".

So, can anyone (hopefully Microsoft) confirm that this is in fact a flaw? I am on MSFT VS 2008 V 9.0.30729.1 SP.


Brian

SSIS package runs very slow on SQL 2008 R2 server, runs very quickly on virtual XP machine

  

Hi All,

I've been troubleshooting this issue on and off for a few weeks now.  I've scoured these forums and looked through sqlis.com, but I'm just about out of ideas.  Here's what's happening.

I have a *very* simple integration services package I developed on a  virtual Windows XP machine running on my workstation.  The package uses a script task to simulate a one-row source with four columns (int, char(64), varchar(512) and char(2)) and then passes that off to a fuzzy lookup.  The fuzzy lookup is running against a table of only about 6,000 rows.  The package then inserts the results into a table and exits.

When I run this from my virtual XP machine, it completes in 1-2 seconds.

When I execute this on the server (either as a job or through DTExec) it takes 14-18 seconds.  The server stores the package in MSDB and is also the location where the fuzzy lookup happens.

I've tried using the 32-bit runtime, creating 32- and 64-bit aliases to the server, changing the name of the server from the name to the IP address and port, stopping/restarting SQL Agent and SSIS Services... none of this seems to change the execution time.

The one thing I've found is that if I enable package logging on the logging tab of the job, using the SSIS Log Provider for SQL Server and pointing it to the same connection st

Using Conditional Split data Transfer in SSIS 2008

  
This article uses the Integration Services Conditional Split Data Transformation element to filter and transfer data from a set of flat text files to SQL Server database table. The concept can be easily extended to apply to any other source or destination such as Microsoft Excel. This scenario is useful in creating denormalized database tables in a reporting and analysis situation.

Response.Redirect slow

  

I'm in the middle of migrating servers and on the new server Response.Redirect is extremely slow. It takes around 20 seconds for the browser to get the redirect. This is something that was quick on the existing server. It's an ASP.NET 2.0 application, both servers are running IIS6.

Additionally, if I "manually" do the redirect, it's fast. For example:

Existing way:


Response.Redirect(url);

takes around 20 seconds on the new server


New way:

Response.Status = "302 Moved Temporarily";
Response.AddHeader("Location", url);
Response.End();

is fast on the new server.


Any ideas why Response.Redirect would be so slow on the new server?


Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend