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


Post New Web Links

Data flow - Conditional lookup possible?

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

In my data flow I use a Lookup component to check if the row already exists in the data warehouse. I insert the row in the No Match output path and update in the Match path.

In the initial load it is a waiste to have a lookup for each row - I know the rows are not there. Is there a way to skip the lookup?

Ideal case (As I see it):
Initial load - No lookup which also means no index on business key in destination table, giving faster inserts as well
Diff load (Dest table might have 2 mill rows) - Lookup on index of business key

I have set a variable to Inital load - but I can't see how I can use the Conditional split - because I can find no way for 2 paths to point to the task after my lookup.

Hope someone can help.

 




View Complete Post


More Related Resource Links

Excel File as input paramter for Lookup Data Flow Transformation problems

  

Before I start, I'm using SQL 2008.

I have a Excel file with email addresses that need to acts at input parameters to a Lookup transformation. I have set the Excel Source to my file and specified the email field to be the output. I have dropped the Lookup Transformation Data Flow and connected the both. I'm going to execute a very simple stored procedure, and under the Connections section my SQL query looks like follows: EXEC Test_GetUserName ? 
When I run that I get an error saying that no parameter was provided. But when I run EXEC Test_GetUserName 'someemail@companyname.com' everything executes great, for the obvious part that the email is hard coded. How do I pass the excel input as the parameter?

Thanks for all the help.


There is 10 types of people in the world, those that understand binary, and those that don't.

Lookup conditional caching possible? or flush cache as data changes?

  

Hi Forum

Is there any way to switch caching setting on the lookup up component on-the-fly? I have a lot of lookup components that I want to use full caching setting - some times I know that there will not be a lot of data - so N lookups or partial caching would be better. I could indicate this by using a variable  - I could use a conditional split to make 2 paths - I could make 2 different lookup components but these two cannot point to the next task in the flow since it seams that all data flow task only take one input path.

I could really also use a way to flush the cache when ever a row value changes. In this way I could order the data by Company, and have it use full caching but flush the cache whenever the company id changes. Is this in any way possible?

Thank you in advance

Jay


How to update a variable in Data Flow component of lookup has rows that don't find matches

  

Hi 

I'm using SSIS 2008

I have a data flow that gets data from an Excel spreadsheet. It then does numerous lookups to my database to ensure that all the entities in the spreadsheet are present in my dimension tables. For each of these lookups, I redirect rows with "no matching entries" to be written to another spreadsheet which is then sent to an email address for action.

I have a Package scoped variable that I want to update if any of the lookups have rows with no matching entries. This is used later in the package to determine if it should proceed or not. So this variable only needs to be updated once if any of the lookups fail. 

 

What are the options for doing this? What is the best way?

Thanks


Craig Bryden - Please mark correct answers

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.

Dynamic WPF: Create Flexible UIs With Flow Documents And Data Binding

  

Flow documents offer enormous flexibility in arranging text layout and pagination, but they don't support data binding, so you can't dynamically change content. Here we build a component to solve that problem.

Vincent Van Den Berghe

MSDN Magazine April 2009


BDC Entity as a Data Source for a Lookup column

  
Is it possible to create a custom lookup field type to have a BDC entity as the data source?

How do I add two XML tasks to a Data FLow task?

  
I am new to SSIS and am developing a package which takes input from an XML file and then populates an existing SQL table with these records.  However, this is a very large XML file.  I keep getting an Out of Memory exception.  (This file is 210 MB)!  So what I tried doing was separating this XML file into 8 smaller files.  Now, I can successfully load data from these smaller files one at a time.  But how can I do all of them in the same package?  I connected two XML tasks to the Data Flow task and get the error below.  I also increased my timeout and number of errors to no avail.  Advice? Error: 0xC002F304 at XML Task 1, XML Task: An error occurred with the following error message: "Exception of type 'System.OutOfMemoryException' was thrown.". Task failed: XML Task 1 Warning: 0x80019002 at zSkywardEnrollment: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors. Error: 0xC002F304 at XML Task, XML Task: An error occurred with the following error message: "Exception of type 'System.OutOfMemoryException' was thrown.". Task failed: XML Task SSIS package "

