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


Top 5 Contributors of the Month
Imran Ghani
Post New Web Links

SSIS - Lookup Task (Duplicate Row)

Posted By:      Posted Date: October 12, 2010    Points: 0   Category :Sql Server
 

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!




View Complete Post


More Related Resource Links

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 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  

C# newbie stuck - trying to access column data in a SharePoint list in an SSIS script task

  
Hello, I'm sure this is the simplest question but I can't figure it out, even with Google's help. I am trying to stumble through some C# code in an SSIS script task and I am frustrated that I can't figure out how to do the easiest things.  I eventually want to find data in a column,and then use another list as a lookup to replace that value with another where the existing value matches a value in the lookup list.  So, the data in my (multiple choice) column might be "apples; bananas" and in another list I have a row that contains two columns, the first holding the value "Apples" and the second containing "Red Delicious" and my original column should read: "Red Delicious; bananas." But, alas, I can't even figure out how to see the data that is in a column. Here is my code: /*<br/> Microsoft SQL Server Integration Services Script Task<br/> Write scripts using Microsoft Visual C# 2008.<br/> The ScriptMain is the entry point class of the script.<br/> */<br/> <br/> using System;<br/> using System.Data;<br/> using Microsoft.SharePoint;<br/> using Microsoft.SqlServer.Dts.Runtime;<br/> using System.Windows.Forms;<br/> using Microsoft.SharePoint.Utilities;<br/> <br/> namespace ST_08becda4c05c49cd9f30ea76110076cd.csproj<br/> {<br/> [

Post Upgade task, Upgrading SSIS Pacakges to SQL Server 2008.

  
Hi, I am trying to upgrade sql 2005 packages to sql 2008 after doing in-place upgrade of DE and SSIS. Can I know what packageformat column in msdb.dbo.sysssispackages refer to, as according to http://msdn.microsoft.com/en-us/library/cc879336.aspx the value should be 2 if the package is in sql 2005 and it should be 3 if it is upgraded. But I am seeing only 0 or 1.   Can I know any other method to figure out version of the ssis packages? I am having issues upgrading SSIS Packages from 2005 to 2008, using SSIS package upgrade wizard.   Thanks for your help. Regards, KRanp.

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?

