Lookup Task Based On A Variable
Jul 17, 2006This doesn't seem possible but I'll ask anyway...
Can I build a lookup task where the lookup query is based on a variable, rather than hardcoding the SQL staement?
ADVERTISEMENT
Is It Possible To Lookup Value Based On Two Tables Using Lookup Task
Jun 27, 2007Hi All,
Actually this is in regard to SCD Type 2 Dimension, Scenario is like that I am moving Fact table from some old source and I have dimensionA description value in fact which I want to replace with appropriate id from Dimension Table and that Dimension table is SCD Type 2 based on StartDate and EndDate and Fact Table doesn't contains direct date value rather there is timeId in Fact so to update the value in Fact table I have to Join Time Dimension table and other Dimension Table to replace fact Description with proper Id.
Lets assume DimensionA Structure
id
Description
StartDate
EndDate
Fact Table
id
measure1
measure2
TimeId
Description
Time Dimension
TimeId
Date
Day
Hour ...
Script Task - Set Variable Value Based On Success Or Failure
Feb 14, 2008
Please excuse my ignorance as I'm a complete noob when it comes to vb.net.
I have 2 script tasks, each connected to an upstream task via Success and Failure constraints. Each script assigns a value to a variable, depending on whether the task succeeds or fails.
My code thus far is:
Code Snippet
Public Sub Main()
Dts.Variables("strEmailBody").Value = _
"Business Model Reporintg Control Complete - Status = Success"
Dts.TaskResult = Dts.Results.Success
End Sub
What i want to do is use a single script task depending on the success or failure of the package, setting the variable value accordingly.
If there are no errors Then
"Success"
Else
"Failure"
I've tried
Code SnippetIF CBool(Dts.Results.Success) Then...
But whislt it compiled, didn't evaluate correctly during runtime.
Can anyone suggest where I'm going wrong? Again I'm totally new to .net and I'm surprised I've gotten this far!
Thanks in advance.
Leigh
Lookup Transformation Based On A Csv File
May 21, 2007My lookup data is in a csv file, not a table. Is there a way to get the Lookup transformation to use the csv file as the source 'table'? Obviously the alternative is to load the file into a SQL Server table and use that, but I want to keep it simple if possible.
SSIS Script Task Alters Package Variable, But Variable Does Not Change.
Oct 25, 2006I'm working on an SSIS package that uses a vb.net script to grab some XML from a webservice (I'd explain why I'm not using a web service task here, but I'd just get angry), and I wish to then assign the XML string to a package variable which then gets sent along to a DataFlow Task that contains an XML Source that points at said variable. when I copy the XML string into the variable value in the script, if do a quickwatch on the variable (as in Dts.Variable("MyXML").value) it looks as though the new value has been copied to the variable, but when I step out of that task and look at the package explorer the variable is its original value.
I think the problem is that the dataflow XML source has a lock on the variable and so the script task isn't affecting it. Does anyone have any experience with this kind of problem, or know a workaround?
Passing A SSIS Global Variable To A Declared Variable In A Query In SQL Task
Mar 6, 2008I have a SQL Task that updates running totals on a record inserted using a Data Flow Task. The package runs without error, but the actual row does not calculate the running totals. I suspect that the inserted record is not committed until the package completes and the SQL Task is seeing the previous record as the current. Here is the code in the SQL Task:
DECLARE @DV INT;
SET @DV = (SELECT MAX(DateValue) FROM tblTG);
DECLARE @PV INT;
SET @PV = @DV - 1;
I've not been successful in passing a SSIS global variable to a declared parameter, but is it possible to do this:
DECLARE @DV INT;
SET @DV = ?;
DECLARE @PV INT;
SET @PV = @DV - 1;
I have almost 50 references to these parameters in the query so a substitution would be helpful.
Dan
Lookup Based On Results Of Previous Lookups
Feb 17, 2008i'd like to use ssis on a certain project but am concerned that one of my transformations needs lookup results to be based on actions taken on previous lookups and that the toolkit doesnt really offer something like that.
so, i have a dataflow whose first component extracts certain kinds of data from an xml document.
each row returned by the latter needs a lookup but the results of that lookup may dictate a certain kind of update. The next row's lookup may need to be influenced by the previous row's update.
So I think I have two challenges, 1) combining a lookup and update, 2) making sure the buffer architecture completes one lookup and update before the next lookup begins.
Best Practice - Lookup Or SQL From Variable?
Feb 2, 2006Hi,
I am pulling data from FoxPro tables into SQL 2005, and want to only pull new or changed rows. Accordingly each table in Fox has a column LastChangedDateTime, indicating the last time the row was updated, and I have a table in SQL which has one row per Fox table, listing the table name and the most recent data pulled into SQL.
In 2000 DTS I would have pulled the SQL datetime value into a package variable, then used a parameterized SQL statement with ".. WHERE LastChangedDateTime > ? " to select the rows I require.
In SSIS this approach does not seem to be possible, and the options are that I either use a variable for the entire SQL statement or, as the first SSIS tutorial suggests, use a lookup against the SQL table.
Gut feel is that the lookup will perform slower than creating the variable SQL and executing that (given that the source table is 13 million rows and rising, and I only want the last 100,000 or so from today).
What is considered best practice under these circumstances?
Also is it possible to write SSIS scripts in C# rather than VB.NET, as the syntax differences are driving me mad? ;-)
Thanks in advance,
Richard R
Lookup Task
Feb 22, 2008Hi,
I've looked at so many examples on the web but I can't get my dataflow with lookup to work correctly. It just keeps inserting the records instead of updating them because they already exist. I know it's something simple but I've been trying to figure it out for the last 4 hours and am over my head. In the lookup I have 5 columns as available input and of course the matching lookup columns and selected 4 with lookup operation replace and tried add as new. It just keeps adding them to my destination table when they already exists. I can't figure out what i am doing wrong?
thanks,
Using Lookup Task
Jun 6, 2006Is there a way to set the Lookup task to be not case sensitive? For example, the lookup table has id 1, value 'ABCD'. The value that I'm using to lookup is 'abcd'. I cannot make it to return the id 1 unless I convert my lookup value to be all upper case.
And I don't want to use Fuzzy Lookup.
Thanks,
Ash
Lookup Transform With Variable Parameter
Jun 8, 2006Is it possible to use a VARIABLE in the Lookup Transform? I am setting the cache mode to partial and have modified the caching SQL statement on the advanced tab to include the parameterized query, but the parameter button only allows me to select columns to map to the parameter. I need to use a variable instead. I see the ParameterMap property of the transform in the advanced editor, but don't see how I can use this to map to a variable.
Can this be done, or do I need to use a new source, sort and left join component to accomplish the same thing?
Thanks!
Brandon
Lookup A Variable From Stored Proc
Oct 31, 2007
Hi all,
I haven't been able to get a variable to get its value from a query using other variables as paramters. Is this possible?
Here's my situation:
I have a table workflow
(
id int PK,
Quarter int UK1,
Responsible varchar UK1,
Stage varchar UK1
)
The workflowId is a composite key of the other three columns to keep the facttable rows narrow.
And a stored proc GetWorkflowId that looks if a certain combination of quarter, responsible and Stage exists. If so, it returns the id, if not, it inserts the row and returns the Id. So i can;t use a lookup or merge join, becuase the workflow row may not exist yet.
Now i need this workflowId as a variable in my package. (First a sql task uses it to delete old values, then a dataflow task would use it as a derived column to insert the new values.
Quarter is a variable in my package, and i need to lookup/ create a workflowid with the stored proc using Quarter, and then get the return value into a variable WorkflowId. Can i do that?
Hope i've been clear, if not let me know.
Thanks in advance,
Gert-Jan
SQL Server 2012 :: Get ID From Lookup Table Based On Value Passed To Function
Dec 19, 2014I have a requirement regarding a color combination data. I have a lookup table that holds a colorid, p1, p2, p3, p4 to p8 which will be having colors Red, Green and Amber. P1 to P8 columns holds these three colors based on their combinations.
I have attached the look up table data for reference.I need to pass the color values to p1 to p8 and need to retrieve the color id based on the passed color. If we pass values for all p1 to p8 then it is easy to get the color code, however it will not happen. The passed values may be dynamic. ie we will not have all 8 values all the times. sometimes we will have 2 colors passed, sometimes 5 colors will be passed.
If i pass only two colors say red and red, i need the color id of only the row that has red and red for p1 and p2 alone. i dont want want all the colorid's that has red and red in p1 and p2 and some other colors in p3 to p4.
The exact colorid of the combination must be returned on passing the values to p1 and p2.I am passing Red and Red as values to P1 and P2. In the look up table we can have 10 rows that has red and red i p1 and p2 like
colorid p1p2p3p4p5p6p7p8
1 redred
10 redredred
20 redredred
30 redredredred
40 redredredredred
50 redredredredredred
60 redredredredredredred
70 redredredredredredredred
So the result must have only the colorid 1 and not all the colorid's listed above. when I pass 3 red as values for p1, p2, p3 then the result must be 10. Colorid 1, 20, 30, 40, 50, 60 and 70 must not come in the result.I need a function or procedure that will accept the arguments and provide me the result based on the values.
Lookup Task With NOLOCK
Mar 2, 2006I have an SSIS package that unpivots data - each row from the source becomes 20-24 rows in the destination db. In the dataflow it looks up some type-data IDs before inserting into the destination table. The whole process flies through the data at an incredible rate, however I find that the final commit on data insertion takes a very long time, and two of the lookup tasks remain yellow throughout. It appears that there may be a contention issue because the final table has FKs to those lookup tables.
In T-SQL it is possible to do SELECT..... WITH (NOLOCK), but when I modified the SQL statement of the lookup task by adding this clause, it caused an error "Incorrect syntax near the keywork 'with'."
Is there any way around this problem? THNX!
PhilSky
Lookup Task Not Performing Correctly!
Apr 8, 2008Hi, I'm using the lookup task to perform updates on some records and it seems to be working correctly but isn't making all updates. I have a case where there are 543 rows in my source connection, there are 436 of them that do not match the date column of the 543 in my destination table. It only updated 224 of them when it should have updated all 436? Does anyone know why it would only update this many instead of all? I also had another that only updated 18 out of 19 instead of all 19.
thanks,
Problems With Lookup Task And Dates
Aug 2, 2005I€™m trying to make a Lookup transformation with a relation on two string columns and a between dates condition set by parameter:
View 7 Replies View RelatedHandling Errors When Using A Lookup Task
Jul 13, 2006Hi
I am trying to use this painful new SSIS process. I basically need to use a lookup task to check to see whether a record exists or not. If not, then I need to insert the record. However, because this is treated as an error situation (which is stupid in itself), I get a problem when the number of records not found reach the MaximumErrorCount, and the rest of the package fails. Is there any other method of doing this type of thing, without simply increasing the MaximumErrorCounty to some ludicrous value. I could do this type of thing very very very easily when using DTS packages using the Data Driven Task, it seems so stupid that I can't perform the same kind of task using SSIS.
Any help would be appreciated
Thanks
Darrell
Lookup Task Datatype Issue
Jul 13, 2006Hello:
I'm attempting to use the lookup task and i'm running into an issue about incompatible datatypes.
The table that i'm referencing in the lookup has a field named DateCreated, and it's DataType is DateTime.
But when I hook it up to the Lookup Task, the lookup task thinks it's a DB_Timestamp, so i get an incompatible data type error.
What can i do?
Case Sensitive In Lookup Task
Jan 25, 2008
Hi,
My SQL Server database is case in-sensitive.
I have a lookup task to join on varhar column. If the data is only case different such as 'DataModel' and 'Datamodle', lookup task consdiers them as different. Is there a way to set up lookup task as case insentive? I know I can work around it by converted them to upper case and compare them.
Use Partial Or No_Cache Mode In Lookup Task
Jun 21, 2006Hello,
Can anyone explain to me (or show me where to get info) in what scenario should I use Full Cache, Partial, or No_Cache mode in the Lookup task? I got below warning message during execute my package. I understand that my lookup data have duplicate reference keys. I want to change the mode to partial or no cache to see how it works, but don't know what to change for the parameter map.
Warning: 0x802090E4 at Data Flow Task, Lookup [37]: The Lookup transformation encountered duplicate reference key values when caching reference data. 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.
Any info is appreciated.
Ash
SQL 2012 :: Lookup Task In SSIS - Using A View Rather Than A Table?
Aug 6, 2014We have a LKP table that we will use to decode incoming codes from multiple sales feeds to link to the relevant surrogate key for the ultimate dimension table.
The LKP table has a start date, end date and active row flag to track history of the codes.
This table in its entirety is required for the FULL history load but we will need a CURRENT version of the table containing only the ACTIVE rows for the DAILY incremental loading.
I am thinking of putting a standard view over the top of the LKP table to present only the ACTIVE rows rather than creating a 2nd table and reloading it each night. We don't have enterprise edition to use materialised views.
My question is, will the LKP cache populate as the SSIS packages begins, loading it with all the rows from the view, as it would if I used the 2nd physical table with only ACTIVE rows?
SSIS Lookup Task - Best Practice Concerning Cachetype Property
Oct 26, 2006SSIS data flow transformation - Lookup task - best practice concerning Cachetype
I would like to know if there's any best practice concerning the CacheType property for the the Lookup task. Default value is "Full", but if the SSIS package is working with at lot of data, i.e. +10 mill. records from the OLE DB source to be handled through a variated numbers of data flow transformation tasks, it must have an impact memory usage if the lookup table also is a large table, i.e. +8 mill. records? When should I consider turning the property value to "none"
Dataflow Error In Lookup Task : Object Was Open..
Apr 25, 2006
I Can't reproduce the error if I run the package stand-alone.
I'm using the same lookup call (same table, etc.) in 2 packages that are running in parallel (called by a parent package).
[LKP_UnderwriterId [72283]] Error: An OLE DB error has occurred. Error code: 0x80040E05. An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80040E05 Description: "Object was open.".
Anyone seen this one?
Fuzzy Lookup Task - Expressions For MatchIndexName And ReferenceTableName
Jun 19, 2007Why is there no Expressions collection for the Fuzzy Lookup task? Is there another good way to dynamically configure the ReferenceTableName and MatchIndexName properties?
View 1 Replies View RelatedLookup Task Running Very Slow When Deal W/big Tables
Sep 18, 2006I try to convert a Procedure that join 8 tables with INNER AND OUTER JOIN, my understanding is that the Lookup task is the one to use and I should break these joins into smaller block, it takes a long time to load when I do this, since each of these tables had 10-40mill. rows and I have 8 tables to go thru, currently this Stored Procedure took 3-4min to run, by converting this to 8 Lookup tasks, it ran for 20min. has anyone run into this issue before and know a work around for this.
Thanks
Integration Services :: Possible To Parameter Connection Of Lookup Transformation Task
Aug 14, 2015Is it possible to parameter the connection of a Lookup Transformation task - specifically the table/view name? I would like to be able to dynamically set the table that the Lookup Transformation is connecting to at runtime.I've looked into the "Use results of an SQL query" on the connection screen (which correlates to the "SqlCommand" property), but I'm unable to pass in a parameter this way.I've also looked into the SqlCommandParam, but that doesn't allow me to use a parameter in the "FROM" clause of the sql syntax.
View 4 Replies View RelatedVariable Inside A Variable From Sql TAsk
Sep 28, 2006I've got two Sql Tasks on my dtsx. The first one loads a value into "Proyecto" user variable and the second one executes a variable named "SegundoProceso" which contains from the beginning:
"select Fecha from LogsCargaExcel where Proyecto = " + @[User::Proyecto] +""
As SqlSourceType propety I have "Variable" and inside ResultSet or Parameter Mapping nodes there is nothing.
[Execute SQL Task] Error: Executing the query ""select Fecha from LogsCargaExcel where Proyecto = " + @[User::Proyecto] +""" failed with the following error: "Cannot use empty object or column names. Use a single space if necessary.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Where am I wrong?
TIA
SQL Variable And IS Variable In Execute SQL Task
Jan 3, 2007Hi,
I have an Execute SQL Task (OLE DB Connnection Manager) with a SQL script in it. In this script I use several SQL variables (@my_variable). I would like to assign an IS variable ([User::My_Variable]) to one of my SQL variables on this script. Example:
DECLARE @my_variable int
, <several_others>
SET @my_variable = ?
<do_some_stuff>
Of course, I also set up the parameter mapping.
However, it seems this is not possible. Assigning a variable using a ? only seems to work in simple T-SQL statements.
I have several reasons for wanting to do this:
- the script uses several variables, several times. Not all SQL variables are assigned via IS variables.
- For reading and mainenance purposes, I prefer to pass the variable only once. Otherwise every time the script changes u need to keep track of all questionmarks and their order.
- Passing the variable once also makes it easier to design the script outside IS using Management Studio.
- This script only does preparation for the actual ETL, so I prefer to keep it in one task instead of taking it apart to several consecutive Execute SQL Tasks.
- I prefer to use the OLE DB connection manager because it's a de facto standard here.
Could anyone help me out with the following questions:
- Is the above possible?
- If so, how?
- If not, why not?
- If not, what would be the best way around this problem?
Thanx in advance,
Pipo
Is There Away To Reference Global Variables In A Lookup Transformation That Are Set Outside A Data Task Flow?
Mar 31, 2008
The logic I am trying to recreate via SSIS is the following SQL statement:
insert into db3.dbo.targettable1 -- Target database table
(SiteC,
Objecte,
Attrib1)
select distinct ?,
?,
from ? -- Source database table
join dbo.targettable2 c1 -- Target database table
on c1.Alias = ? and
c1.CSetID = ? and
c1.FacID = (select f.PFacID
from dbo.Fac f
where f.FacID = ?)
where not exists (select * from dbo.targettable2 c -- Target database table
where c.Alias = ? and
c.FacID = ? and
c.CSetID = ?)
I have an OLE DB Source that consists of an expression to approximate the following portion of the Above Select statement:
Select ?,
from ? -- Source database table and
The package has 2 global variables User:CSetID and User::FacID whose scope is global to the package and whose values are set within a Foreach Loop Container outside of the Data Flow Task
I was trying to reference the 2 global variables within the Looup Transformation to recreate the following portion of the SQL statement.but encounter errors:
join dbo.targettable2 c1 -- Target database table
on c1.Alias = ? and
c1.CSetID = ? and
c1.FacID = (select f.PFacID
from dbo.Fac f
where f.FacID = ?)
In the Advanced Editor window of Lookup Transaction
select * from
(select * from [dbo].[targettable2 ]) as refTable
where [refTable].[Alias] = ? and [refTable].[FacID] = ? and
[refTable].[CSetID] = ?
Is there away to reference global variables in a Lookup Transformation that are set outside a Data Task Flow?
Lookup Task Data Flow Transformation Causes Data Flow Task To Hang?
Dec 28, 2007Hi,
I'm trying to implement an incremental data pull (Oracle to SQL) based on Andy's blog:
http://sqlblog.com/blogs/andy_leonard/archive/2007/07/09/ssis-design-pattern-incremental-loads.aspx
My development machine is decent: 1.86 GHz, Intel core 2 CPU, 3 GB of RAM.
However it seems the data flow task gets hung whenever I test the package against the ~6 million row source, as can be seen from these screenshots. I have no memory limitations on the lookup transformation. After the rows have been cached nothing happens. Memory for the dtsdebug process hovers around 1.8 GB and it uses 1-6 percent of CPU resources continuously. I am not using fast load to insert new records into my sql target table. (I am right clicking Sequence Container 3 and executing this container NOT the entire package in the screenshots)
http://i248.photobucket.com/albums/gg168/boston_sql92/1.jpg
http://i248.photobucket.com/albums/gg168/boston_sql92/2.jpg
http://i248.photobucket.com/albums/gg168/boston_sql92/3.jpg
http://i248.photobucket.com/albums/gg168/boston_sql92/4.jpg
http://i248.photobucket.com/albums/gg168/boston_sql92/5.jpg
http://i248.photobucket.com/albums/gg168/boston_sql92/6.jpg
The same package works fine against a similar test table with 150k rows.
http://i248.photobucket.com/albums/gg168/boston_sql92/7.jpg
http://i248.photobucket.com/albums/gg168/boston_sql92/8.jpg
The weird thing is it only takes 24 minutes for a full refresh of the entire source table from Oracle to the SQL target table.
Any hints,advice would be appreciated.
How Do I Set A Variable Value Based On A SQL Query
Apr 25, 2007I have a SQL query that finds a value which I need to pass into an SSIS variable so I can use it later to set the filename for my data flow task. So far, I have been unsuccessful in determining how this is done.
google searches, etc seem to be fruitless for me, there are so many hits but none really seem to address what I want to do because there are so many things you can do with variables. Likewise the books online and the boks I have on SSIS also only give a very high level of info. I need some examples, if anyone could help I would appreciate it.
Here is the basic gist of my query:
exec SP_GET_FILE_NAME @file_name OUTPUT
I need to take the resulting @file_name and store it in an ssis variable. How?
Integration Services :: Perform Lookup On Large Dataset Based On A Small Dataset
Oct 1, 2015I have a small number of rows in a dataset, Table 1. There is a CLOB on a large dataset, Table 2. They join on a PK. I would like to retrieve this CLOB and add it to the data flow for Table1. In short I want to emulate the following:
Table 1:Â Small table without CLOB, 10 rows.Â
Table 2: Large table with CLOB, 10,000,000 rows
select CLOB
from table2
where pk = (select pk from table1)
I want this to return the CLOBs for the small number of rows in Table 1. The PK is indexed obviously so it should be a fast look up.
Table 1 and Table 2 live on different Oracle databases. How do I perform this operation efficiently in SSIS? It seems the Lookup and Merge Join wont do this.
Selecting Data Based On A Variable
Jun 7, 2007Hi All
I want to select a certainnumber of rows
select custno, amt, balance from customer where custno='customerno'
when showcust='r' then select rows where amt<balance
when showcust='c' then amt>balance etc
if showcust='' then show everything
Any insight will be greatly apprecaited.
Thanks