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

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

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

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


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?


Craig Bryden - Please mark correct answers

View Complete Post

More Related Resource Links

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!

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

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.

How To Map A GUID Variable And Use It In An OLE DB Source Data Flow Select Query


I have a simple SSIS package that does the following. 

On the control flow tab, there is an Execute SQL task that calls a stored procedure and returns a GUID. 

I have a package scoped variable that stores a UNIQUEIDENTIFIER output from the stored procedure. It appears that the package variable data type does not include a GUID data type so I tried to use string instead.

Next, the Execute SQL task is mapped to a data flow task. 
Within that data flow task I have an OLE DB Source which connects to a SQL Server Destination task.

On the OLE DB Source Editor screen I have a query that performs the following:

SELECT ID, SessionKey, ....
FROM dba_DroppedLoginsLog
WHERE SessionKey = ?

I have clicked on the parameters button to map the Parameter to my variable called User::SessionKey. This is the output variable from my Execute SQL Task component on the Control Flow tab. This value should be a GUID, but again, I don't see a data type for that.

When I execute the package it fails on the OLE DB Source with the following error:
[OLE DB Source [1908]] Error: SSIS Error Code DTS_E_OLEDBERROR.  An OLE D

data Flow Script Component

Hola, estoy en un data Flow y en el Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer) quiero validar si la columna de tipo fecha es realmente es fecha, en el caso contrario debo interrumpir la ejecución del ETL, pero en estos Script de transformación no he podido indicar un Dts.TaskResult = ScriptResults.Failure ... como lo puedo hacer? Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer) If IsDate(Row.FECHAINGRESOVENTA) Then Row.FECHAINGRESOVENTA = parseFecha(Row.FECHAINGRESOVENTA) Else Dts.TaskResult = ScriptResults.Failure End If gracias

Data flow - Repeat of lookups in insert and update path


Hi forum

I find myself repeating lookups when I have insert and update paths. In my data flows I use a Lookup component to check if the row already exists in the destination table. I insert the row in the No Match output path and update in the Match path.

I often have to do the same for the insert and update paths. I always have to do them in the insert and might have to do them for the updates. In the update path I use conditional splits to check if values have changed - if they have - then I lookup for example a supplier data warehouse key. I could place it before the lookup that check if the row exists but it would be a waste - because often the supplier did not change - some other value did.

So I end up with 2 copies of my lookups I have to maintain :(

Hope someone can help me out.


Data flow - Conditional lookup possible?


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.


SSIS data flow task not loading all rows from sybase server



Using a data flow task I am trying to load data from a table in sybase server to sql server. There are approx 10000 rows in the sybase table, however for some reason which I am not able to find out, at random certain no of rows are getting inserted. e.g. in one run 800 rows then 200 rows etc.

Can somebody please guide me on this?

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

Xml Ssis Data Flow Component?


There is a table with a column that contains Xml documents. For each record from my Data Flow Source, I want to pass in the Xml document and the node to interrogate, and return the value contained in the node.  Like the Crm component, this is probably one I will have to write from scratch in C#, but I would like to avoid having to create the custom component if it already exists in the public arena. 


Does anyone know of any Xml Ssis Data Flow Components that are downloadable for free?

Data Flow Source.... Expressions/Variable

I am using the SSIS data source reader to connect to a MySQL database.

Am I able to use expressions with this Data Flow Source. I need to pass a variable to the SQL code?

I am using SQL 2005.

Mr Shaw

MS SQL Server: Search All Tables, Columns & Rows For Data or Keyword Query

If you need to search your entire database for specific data, this query will come in handy.

So when a client needs a custom report or some sort of custom development using Great Plains, most of the time I will have to track down the data in the system by running this query and find the table(s) it is in.

How to format and update GridView and DataGrid rows using JQuery

The behavior described in this question is as expected. When you set text of a cell in grid, it directly affects HTML that is going to be rendered. When you set text value of a cell, it means that you are setting innerText of the cell. The column that GridView creates for command fields (Edit, Delete and Select) are a (anchor) or button elements. So you can see what will happen if you set text value in that cell. It will wipe out those link or button controls and replace them with simple text string.

>>>How to update multiple rows of gidview by one click



I want to add multiple rows of a gridview if the data for multiple fields are same.

Idea for this is that a user may search a record with some parameter e.g. Sector, plot.

For example 15 records being displayed in gridview.

There should be checkbox before every row and if user wants to update Plot and Flat No that is same for 10 rows out of 15 so he will check the rows and update.

For this a dialog box should appear asking for the parameter i.e. Plot and Flat or something else next time may be it will be City or country. Moreover, there is already an Edit button to update records, when user click it another page open for the record to be updated and whatever he wants to update, it updates.

Hope everone understand the scenario.

I don't want to disturb the already provided Edit functionality and want to provide same record to be updated.

Any idea in this regard would be appreciated.

How to retrieve data from SqlDataSource and pass to a variable?


Hi there,

I have a page in my website where there is very little code-behind. I only have two lines of code in Page_Load() and the rest of the code is declarative ASP.NET code. I have a FormView control which uses a SqlDataSource to get some order information. The SqlDataSource calls a stored procedure called usp_GetOrderDetails, here's the code for the SqlDataSource:

    <asp:SqlDataSource ID="sdsFormOrderDetails" runat="server" 
ConnectionString="<%$ ConnectionStrings:ForexDB %>"
ProviderName="<%$ ConnectionStrings:ForexDB.ProviderName %>"
SelectCommand="usp_GetOrderDetails" SelectCommandType="StoredProcedure"
UpdateCommand="usp_UpdateOrder" UpdateCommandType="StoredProcedure"
<asp:ControlParameter Name="orderId" ControlID="grdOrders" PropertyName="SelectedDataKey.Value" />
<asp:ControlParameter Name="orderId" ControlID="frmOrderDetails" PropertyName="SelectedValue" />

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

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