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


Top 5 Contributors of the Month
Sharon Maxwell
Post New Web Links

SSIS Lookup Transformation Issue

Posted By:      Posted Date: August 28, 2010    Points: 0   Category :Sql Server
 
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  


View Complete Post


More Related Resource Links

Datasheet view lookup column issue

  

When a column that has a lookup is empty in datasheet view and I attempt to update a different column I get this message: the text entered for isn't an item from the list. select an item from the list, or enter text that matches one of the listed items.

I found this hotfix, but it did not apply.

http://support.microsoft.com/kb/948952

Any ideas how to get this resolved?


SSIS Changing Column Order during Transformation

  
First let me say, I really can't believe this chain of events myself--and they are happening to me. I am upgrading several DTS packages to SSIS on what will be my new production server.  These packages create tables, export them to a flat file, and ftp them off to other locations. What is happening (on the SSIS side) is that the OLE DB Source is reordering some of the columns on its own (moving them to the end of the table/file.  Then when my pickup/load routines run, the data is out of place and they fail. Can anyone please explain what is happening here with the mapping.  I have evaluated the table and the columns are in the order that I expect.  When I preview the source table in the OLE DB Source Editor the columns are in the correct order/alignment, but when them in the OLE DB Source Editor --Columns section within BIDS the order is changed arbitrarily. I have been somewhat successful (2 out of 3) in being able to re-map the data, but this last table just doesn't want to change.  Thanks in advance for any help and/or information you can provide