SSIS 2005 - Send Mail Task - signature appended to email is garbled - unicode problem?

  
Hi, I'm pretty new to SSIS so go easy on me. I have a Send Mail Task to notify if a file cannot be imported - the mailbody is created on the previous step by a VB.NET script task to include the name of the file and the path it's been archived to. The problem I'm having is that while the body of the email I've created is displaying fine, our company's Exchange server appends a signature to all emails, and this is coming up as undisplayable characters, presumably due to some kind of unicode encoding problem. I've tried casting the email body in an expression to DT_STR (doesn't work as DT_STR "cannot be converted to a supported type" which seems a bit odd but never mind), DT_WSTR (garbled signature), DT_TEXT/DT_NTEXT (strange error on this one - "Attempted to read or write protected memory") none of those ideas worked, and I'm a bit stumped now. Can anyone help? I'm using SSIS 2005 with SP3

create ssis package with muli task

  
Hi Friends, Pls solve my following query with example. now i am doing project using ssis 2005. in that 1. import data from multiple resources like .xls, .xml,db 2.Read only file path from created meta data table like dil_table_met (Filetype=.xls,filepath=c:\..) 3.In that meta table, i want to read only file path and check file path which filetype like .xls or .xml and then import to excel source and to db destination , the same via for all file type 4. next read data from resources import correct data into correct table and wrong data into error table 5.for all these above condition are in loop.   pls give a example for this its very urgent pls pls help me out for thisR.Vinothraja

send records as weblinks through send mail task in SSIS?

  
Hi All, i have a table with two columns and two records as follows. TypeOfReport                       Links InvalidRecords                http://ReportInvalid MissingRecords               http://ReportMissing --------------------- I have a package with execute sql task selecting * from the table above, full result set selected and an object variable created, it works good. but now when i connect it with send mail task, i wanna send email like Subject : Reports On InvalidRecords and MissingRecords MessageText : Links for the Reports : http://ReportInvalid                                                     : http://ReportMissing Can somebone help me with it. NOTE : i tried creating user and object variable but its good if i have only one record in table. i tried creating 2 user and 2 object variables as well but didnt work. Thanks

SSIS Script Task

  
I am trying get user input from a form created via script but the form flashes on the screen and then disappear...is there a special implementation for this?? The following code is in my main; FormIU   from = new FormIU();   from.Show();    

SSIS:package contains two objects with the duplicate name

  
public static void CreateDestDFC1() { destinationDataFlowComponent1 = dataFlowTask.ComponentMetaDataCollection.New(); destinationDataFlowComponent1.Name = "SQL Server Destination 1"; destinationDataFlowComponent1.ComponentClassID = "{5244B484-7C76-4026-9A01-00928EA81550}"; managedOleInstance1 = destinationDataFlowComponent1.Instantiate(); managedOleInstance1.ProvideComponentProperties(); managedOleInstance1.SetComponentProperty("BulkInsertTableName", "Employee"); managedOleInstance1.AcquireConnections(null); managedOleInstance1.ReinitializeMetaData(); managedOleInstance1.ReleaseConnections();  } //Second one here.. public static void CreateDestDFC2() { destinationDataFlowComponent2 = dataFlowTask.ComponentMetaDataCollection.New(); destinationDataFlowComponent2.Name = "SQL Server Destination 2"; destinationDataFlowComponent2.ComponentClassID = "{5244B484-7C76-4026-9A01-00928EA81550}"; managedOleInstance2 = destinationDataFlowComponent2.Instantiate(); managedOleInstance2.ProvideComponentProperties(); managedOleInstance2.SetComponentProperty("BulkInsertTableName", "Customer");  managedOleInstance2.AcquireConnections(null); managedOleInstance2.ReinitializeMetaData(); managedOleInstance2.ReleaseConnections(); } And its giving a error.can anyone say why? or can anyone change this? The package

Microsoft.SqlServer.Dts and related assemblies to develop custom ssis task are missing

  
Hi All, I tried to develop a simple ssis task but the problem that I can't refer the necessary assemblies like Microsoft.SqlServer.Dts.Runtime And also Microsoft.SqlServer.DTSPipelineWrap Microsoft.Sqlserver.DTSRuntimeWrap Microsoft.Sqlserver.ManagedDTS Microsoft.SqlServer.PipelineHost What is certan that they aren't installed within the GAC in my case, so where could I find them? I have SQL server 2008 entrprise edition Other question, should I use Microsoft.SqlServer.Dts.Design and  Microsoft.SqlServer.ManagedDTS which are missed too, or they are part of the 2005 version only Thank you   The complexity resides in the simplicity

Insert error description in sysjobhistory from ssis script task

  
hi. in ssis i have script task that, in some situations, raise error (Dts.TaskResult = ScriptResults.Failure). But in sysjobhistory table there is only shor description what happend (Description: The script returned a failure result.). Is there any way that i can set some system variable to describe what is error so that description ens up in sysjobhistory table? in the end, i want to look in job activity monitor and see that job is "red" and see the description that couses this failure.

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 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

Error Trapping in SSIS Script Task

  
Hi, I am processing a cube partition using an SSIS script task. The script works out the name of the partition which requires processing based on the system date.The problem I have is whenever there is a failure in the processing task the only error I get is that the script task returned a failure result. This is reported by the SQL job that calls the script task and also by SSIS package logging. I am running SQL2005 SP3How can I get my script to return a meningful error? Thanks in advance.

SSIS Script Task

  
Can I use a script task to recieve two date values to start a SSIS package? if so, how do I implement this?
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