I used the lookup object to compare if the data from the working table (Dim_WCustomer) is existing in the Dim_Customer.
The reference column is Customer Account. However, the records with CustomerKey 1 to 5, which have special function when linked to fact table, have no customer account (NULL) that's why i want to filter that out from the lookup.
This is the select statement in my lookup: Select CustomerKey, CustomerAccount where CustomerKey > 5. When I previewed this, i don't see the 1-5 customerkey. However, when I ran the data flow, it gives me this error:
OnError,,,Add new records to Dim_Customer,,,25/02/2008 6:43:52 PM,25/02/2008 6:43:52 PM,-1071636471,0x,SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E2F.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E2F Description: "The statement has been terminated.".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E2F Description: "Violation of PRIMARY KEY constraint 'PK_Dim_Customer'. Cannot insert duplicate key in object 'dbo.Dim_Customer'.".
When I removed the 1-5 customers in the database, it worked well. It seems "where" clause in lookup doesn't function well....
I have a DTS package that calls a LOOKUP objects in an activeX script, I did not create this package and I am trying to figure out what it does, How do I view the actual LOOKUP object? I know what the LOOKUP object name is but cannot seem to find where it is actually stored?
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.".
I am trying to develop a custom algorithm. I have implemented and tested training methods, however I fail at prediction phase. When I try to run a prediction query against a model created with my algorithm I get:
Executing the query ... Obtained object of type: Microsoft.AnalysisServices.AdomdClient.AdomdDataReader COM error: COM error: DMPluginWrapper; Object reference not set to an instance of an object.. Execution complete
I know this is not very descriptive, but I have seen that algorith doesn't even executes my Predict(..) function (I can test this by logging to a text file) So the problem is this, when I run prediction query DMPluginWrapper gives exception -I think- even before calling my custom method.
As I said it is not a very descriptive message but I hope I have hit a general issue.
create from dbo.fromExampleasfrom dbo.Customers cjoin dbo.Invoices ion c.CustomerID = i.CustomerIDnow you can use it as follows:select c.CustomerID, i.InvoiceNofrom dbo.fromExampleI find I reuse FROM clauses alotand reusing a "base query" requires writing a very large and cumbersomeselect clause
Hi, I am having a problem looking up querystrings in my DB, I have the following code <asp:SqlDataSource ID="SqlData_products" runat="server" ConnectionString="<%$ ConnectionStrings:ProductDatabaseConnectionString2 %>" SelectCommand="SELECT * FROM [tbl_subProduct],[tbl_subCategory],[tbl_topCategory],[tbl_Material],[tbl_topLevelProduct] WHERE numSubCategoryID = @Category OR numMaterialID = @Material OR txtOrderCode = @Keyword OR txtMovexCode = @Keyword OR txtUKMapCode = @Keyword"> <selectparameters> <asp:QueryStringParameter Name="Category" QueryStringField="SearchCatString" /> <asp:QueryStringParameter Name="Material" QueryStringField="SearchMatString" /> <asp:QueryStringParameter Name="Keyword" QueryStringField="SearchKeyString" /> </selectparameters> </asp:SqlDataSource> My Querystrings are in the VB file:- SearchCatString = Request.QueryString("txtSelCat") SearchMatString = Request.QueryString("txtSelMat") SearchKeyString = Request.QueryString("txtKeyword")These come from a previous page, where a dropdown list copies the ID of the selected item into a text box. My problem is that this is not working please help
guys i'm trying to use a Lookup in a dataflow that looksup stuff in the results of a query.
Problem I have is that the query needs to take two parameters.. (Source and BaseCurrency in the code below) and i can't figure out how to supply the parameters..
Parameters can be supplied in other task types or transforms .. but can't see how to do it in the Lookup...
PJ
SELECT ForeignCurrency, RateFromFile AS YesterdaysRate
FROM inputrates IR
WHERE fileheaderid in (
SELECT top 1 MAX(ID) FROM FileInputAttempts FIA WHERE Source = '?' AND FIA.BaseCurrency = '?' AND status = 'SUCCESS' Group by CAST(FLOOR(CAST(LoadDate AS float))AS datetime) order by MAX(loaddate) DESC )
i am trying to write a query for phone number lookup . The query should be able to search numbers which have anything matching .... like if the person enters 1918767899 or enters 918767899 the query should be able to find both the records. Itried using the LIKE , but it doesn't work the way it is required.
Hi,Can you recommend the best way (fast and most productive) to search anemployees table?Let's say I have a table that has this kind of structure:firstname, lastname, state, city, street, phonenumber,socialsecuritynumber, dateofbirth, etc.I'd like to provide a search window with one text entry field, thatthe text entered will be searched at all fields, and even in more thenone field toghether (like if you enter "david z" it will search forlike '%david%' in first name and like '%z%' in last name).the problem is I can't write the query that'll perform fast but searchall of these fields, plus there's no way to use an index when usingLIKE.I can't loose the functionality described above (I'm not the client,and not the project manager) but I am trying to find an efficientsolution to the problem.Since most systems has some sort of directory table (like authors inpubs) you would think it will be a classic problem, but I found norecommendations on how to approach it.any help will be most appreiciated.
Two tables:T1 (c1 int, TestVal numeric(18,2), ResultFactor numeric(18,2))--c1 isthe primary key.T2 (x1 int, FromVal numeric(18,2), ToVal numeric(18,2), Factornumeric(18,2))--x1 is the primary key. T2 contains non-overlappingvalues. So for eg., a few rows in T2 may look like.1, 51, 51.999, 512, 52, 52.999, 52........32, 82, 82.999, 82........T2 is basically a lookup table. There is no relationship between thetwo tables T1 and T2. However, if the TestVal from T1 falls in therange between FromVal and ToVal in T2, then I want to updateResultFactor in T1 with the corresponding value of Factor from the T2table.------Example for illustration only---------------Even though tables cannot be joined using keys, the above problem is avery common one in our everyday life. For example T1 could beemployees PayRaise table, c1=EmployeeID, with "TestVal" representingtest scores (from 1 to 100). T2 representing lookup of the ranges,with "Factor" representing percent raise to be given to the employee.If TestVal is 65 (employee scored 65% in a test), and a row in T2(FromVal=60, ToVal=70, Factor=12), then I would like to update 12 intable T1 from T2 using sql;. Basically T2 (like a global table)applies to all the employees, so EmpID cannot serve as a key in T2.---------------------------------------------------------Could anyone suggest how I would solve MY PROBLEM using sql? I wouldlike to avoid cursors and loops.Reply appreciated.Thanks
I have two tables that are pretty standard I think. Table a has product descriptions and one of those fields is a price. I have a second table that contains fees based on the price. so table B looks like this.
min max fee 0 19.99 2.50 20.00 49.99 3.50 50.00 1000.00 5.50
the max ends up around a million just to be sure we cover all prices. my problem is this I need a very efficient query to poll all the values from A and the correct value from B. All the attempts I have made are not working. I also have to make sure this query is extremely efficient as it is executed several times a minute. If there is a better way in general to structure this I am all ears. I wanted to avoid placing the fees in the product table as the fees are updated often, but if its the only way to get this to work, then that is where I will go.
Hi Gurus, I have a Dataflow Task which has an OLE DB Source calling a SP with parameters (?, ?). Then this OLE DB Source is conencted to a Lookup Transform which also calls a SP but on a different database. I am unable to figure out how to pass parameters in a Look up Transform. In the 'Use Results of an SQL Query' pane of Lookup Transform:
Code SnippetEXEC GetMonthlyDataExtract 4, 2007
( I am passing month and year values) this works ok.
But when I chage to
Code SnippetEXEC GetMonthlyDataExtract ?, ?
It says EXEC not supported. Also I can not figure out how to configure parameters since 'Reference Table' Tab of the Lookup Transform does not have any option where we can attach variables to parameters. Also I am interested to map parameters to variables not to input columns. If mention if that is not possible or any other alternative.
When I retrieve data using an OLE DB Source I can create a SQL query and pass parameters to filter the data I get back. I'd like to do the same thing with the Lookup Transform but the parameters button isn't there. Am I missing something or do I have to use some special text format to insert my parameters into the query?
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?
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.
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.
In my package I am using lookup to get new and similar record. I want to filter the rows for Lookup Reference Data Set by using Variable Value.
I have created variable @[User::CustId] with Int32 datatype, having default value 2 when I am trying to evaluate below query I am getting error
"select CustId,PartNm,LocId,LocTyp from loc where CustId= "+ @[User::CustId]
Error. The Data types "DT_WSTR" and "DT_I4" are incompatible for binary operator "+".
The operand types could not be implicitly cast into compatible types for the operation. To perform this operation , one or both operands need to be explicitly cast with the operator.
I am using a lookup component in a SSIS data flow. The lookup is a select to a foxpro table. THe lookup works fine with full cache selected. I cannot get the lookup to work with a partial or no cache. I have the latest Foxpro OLE DB driver installed which I understand to support paramaterized queries. Has anyone had success with using cached lookup to Foxpro? Does anyone know how to set the lookup properties of sqlcommand and sqlcommandparam? I am unable to find any examples in BOL or on the web.
Here are some details. IF I go with "use a table or a view" option with the default cache query I get initialization errors
[lkp_lab_worst_value [6170]] Error: An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft OLE DB Provider for Visual FoxPro" Hresult: 0x80040E14 Description: "Command contains unrecognized phrase/keyword.".
In the advanced editor I see
SQLCommand set to
"select * from `kcf`"
and SQLCommandParam set to
"select * from (select * from `kcf`) as refTable where [refTable].[patkey] = ? and [refTable].[dayof_stay] = ? and [refTable].[modifier] = ? and [refTable].[kcf_code] = ? and [refTable].[source] = ? and [refTable].[kcf_time] = ?"
I believe the above error is because Foxpro V7 does not support the inner subselect . In addition the query contains CRLF without a continuation character (";").
If I remove the CRLF in the sqlcommandparam query, using the advanced editor, I get this design time error "OLE D error occurred while loading column metadata. Check the sqlcommand and sqlcommandparam properties". The designer requires both properties to be set, its unclear to me how the interact.
I cannot find any examples in BOL or on the web on how to set these 2 properties. Can someone give me a few guidelines?
I can get past the design errors by changing sqlcommandparam to a plain select that is VFP 7 compatible ( I removed the subselect and the square brackets):
select * from kcf as refTable where refTable.patkey = ? and refTable.dayof_stay = ? and refTable.modifier = ? and refTable.kcf_code = ? and refTable.source = ? and refTable.kcf_time = ?
But then I get a runtime error
[lkp_lab_worst_value [6170]] Error: An OLE DB error has occurred. Error code: 0x80040E46. An OLE DB record is available. Source: "Microsoft OLE DB Provider for Visual FoxPro" Hresult: 0x80040E46 Description: "One or more accessor flags were invalid.".
[lkp_lab_worst_value [6170]] Error: OLE DB error occurred while binding parameters. Check SQLCommand and SqlCommandParam properties.
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.
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:
I am building a search application that has several fields in it - I want to be able to allow the user to put in specific search criteria or all them to say "All" and then pull back any records with anything in that row. Example Select *From mytableWHERE (SDate >= 1-1-2007) AND (EDate <= 1-25-2007) AND (Location = "ALL" or In ALL) this is where I am cloudy.. What/How would I write in the query to pull back everything, I know I could just leave the Location out of the query all together, but then that does not allow them to select just one the next time they run the application. Thoughts? Thanks,Ad.
Hi everyone, I am trying to run this query and it is not returning a single row. Although I can see one row with this 10/14/2005 date in the table.SELECT iSourceId, UserId,FROM tblEmployeeWHERE dtInsertDate >= '10/14/2005' and dtInsertDate <='10/14/2005'I also tried to rewrite the query this waySELECT iSourceId, UserId,FROM tblEmployeeWHERE dtInsertDate = '10/14/2005' but still it is not returning a single row.Please let me know what am I doing wrong.Thanks.
SELECT t1.fname, t1.lname, t2.district_name, t2.district_number FROM t1 AS table1, t2 AS table2 WHERE t2.district_name LIKE 'S%'
i have these values in the table
table1
fname lname mike jackson roy mires
table2
district_name district_number South 123 Daggerty 7845 duffel 7224 rubble 7545
Now the query is dynamic (the letter is that the like clause is run against)
When the letter D is searched for i get the 2 colums duffel, daggerty BUT when S is searched against I get nothing..
I am confused as to why, It doesn't seem to be case sensitive, as the 2 colums duffel and Daggerty 1 is d is in lowercase and the other is in uppercase. andive tries both lowercase s and uppercase S and still got nothing.
Is tehre a better way to use the LIKE clause? Ive looked and looked for documentation about the LIKE clause but I cannot find anything
How can I get all names that start with "sci" or "eng" without using the OR clause. In other words, how can I modify the following statement so I don't use the OR clause: select * from course where (name like 'sci%' or name like 'eng%')
I have created a profile object which is a list to store photoId into an array. (kinda like a shopping cart) which will persist in the list untill manually deleted by the user.I want to use the photo Ids stored in this object in my profile as a parameter for an sql select statement to be displayed into a data grid. What is the best way to go about this... and suggestions... I know you can use a profile property as a parametere in a sqldatasource but Im not sure how to do this for an array.HELP
A user in my group created a batch of tables - now belonging to his id. I have permissions to use all those tables - however I must type the owner/table to use those objects. Is there any method of only typing in the table/object name instead of owner/table?
Does anyone know how to count rows of data from 1 table that meet one or two different criteria and then get the probability of occurance for that criteria ... as an output column? Do I use 'Having' or temp tables or Views? Here is my output for now. I am trying to solve for 'Prob' - everything else works on it's own.Craig
SELECT Wins_Lng/Trades_Lng as Prob FROM Transactions SELECT Count(Ticker) as Trades_Lng FROM Transactions WHERE TransType='C' AND DateDiff(day,BaseDate,GetDate())<=100 AND TransKind='B' (SELECT COUNT(Ticker)as Wins_Lng FROM TransactionsWHERE Transkind='B' AND TransType='C' AND DateDiff(day,BaseDate,GetDate())<=100 AND Profit_Lng>=0)