SSIS And Sybase Source OLEDB connectivity issue with code page for character set

  
Hi I am using SSIS 2008 to connect the Sybase Server version 15.0.3 64-bit. The default character set id used by this Sybase server is 4 (i.e. roman8). (http://manuals.sybase.com/onlinebooks/group-charc/chg0300e/charsets/@Generic__BookTextView/1706;pt=266) I had installed Sybase Client for Sybase OLEDB Driver (ASEOLEDB) to integrate this in OLEDB Source Editor. As per guidance from one of the past MSDN forum discussion, I tried setting property of AlwaysUseDefaultCodePage property to "FALSE"  (http://social.msdn.microsoft.com/forums/en-US/sqlintegrationservices/thread/f5af9328-e1fb-48d7-a85d-1d08bb7cf0e5/). However, I am still experiencing error message as follows:- ********************************************************* Error at Package2 [Connection manager "xxxxxx.xxxxxxx"]: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available.  Source: "ASEOLEDB"  Hresult: 0x80004005  Description: "[00000] [Native Code: 30061] [ASEOLEDB]Could not load code page for requested charset". An OLE DB record is available.  Source: "ASEOLEDB"  Hresult: 0x80004005  Description: "[1ZZ001] [Native Code: 30016] [ASEOLEDB]Internal Error". Error at Data Flow Task 2 [OLE DB Source [1]]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMC

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

SSIS Package execution issue

  
HI ALL i am in great need,i am using ssis 2008 i have a package and two task in it..both task have OLEDB source and OLEDB desination when i execute the package whole data get dump from source to destination .but still my package does not show that it has been executed successfully..means package still show yellow color (processing mode)..not showing green color even after dumping whole data BOth package run one after other first dataflow task i have join with second data flow task....(second task start loading data but still fist package that has loaded data is in yellow color)

Compatibility issue between Job and SSIS Pacakge

  
HI All, I have created package in SSIS which process the cube in Incremental mode,but what problem i am facing when i run the package first,its runs successfully but not updated the cube while when i run the package secind time it upload the data in to the cube and this happens regulrly. This happens with processing mode too.  Kindly Suggest what mistake i have commited.   Shraddha  

lookup error o/p exel issue

  
I created one ssis package ,I configure lookup error o/p and provided exel destination .When I am running package again and again ,the error row is adding in exel destination file .I want different exel file whenever I run the package.Thanks

Lookup field issue after restore

  
After restoring a site collection in MOSS 2007, lookup fields cannot link up with the parent list. May I know if this problem fixed in MOSS 2010? Besides, the list item GUID in MOSS 2007 are changed after restoring. Does this case still appear in MOSS 2010? Thanks!

LookUp Transformation

  
Hi All I have a flatFile which has 3 columns. Serialnumber,name,string. And i have a DB Table called dbo.devices and i have two columns in it deviceID,Serialnumber. In this table for each serialnumber ,deviceid is a identity generated column. Now i want to replace the SerialNumber Column in the FlatFile with the Correspoding deviceID column in the DB Table. My flat files will have app 20ooo Rows and i have  7000 Files. How can i accomplish this Task. This is just a one time task but i want it to be done in faster way. Am planning to use a Lookup Transformation For this

Issue Adding a new Lookup Field to an existing List Instance

  
I have a code base that houses all of the column, content types, template, and list instance definitions. Am using the Update functionlaity a lot from version to version. Has been working well to this point. I just added two new columns (fields), one a Lookup and the other a Multi-Lookup. The fields are added fine on the update, columns look correct and the content type to which these were added also look fine. I can create a new List instance from the content type, and the new lookup columns are fine. The problem is this. Any existing list instances that were created from this same content type, now blow up when I try and access list items via the SP GUI. Something about a valid GUID, I dont't have the exect error anymore, but will repost it when I recreate the issue for the tenth time. I tried deleting the new lookup fields from this new list instance, thinking that I might be able to re-add them to fix the problem, but I get a new stack trace when I try and re-add them. Any hints on where I can start digging would be appreciated. I think I'll start with comparing the list instance schemas between an existing list and a newly created one.  

SSIS issue with dynamic excel sheet name

  
Hi Team, I am creating a Excel file dynamically using a script, excute sql and then the dataflow. I am not able to cretae a excel sheet name without an underscore exisitng file: "team data" even though am giving the table name in excute sql task(which is excel sheet name) as "team data" it is creating the name as "team_data" because of which the package is failing? I am able to achieve this with an underscore, as it is a client requirement to generate wihtout an underscore am posting this question. Is this an issue with Sql server 2005? Thanks, Eshwar  

Script Component Transformation as a Lookup

  
Hi Guys, I have the following code from my Script Component as a Lookup. Imports System Imports System.Data Imports System.Math Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper Imports Microsoft.SqlServer.Dts.Runtime.Wrapper Imports System.Collections.Generic Imports System.Data.SqlClient <Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute()> _ <CLSCompliant(False)> _ Public Class ScriptMain     Inherits UserComponent     Dim connMgr As IDTSConnectionManager100     Dim sqlConn As SqlConnection     Dim sqlCmd As SqlCommand     Private vTaxAmt As Decimal     Private vDelChgAmt As Decimal     Public Overrides Sub AcquireConnections(ByVal Transaction As Object)         connMgr = Me.Connections.Connection         sqlConn = CType(connMgr.AcquireConnection(Nothing), SqlConnection)     End Sub     Public Overrides Sub PreExecute()         sqlCmd = New SqlCommand("SELECT ItemTypeCode FROM LkpItemDepartment WHERE (ItemDeptCode = @ItemDeptCode) AND (ItemClassCode = @ItemClassCode)", sqlConn)                 With sqlCmd.Parameters   

SSIS Issue

  
Hi Guys/Gals I’m trying to schedule a SSIS(SQL Integration Services 2008) package containing a FTP Task that copy files to a remote server. The problems is if I run this package in  Visual Studio is works 100%. When I schedule that exact same package using SQL Server the package run successfully but no files was copied. There is no error message or nothing. Is their a way to track this log   Thanks in advance for any help with this one   RTTAdmin

Relation List Lookup Issue

  
Hi, I have 2 lists that are related. List 1 - costs ----- cost - Number quarter - Drop Down Created By - Default SP Field List 2 - people ----- person - User/Group AD Lookup team - drop down I am trying to link these 2 lists so I can retrieve the users team from the people list in a view for the costs. I have tried making it so the user has manually select their name for each cost (instead of using the Created By field), but when I try to make this value a lookup it doesn't allow me to select the person or team field from the people list? Does a lookup only allow you to lookup text values and is there a work around? Many Thanks, Michael
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