How to detect that a Data Flow Task has failed (from inside a custom componet)

  
Hi, in the PostExecute Method of a custom component i would like to check if the Data Flow Task has been failed. Is that possible? Also it would be great to be able to check if this custom component already got all data or has been marked as "green".  Thanks, Dennis

Custom SSIS Data Flow Component Not Showing in Toolbox or GAC

  
Hello - I have created a very simple data flow component for SSIS (Actually, I am following this example:  http://www.microsoft.com/downloads/details.aspx?familyid=1C2A7DD2-3EC3-4641-9407-A5A337BEA7D3&displaylang=en).  However, when I register the DLL to the GAC, I am unable to find the assembly in C:\Windows\Assembly - even though the GACUTIL says "Assembly Registered Successfully".  Furthermore, after copying the DLL to the PipelineComponents folder for SSIS (C:\Program Files (x86)\Microsoft SQL Server\100\DTS\PipelineComponents), it does not show in the "Choose Items . . ." dialog box of SSIS.   I am running SQL Server 2008 Dev edition, Visual Studio 2010 with .NET 4.0, and Windows 7 Enterprise 64-bit edition.  Any assistance/thoughts would be appreciated. Thanks!

Data flow task with multiple destinations randomly hangs

  
I have a package whose control flow consisting of a single data flow task with multiple destinations continues to hang at random locations.  The data flow task is fairly simple: - One OLE DB source (one query of selecting about 10 fields from one table) - 9 lookups that serve basically as left joins to capture which data "drops off" (does a row redirect on on lookup errors to an "error flow") - Two destinations: one the captures all the data that doesn't drop off from the lookups, and another to capture the data that does drop off; these write to two tables unrelated to each other, and unrelated to any of the tables I'm reading from I have tried the following: - Changed one or both destinations from OLE DB to flat file -- still randomly hangs - Removed both destinations altogether -- this always suceeds.   - Removed destination A and leave destination B -- this always succeeds - Removed destination B and leave destination A -- this always succeeds I took great care in making sure I get no warnings when the package runs, even resolving the unneeded column warnings, and the duplicate keys on all my lookups, but it still hangs. However, once I give up and stop the package, the following error is thrown (among others that appear to be a byproduct of the root issue): Error: 0xC02020C4 at Data Flow Task, OLE_SRC AP12 [1]: The attempt to

What does strategy exist to deploy SSIS package and my own data flow components into a enterparise s

  
I created a SSIS package and several data flow componenets for this package.    What does strategy exist to deploy SSIS package and data flow components into a enterparise server?   Thanks in advance.

OLE DB Destination issue in ForEach Data Flow

  
Has anyone encountered the following problem:I have a ForEach container with a single task in it - a Data Flow. The Data Flow uses a Source Script transformation to read each file and writes out to several output streams, each of which is connected to a OLE DB Destination.The ForEach executes for each file (in my example 3 times) but only the data from the last execution is in the DB tables.This from is my output window:SSIS package "PIF.dtsx" starting.Information: 0x4004300A at ETL Audit, DTS.Pipeline: Validation phase is beginning.Information: 0x4004300A at Load V6 Policy, DTS.Pipeline: Validation phase is beginning.Information: 0x40043006 at Load V6 Policy, DTS.Pipeline: Prepare for Execute phase is beginning.Information: 0x40043007 at Load V6 Policy, DTS.Pipeline: Pre-Execute phase is beginning.Information: 0x4004300C at Load V6 Policy, DTS.Pipeline: Execute phase is beginning.Information: 0x402090DF at Load V6 Policy, stg_RiskLocation [5449]: The final commit for the data insertion has started.Information: 0x402090DF at Load V6 Policy, stg_PDMRFeed [5277]: The final commit for the data insertion has started.Information: 0x402090DF at Load V6 Policy, stg_Policy [5208]: The final commit for the data insertion has started.Information: 0x402090E0 at Load V6 Policy, stg_RiskLocation [5449]: The final commit for the data insertion has ended.Information: 0x402090DF at Load V6 Pol

Problem while retrieving data from a lookup column from sharepoint linq.

  
Hi I am working with sharepoint linq concept. When I am giving a query with lookup column It's working fine.  But when i am trying to retrieve the data from a look up column to spgridview. I was unable to get the output. Here is the above code which i have worked.   string strTitle = string.Empty; string strStatus=string.Empty; var context = new LinqSampleDataContext(SPContext.Current.Web.Url); EntityList<SharePoint2010ConceptsItem> Concept = context.GetList<SharePoint2010ConceptsItem>("SharePoint 2010 Concepts"); var query = from cs in Concept where cs.Status.Title.ToString()!="Completed" select new { cs.Title, cs.Id, //cs.Status }; sgvConcepts.DataSource =query; sgvConcepts.DataBind();   Any help me plz how to retrieve the data from a lockup column.bvnprasad

Problem with Retrieving data from lookup column using SharePoint 2010 Linq Concept

  
Hi I am working with sharepoint linq concept. When I am giving a query with lookup column It's working fine.  But when i am trying to retrieve the data from a look up column to spgridview. I was unable to get the output. Here is the above code which i have worked.   string strTitle = string.Empty; string strStatus=string.Empty; var context = new LinqSampleDataContext(SPContext.Current.Web.Url); EntityList<SharePoint2010ConceptsItem> Concept = context.GetList<SharePoint2010ConceptsItem>("SharePoint 2010 Concepts"); var query = from cs in Concept where cs.Status.Title.ToString()!="Completed" select new { cs.Title, cs.Id, //cs.Status }; sgvConcepts.DataSource =query; sgvConcepts.DataBind();   Any help me plz how to retrieve the data from a lockup column.bvnprasad

execute sql task vs data flow task

  
My manager has asked me to use linked server to connect to ORACLE database rather then oledb   my source db is ORACLE and my Distination is SQL server 2005 ( in 64 bit  windows server) I am planning to write this query in an execute SQL task, INSERT  dbo.MV_MAX (PERIOD                                 ,PRODUCTGROUPID                                 ,PRODUCTID                                 ,SUBPRODUCTID                                 ,HLBER_ID                                 ,BE_ID                                 ,ENTITYID                             &n

Error in Script component in the Data Flow Task

  
Hi, I need a small help. This is what i require. I need to generate a auto increment number using script component and assign it to target column. use db1 go create table src (name varchar(10) ) insert into src select 'A' union all select 'B' union all select 'C'   create table trg (id int, name varchar(10) ) while loading the trg table, i need to generate a auto increment number and then load it to target. Finally, my trg table data should look as follows id name 1  A 2  B 3  C This is what i have done inside the package. step1 : declared a global variable "tmp" of int32 step2 : created a connection manager pointing to "db1" database step3 : drag n drop data flow task step : inside the data flow task , i have the following        OLEDB Source  (select name from src)           |        Script Component (i have added a new Output column "v_id" of int datatype in INPUTS AND OUTPUTS page)           |        OLEDB Destination In the Script page in Script component , in ReadWriteVariables | User::tmp, I have chose Visual Basic 2008 and clicked the EDit script and inside the Editor this and inisde the below routine i have added only two

Paste data in a lookup column

  
Hi all, I am trying to create a Sharepoint list from a previous Excel file and I would like to use a lookup column, to allow users to pick values of “column B” from “column A”. The values are already entered in "column A", so now I would like to edit the list in datasheet view and copy/paste the values from my excel file into my “column B”. It works well when I copy/paste only one data, but when I try to paste several values in the same row (using a semicolon as delimiter), I get the error message “Cannot paste the copied data due to data types mismatches or invalid data.”, although the column is set to allow multiple values. I noticed that lookup columns allowing mulitple values store items with the item ID, as follows: ITEM NAME;#ITEM ID, but don’t display it in datasheet view or standard view, and I guess this is why I can’t copy and paste the data from my excel file since they don’t have the ID encoded, but is it possible to do it anyway? Thanks for your help.
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