Lookup Functionality
Jul 30, 2007
Hi,
I have 3 tables that i gather a single field from each and put them into another table.. simple enough you might think
but when i start using a lookup funciton to check if i have already entered an item (this package could be run a number of times) some duplicates slip though, and as i continue to run the pakcage less and less appear, but never reaching 0
very strange..
doing select statements on the target table reveal strange behaviour.. after the first 'iteration' there are only unique entries in the target table (exactly what i want). After the 2nd, 3rd, 4th etc duplicates appear of upper and lower case
i can only assume that there is something wrong with my query,,, but running it in SQL Manager reveals ~40,000 rows no matter how many times i run it...
more magically appear when hte same statement is ran in SSIS!!
I have tried a view, and using test tables (rather the live ones i am working on) and the reuslt is the same...
any help would be muc happreciated
regards
Chris
View 7 Replies
ADVERTISEMENT
Oct 31, 2007
We did some "at scale" fuzzy lookup tests today and were rather disappointed with the performance. I'm wanting to know your experience so I can set my performance expectations appropriately.
We were doing a fuzzy lookup against a lookup table with 25 million rows. Each row has 11 columns used in the fuzzy lookup, each between 10-100 chars. We set CopyReferenceTable=0 and MatchIndexOptions=GenerateAndPersistNewIndex and WarmCaches=true. It took about 60 minutes to build that index table, during which, dtexec got up to 4.5GB memory usage. (Is there a way to tell what % of the index table got cached in memory? Memory kept rising as each "Finished building X% of fuzzy index" progress event scrolled by all the way up to 100% progress when it peaked at 4.5GB.) The MaxMemoryUsage setting we left blank so it would use as much as possible on this 64-bit box with 16GB of memory (but only about 4GB was available for SSIS).
After it got done building the index table, it started flowing data through the pipeline. We saw the first buffer of ~9,000 rows get passed from the source to the fuzzy lookup transform. Six hours later it had not finished doing the fuzzy lookup on that first buffer!!! Running profiler showed us it was firing off lots of singelton SQL queries doing lookups as expected. So it was making progress, just very, very slowly.
We had set MinSimilarity=0.45 and Exhaustive=False. Those seemed to be reasonable settings for smaller datasets.
Does that performance seem inline with expectations? Any thoughts to improve performance?
View 4 Replies
View Related
Sep 26, 2007
I'm working with an existing package that uses the fuzzy lookup transform. The package is currently working; however, I need to add some columns to the lookup columns from the reference table that is being used.
It seems that I am hitting a memory threshold of some sort, as when I add 3 or 4 columns, the package works, but when I add 5 columns, the fuzzy lookup transform fails pre-execute:
Pre-Execute
Taking a snapshot of the reference table
Taking a snapshot of the reference table
Building Fuzzy Match Index
component "Fuzzy Lookup Existing Member" (8351) failed the pre-execute phase and returned error code 0x8007007A.
These errors occur regardless of what columns I am attempting to add to the lookup list.
I have tried setting the MaxMemoryUsage custom property of the transform to 0, and to explicit values that should be much more than enough to hold the fuzzy match index (the reference table is only about 3000 rows, and the entire table is stored in less than 2MB of disk space.
Any ideas on what else could be causing this?
View 4 Replies
View Related
Sep 23, 2015
Say I want to lookup a value in another dataset, but there is a grouping that requires you to know what the values for each level is in order to get to the correct detail record. Can you still use the lookup function with more than one field to compare against? So for example
Department
\___SalesPerson
\___Measure
I want to be able to add a new row at the Measure level, but lookup each field from another dataset. In order to do that I will need the Department AND SalesPerson values to do the lookup, but I dont think the Lookup function will let us do that will.
View 2 Replies
View Related
Jun 27, 2007
Hi 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 ...
View 1 Replies
View Related
Jul 24, 2007
I am doing a lookup that requires mapping 2 columns in the column mapping section. When I do this, I get the error "Row yielded no match during lookup" . The SQL that I captured in SQL profiler does find the record when I run it in Management Studio. I have already tried trimming everything to no avail.
Why is this happening?
I tried enabling memory restrictions but then I my package hangs and I get a SQLDUMPER_ERRORLOG.log file with the following logged:
07/24/07 13:35:48, ERROR , SQLDUMPER_UNKNOWN_APP.EXE, AdjustTokenPrivileges () failed (00000514)
07/24/07 13:35:48, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Input parameters: 4 supplied
07/24/07 13:35:48, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ProcessID = 5952
07/24/07 13:35:48, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ThreadId = 0
07/24/07 13:35:48, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Flags = 0x0
07/24/07 13:35:48, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, MiniDumpFlags = 0x0
07/24/07 13:35:48, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, SqlInfoPtr = 0x0100C5D0
07/24/07 13:35:48, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, DumpDir = <NULL>
07/24/07 13:35:48, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ExceptionRecordPtr = 0x00000000
07/24/07 13:35:48, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ContextPtr = 0x00000000
07/24/07 13:35:48, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ExtraFile = <NULL>
07/24/07 13:35:48, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, InstanceName = <NULL>
07/24/07 13:35:48, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ServiceName = <NULL>
07/24/07 13:35:48, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Callback type 11 not used
07/24/07 13:35:48, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Callback type 15 not used
07/24/07 13:35:49, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Callback type 7 not used
07/24/07 13:35:49, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, MiniDump completed: C:Program FilesMicrosoft SQL Server90SharedErrorDumpsSQLDmpr0033.mdmp
07/24/07 13:35:49, ACTION, DtsDebugHost.exe, Watson Invoke: No
Why am I getting this error with "Enable Memory Restriction"?
View 12 Replies
View Related
Sep 29, 2006
Hi all,
I don't understand what's happening here.
I have a Conditional Split with 3 outputs. On the first output I have a lookup, when I execute the package I have 56 rows going through the Conditional Split, all rows are then going to the 2nd and 3rd output but the lookup on the first output generates an error "Row yielded no match during lookup".
I don't understand why the lookup is generating an error while there is no row going through it.
Any idea ?
Sébastien.
View 6 Replies
View Related
Oct 4, 2007
I am designing a ssis package,This is intends to mine text data(Data extracted from websites).
Term lookup/Term extraction has been used as tools for mining.
I have lookup terms defined with me for reference table,but the main problem lie in extracting the nearby text/number/charcters to these lookup terms during mining.
For example :
I found noun "Email" 200 (frequency score) times in my text,Now I want to extract nearby email address(this is also true for PhoneNumber,Address attributes also).so how can I achieve this with SSIS.
If u have some idea/suggestion to carry out this challenge with or without Term Extraction/Term Lookup,plz do write here.
View 1 Replies
View Related
Apr 15, 2004
Does anyone know what is the Equivalent of Top in Oracle.
SELECT TOP 2 from TableNAME --> SQL SERVER.
What is the equivalent of this in Oracle
View 2 Replies
View Related
Nov 5, 2006
nevermind. please ignore this. theres too much extra data that id have to provide, but it would confuse the question so much that it couldnt be answered properly.
View 4 Replies
View Related
Nov 28, 2007
Hi All,
We are evaluating DB mirroring for our production box. But we have some queries with the operating mode available for mirroring.
1) I set the database miroring operation mode to full safety mode which is with synchronous. And Then executed a command to insert data in bulk in principal, the changes were commited to principal even when they are still getting mirrored to mirror server.
But as per the mirroring documentation from microsoft says this
"If transaction safety (or just 'safety') is set to FULL, the principal and mirror servers operate in a synchronous transfer mode. As the principal server hardens its principal database log records to disk, it also sends them to the mirror. The principal then waits for a response from the mirror server. The mirror responds when it has hardened those same log records to the mirror's log disk."
Is this a bug or I am doing anything wrong.
2) Mirroring documentation also says that when the transaction is on full safety mode, and if mirror and witness goes down then principal goes to read only mode. But again I saw deviation from the documentatin because when I turned mirror and witness off, principal went to "In recovery" mode.
Please help incase anybdy have faced this before or can help me if I am interpreting it wrong.
Thanks
Sachin
View 4 Replies
View Related
Jun 2, 2008
HI,
I am working on a Family tree portal which need tree functionality to display family members in tree structure. on click on any node the adding option should be displayed
for this i need a table and procedure to complete family tree
Thanks
@mbi
View 5 Replies
View Related
Apr 28, 2008
Hi,
I am using LIKE operator for a query to get wild card searched data.
for example if i give AN, should get for %AN% only. But it is returning data for %NA% data too. I cross checked by giving simple data. Please help me on this.
Thanks in advance
View 6 Replies
View Related
May 12, 2004
Functionally, is there any difference between DATEPART for m, d, and yyyy, verses simply using MONTH, DAY, and YEAR functions respectively?
For all intents and purposes, they seem identical, so is there any performance considerations using one verses another?
View 1 Replies
View Related
Jun 13, 2007
I have a table in one database (source) and an equivalent table in another (destination) and I need to move from my source to my destination database.
When I move the data I need to do the equivalent of an updategram, so for instance compare primary keys and if it exists in both do an update, if it is in source but not destination do an insert, if it is in destination but not source do a delete.
Is there already a process for doing this, as it seems pretty fundamental but I've not really found anything that gives the result I need?
If not, is there a way of doing this with what is available within Data Flows or should I just go ahead and go through the pain of creating my own data flow destination object?
TIA,
CD
View 3 Replies
View Related
Jul 11, 2007
I design a reporting services solution based on the standard Report Manager web site.
I configure IIS using basic authentication and passing the user credential to SSAS 2005 DB for securing data access.
Everything funtions well .... but now my client ask me for a new 'funcionality'.
They ask me to add a logoff button to change the current logged user.
Any suggestion how to implent that functionality with the minum development effort ???
Thanks
Cosimo
View 1 Replies
View Related
Jul 3, 2007
currently have my website that is functional...but would like to add some navigation to my photos which are displayed using a repeater tied to a SQL database. (live example can be viewed here)
the above link shows a gallery page that displays all the photos within a given shoot (using a querystring to display the unique ID of the photo shoot). When you click on one of the images within the shoot a page called is called displaying the selected photo by passing the shoot ID and the unique ID of the photo itself through a querystring.
I would like to add navigation to the page displaying the photo that would allow the user to go to the next photo, previous photo, first and last phot within the specific SHOOT ID.
FirstNextPreviousLast
I am using a repeater to display the photo with the following sqlDataSource:
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:Connection %>"SelectCommand="SELECT usr_Photos.*, usr_PhotoShoot.* FROM usr_Photos INNER JOIN usr_PhotoShoot ON usr_Photos.Shoot_ID = usr_PhotoShoot.Shoot_ID WHERE usr_Photos.Photo_ID = @ID AND usr_Photos.Shoot_ID = @Shoot"><SelectParameters><asp:QueryStringParameter DefaultValue="" Name="ID" QueryStringField="ID" /><asp:QueryStringParameter DefaultValue="" Name="Shoot" QueryStringField="Shoot" /></SelectParameters>
</asp:SqlDataSource>
I am having trouble figuring out a way to add the functionality i would need to change photos within the specific photo shoot without having to go back to the gallery page. any help or ideas you could point me in would be great...thanks in advance...
View 2 Replies
View Related
Jan 15, 2001
Hi,
Is is anyway to print out the result from OLAP cube?
Thanks in advance.
David
View 1 Replies
View Related
Nov 22, 2004
Hello, everyone:
In the BOL about Designing a Backup and Restore Strategy, it is mentioned there is a Base Functionality Script that should be run after SQL Server restored. What is Base Functionality Script? Just DBCC CHECKDB? Anything else?
Thanks
ZYT
View 1 Replies
View Related
Jul 20, 2005
Hi,I've used the minus functionality which is available in Oracle andi would like to use it in SQL server, but i don't know how to. Thefolllowing is how it works in OracleSelect symbols from symbol_tableminusselect tsymbols from tradeIt returns a list of all the symbols from symbol_table which are notpresent in trade.Similarly, the intersect will return only those which are common toboth.I was wondering if someone throw some light on this problem for me.Thanks in advance,Sumanth
View 1 Replies
View Related
Sep 19, 2007
I'm not seeing any built-in functionality for Report Builder to get Top N values. Am I missing something? Is there a way to do this? If not, is there a way to simulate this functionality?
Thanks.
PJJ
View 1 Replies
View Related
Mar 15, 2006
I am fairly new to MS SQL and an wondering if the following is possible;
Step 1 - person fills out a form on our website and submits it to our SQL DB via a stored procedure
Step 2 - the stored procedure inserts some of this information into a DB table and sends some of the information via the inbuild DBMail in an email.
Firstly, is this possible?
Sencondly, is there a way of encrypting this email before it is sent?
View 1 Replies
View Related
Jun 12, 2007
There is a view on our server that has fields for project name and then 25
months, each row contains the descriptive name and the forecasted budget in
each month. I need to create a chart showing the total budget for month and
a running total for the year to date. In Excel this is easy with a Pivot
Chart, how do I do this in SSRS 2005?
TIA
Dean
View 3 Replies
View Related
Nov 26, 2007
In one of my packages, I am trying to create functionality that is similar to the fuzzy grouping transformation in that it takes a set of records and returns a subset of unique records. However, rather than fuzzy-matching columns which creates significant overhead, I just want to compare columns exactly, similar to how the lookup transformation works. At this point, data has already been fuzzy unique/duplicate seperated, this step is to only keep one record for each exactly similar dupe for an alias table.
At the moment, I am doing this (with lack of scalability foresight) with a fuzzy grouping transformation with all columns set to exact except for one which is set to fuzzy with a minimum similarity of 0.99. While this worked during development when I was testing with a small subset of data, it is unreasonable for the ~5mil records of production data it needs to operate on.
I am sure there is an efficient way to do this. I have thought about using a lookup transformation or combinations thereof, but I have not come to a definite solution yet.
Does anyone have any experience doing something similar to what I am trying or have any suggestions/ideas/questions/etc? Any help will be greatly appreciated.
View 9 Replies
View Related
Dec 27, 2006
Hi,
I have a doubt regarding use of navigation controls in a report and exporting the report. For example using a drilldown functionality in a report. After the report is generated one can export the report in whatever format he wants (PDF,TIFF, XML,...).
In this case, with the drill down functionality in the report, the details of the report are visible only when the plus sign is clicked. Suppose now the plus sign is not clicked and one exports the report in a PDF or TIFF format.
In the PDF file, the navigation or the drill down functionality is now not useful right ? There is no way to view the detail fields of the report now, using the PDF file right?
Thanks in advance,
View 1 Replies
View Related
Aug 13, 2007
Hi All,
In my Report using SSRS 2005, I am displaying data using table and this table has table footer in which I am calculating "average" using Avg() function.The values in table rows can be either floating numbers (for eg; -1,-99,5.005) or "N/A". I use the following function to calculate Average
=Avg(Iif(Fields!Channel1.Value="N/A",CDbl("0"),CDbl(Fields!Channel1.Value)),"MainDS")
where "Fields!Channel1.Value" are values in table rows.
Everything works fine if all the table rows are floating point numbers and not "N/A", but as soon as table rows values are "N/A" I encounter "#Error" in the table footer.
Can anyone figure out how to get this code working. Thanks in advance.
Regards,
abhi_viking
View 9 Replies
View Related
May 18, 1999
see article: Article ID: Q216700 .
Within a trigger on SQL Server 6.5 Service Pack 5 or 5a, the IF UPDATE clause will evaluate to true for all columns when an INSERT is performed, even if there is no value specified and no default value exists for the column
http://support.microsoft.com/support/kb/articles/q216/7/00.asp
View 2 Replies
View Related
May 7, 2007
Hello,
I have the problems with UserSort functionality. I have attached the report to show what I mean. textbox3 located in the table group has the following user sort property value:
<UserSort>
<SortTarget>table1</SortTarget>
<SortExpression>=Fields!CompanyName.Value</SortExpression>
</UserSort>
I expect that the sorting will be performed within the entire table(data in both of groups will be sorted alphabeticaly by company name).
Instead of it, the sorting is performed independently for every group of table. What is the reason??
Here is the report
<?xml version="1.0" encoding="utf-8"?>
<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<DataSources>
<DataSource Name="DataSource1">
<ConnectionProperties>
<ConnectString>Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:wind.mdb</ConnectString>
<DataProvider>OLEDB</DataProvider>
</ConnectionProperties>
</DataSource>
</DataSources>
<BottomMargin>1in</BottomMargin>
<RightMargin>1in</RightMargin>
<InteractiveWidth>8.5in</InteractiveWidth>
<Body>
<ReportItems>
<Table Name="table1">
<Left>0.375in</Left>
<DataSetName>DataSet1</DataSetName>
<Top>0.125in</Top>
<TableGroups>
<TableGroup>
<Header>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ColSpan>3</ColSpan>
<ReportItems>
<Textbox Name="textbox3">
<ZIndex>1</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<BackgroundColor>MediumTurquoise</BackgroundColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<UserSort>
<SortTarget>table1</SortTarget>
<SortExpression>=Fields!CompanyName.Value</SortExpression>
</UserSort>
<Value>=Fields!Country.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.25in</Height>
</TableRow>
</TableRows>
</Header>
<Grouping Name="CountryGroup">
<GroupExpressions>
<GroupExpression>=Fields!Country.Value</GroupExpression>
</GroupExpressions>
</Grouping>
</TableGroup>
</TableGroups>
<Width>4.5in</Width>
<Details>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ColSpan>3</ColSpan>
<ReportItems>
<Textbox Name="CompanyName">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!CompanyName.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.25in</Height>
</TableRow>
</TableRows>
</Details>
<TableColumns>
<TableColumn>
<Width>3.15in</Width>
</TableColumn>
<TableColumn>
<Width>1.08in</Width>
</TableColumn>
<TableColumn>
<Width>0.27in</Width>
</TableColumn>
</TableColumns>
<Height>0.5in</Height>
</Table>
</ReportItems>
<Height>0.75in</Height>
</Body>
<LeftMargin>1in</LeftMargin>
<DataSets>
<DataSet Name="DataSet1">
<Query>
<CommandText>select * from customers where country='Mexico' or country='Austria'</CommandText>
<DataSourceName>DataSource1</DataSourceName>
</Query>
<Fields>
<Field Name="CustomerID">
<DataField>CustomerID</DataField>
</Field>
<Field Name="CompanyName">
<DataField>CompanyName</DataField>
</Field>
<Field Name="ContactName">
<DataField>ContactName</DataField>
</Field>
<Field Name="ContactTitle">
<DataField>ContactTitle</DataField>
</Field>
<Field Name="Address">
<DataField>Address</DataField>
</Field>
<Field Name="City">
<DataField>City</DataField>
</Field>
<Field Name="Region">
<DataField>Region</DataField>
</Field>
<Field Name="PostalCode">
<DataField>PostalCode</DataField>
</Field>
<Field Name="Country">
<DataField>Country</DataField>
</Field>
<Field Name="Phone">
<DataField>Phone</DataField>
</Field>
<Field Name="Fax">
<DataField>Fax</DataField>
</Field>
</Fields>
</DataSet>
</DataSets>
<Width>6.5in</Width>
<InteractiveHeight>11in</InteractiveHeight>
<Language>en-US</Language>
<TopMargin>1in</TopMargin>
</Report>
View 4 Replies
View Related
May 18, 2006
Is there a way to add additional functionality to the report parameter boxes?
For example, when a user enters an invalid choice into a report parameter box, I want a popup balloon that tells them to try again.
I know how to do this for a regular Winform combobox, but can I implement an interface to override the default behavior of the report parameter boxes?
View 5 Replies
View Related
Aug 10, 2007
Hi folks,
We had a minor data loss snafu (nothing major, and easily recoverable with our backup strategy), but I don't seem to be able to use the ol' DBCC LOG trick to get insight about when the deletion occurred. Obviously, since the log was backed up and truncated at least once since the deletion, there's nothing in the log file to analyze. I've tried restoring to a temporary database, but hit a couple of snags. First of all, running DBCC LOG on a database that's in standby mode simply shows the logged activity of the recovery process, and not any of the activity that was in the original transaction logs. Furthermore, running DBCC LOG against a database that's in recovery fails pretty ungracefully with a severity 20 error (I obviously didn't care to experiment with that approach any further).
Is there any way I can peek into the original transactions contained within a series of full and log backups to get a little who/when information? Or is this something that only Lumigent can do? I don't mind really ugly hacks here, since I'm simply doing read-only business with the backups. I'm not scared of a hex editor if it comes down to it.
View 3 Replies
View Related
Apr 14, 2006
As so rightly stated in http://www.microsoft.com/technet/prodtechnol/sql/70/books/inside6.mspx
"Currently, UDDTs don't support the notion of subtyping or inheritance,
nor do they allow a DEFAULT value or CHECK constraint to be declared as
part of the UDDT itself. These powerful object-oriented concepts will
likely make their way into future versions of SQL Server. These
limitations not withstanding, UDDT functionality is a dynamic and often
underused feature of SQL Server."
There's no point bothering with using a UDDT in T-SQL if you can't apply constraints to the UDDT!
1 vote for the introduction of the mentioned features into T-SQL
comments anyone?
View 6 Replies
View Related
Mar 18, 2008
I have a reporting server that needs to be utilized now but the person that set it up is no longer with the company.
We have no idea what login he used to install the server and nobody was granted admin rights to the server.
is there anything we can do to give someone admin access without having to reinstall (I don't think that is an option)??
View 6 Replies
View Related
