We have a package using a lookup query on DB2 to validate data from a file. Everything works fine, except for the lookup query that has to cache about 1,5 million rows.
Now I would like to specify parameters to that query to minimize the data being cached. I tried using parameters in the query, but I get an error:
"Provider cannot derive parameter information and SetParameterInfo has not been called."
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 )
1) I am using exceute SQL tasks in my control flow. 3 variables have been defined at the package level.They are mapped to 3 parameters respectively in the Execute SQL task.
When I try using these parameters in SQL error is thrown.Query is not getting parsed.My connection is OLEDB. Target and source are in SQL Server.
Can anyone suggest a workaround?
2) Before loading my target I need to define a Lookup . My requirement is if say consumer key matches in fact table then update it else insert.
2 kinds of lookup are available in SSIS dataflow tools. Simple Lookup for exact matching and Fuzzy Lookup for matching based on probability.
Neither of it supports my requirement? Can i put a select and insert query directly in Lookup or will need to call it from a file as a stored procedure?
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?
I've been searching around for a while now and slowly been making progress but I've finally hit a road block and I'm wondering if anyone else has ever gotten this to work. I'm using SS SP2 and the Microsoft OLE DB Provider for Oracle.
I have a lookup task in the data flow. The lookup table is in Oracle and it works fine as long as I don't check the "Enable Memory Restriction" box on the Advanced tab. As soon as that box is checked, the task will throw an error when I try to run it. I need to check it though to get to the Modify SQL Statement. Here is what I do: Create new Lookup task Set the Oracle OLE DB connection Use the following SQL for the reference table source: SELECT COST_CENTER_ID, COST_CENTER_NB, start_dt, end_dt, decode(SIGN(TO_NUMBER(TO_CHAR(START_DT,'MM'))-9),-1, TO_CHAR(START_DT,'YYYY'), 0, TO_CHAR(START_DT,'YYYY'), 1, TO_CHAR(START_DT + 365,'YYYY') ) START_FY, decode(SIGN(TO_NUMBER(TO_CHAR(END_DT,'MM'))-9), NULL, decode(SIGN(TO_NUMBER(TO_CHAR(SYSDATE,'MM'))-9), -1, TO_CHAR(SYSDATE,'YYYY'), 0, TO_CHAR(SYSDATE,'YYYY'), 1, TO_CHAR(SYSDATE + 365,'YYYY') ), -1, TO_CHAR(END_DT,'YYYY'), 0, TO_CHAR(END_DT,'YYYY'), 1, TO_CHAR(END_DT + 365,'YYYY') ) END_FY FROM DIM_COST_CENTER
Then I go to the columns page and connect 1 field from the input column to the lookup column. Then click ok and it runs fine. However, now I go to the advanced page and click the Enable Memory Restriction (at this point is where the problem occurs). As soon as the memory restriction is checked, the thing throws errors: [Lookup [4732]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft OLE DB Provider for Oracle" Hresult: 0x80040E14 Description: "ORA-00933: SQL command not properly ended ". Then if I go in and Modify the SQL Statement into the Oracle syntax by removing the word AS and the [ ]'s it will get a new error: select * from (SELECT COST_CENTER_ID, COST_CENTER_NB, start_dt, end_dt, decode(SIGN(TO_NUMBER(TO_CHAR(START_DT,'MM'))-9),-1, TO_CHAR(START_DT,'YYYY'), 0, TO_CHAR(START_DT,'YYYY'), 1, TO_CHAR(START_DT + 365,'YYYY') ) START_FY, decode(SIGN(TO_NUMBER(TO_CHAR(END_DT,'MM'))-9), NULL, decode(SIGN(TO_NUMBER(TO_CHAR(SYSDATE,'MM'))-9), -1, TO_CHAR(SYSDATE,'YYYY'), 0, TO_CHAR(SYSDATE,'YYYY'), 1, TO_CHAR(SYSDATE + 365,'YYYY') ), -1, TO_CHAR(END_DT,'YYYY'), 0, TO_CHAR(END_DT,'YYYY'), 1, TO_CHAR(END_DT + 365,'YYYY') ) END_FY FROM DIM_COST_CENTER) refTable where refTable.COST_CENTER_NB = ?
Now when running I get the error:
[Lookup [4732]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E5D. An OLE DB record is available. Source: "Microsoft OLE DB Provider for Oracle" Hresult: 0x80040E5D Description: "Parameter name is unrecognized.". Followed by: [Lookup [4732]] Error: OLE DB error occurred while binding parameters. Check SQLCommand and SqlCommandParam properties.
This is where I get stuck. I've gone into the XML and looked through everything and it all seems to match up in terms of variables lineage ID's and such, but I can't see any place to set the parameter name, which should be 0 since it is OLE DB. When I click the Enable Memory Restriction, the only difference I can notice in the XML is that the cachetype line changes from 0 to 2.
<property id="4738" name="CacheType" dataType="System.Int32" state="default" isArray="false" description="Specifies the cache type of the lookup table." typeConverter="CacheType" UITypeEditor="" containsID="false" expressionType="None">2</property>
Has anyone ever got parameters to work with Oracle and a lookup? Any work arounds? I have used a Merge Join with Conditional Split successfully, but I have about 5 other lookups that have to be done and it will be a killer and lots of work to try and re-sort for each merge join and conditional splits for each of them. Looking for any help with making the lookup work or some nicer work arounds.
Parameterized queries are only allowed on partial or none cache style lookup transforms, not 'full' ones. Is there some "trick" to parameterizing a full cache lookup, or should the join simply be done at the source, obviating the need for a full cache lookup at all (other suggestion certainly welcome)
More particularly, I'd like to use the lookup transform in a surrogate key pipeline. However, the dimension is large (900 million rows), so its would be useful to restrict the lookup transform's cache by a join to the source.
For example:
Source query is: select a,b,c from t where z=@filter (20,000 rows)
Lookup transform query: select surrogate_key,business_key from dimension (900 M rows, not tenable)
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.
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 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.
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.
Hi all, From the "How to Call a Parameterized Stored Procedure by Using ADO.NET and Visual Basic.NET" in http://support.microsft.com/kb/308049, I copied the following code to a project "pubsTestProc1.vb" of my VB 2005 Express Windows Application:
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlDbType
Public Class Form1
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim PubsConn As SqlConnection = New SqlConnection("Data Source=.SQLEXPRESS;integrated security=sspi;" & "initial Catalog=pubs;")
Dim testCMD As SqlCommand = New SqlCommand("TestProcedure", PubsConn)
testCMD.CommandType = CommandType.StoredProcedure
Dim RetValue As SqlParameter = testCMD.Parameters.Add("RetValue", SqlDbType.Int)
Console.WriteLine("Number of Records: " & (NumTitles.Value))
End Sub
End Class
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// The original article uses the code statements in pink for the Console Applcation of VB.NET. I do not know how to print out the output of ("Book Titles for this Author:"), ("{0}", myReader.GetString(2)), ("Return Value: " & (RetValue.Value)) and ("Number of Records: " & (NumTitles.Value)) in the Windows Application Form1 of my VB 2005 Express. Please help and advise.
I have a SSRS report with four parameters,and I want to be able to enter information for two of the parameters and run the report opposed to all four of them. However, when I select allow blanks and only select the parameters that I want to run the report by, the report come back blank..Essentially, I want to be able to the run report by different parameters without having to enter information for all parameters at the same time.
Hi, I have an app in C# that executes a query using SQLCommand and parameters and is taking too much time to execute.
I open a SQLProfiler and this is what I have :
exec sp_executesql N' SELECT TranDateTime ... WHERE CustomerId = @CustomerId', N'@CustomerId nvarchar(4000)', @CustomerId = N'11111
I ran the same query directly from Query Analyzer and take the same amount of time to execute (about 8 seconds)
I decided to take the parameters out and concatenate the value and it takes less than 2 second to execute.
Here it comes the first question... Why does using parameters takes way too much time more than not using parameters?
Then, I decided to move the query to a Stored Procedure and it executes in a snap too. The only problem I have using a SP is that the query can receive more than 1 parameter and up to 5 parameters, which is easy to build in the application but not in the SP
I usually do it something like (@CustomerId is null or CustomerId = @CustomerId) but it generate a table scan and with a table with a few mills of records is not a good idea to have such scan.
Is there a way to handle "dynamic parameters" in a efficient way???
When I query the ReportServices WS and scan the parameter dependencies for SalesRep it says there are four dependencies: FromDate, ToDate, DivisionalOffice and Manager!!!
If I change "dsSalesRep" to use "DivisionalOffice.Value" the ReportingServices WS parameter dependency scan returns only one dependency for "SalesRep" parameter!!!( This is the correct behavior )
Has anybody seen this behavior and more importantly, is there a work around?
Hello all, Given: string commandText = "Categories_Delete";SqlCommand myCommand = new SqlCommand(commandText, connection);myCommand.CommandType = CommandType.StoredProcedure; Is there a reason NOT to use myCommand.Parameters.AddWithValue("@CategoryID",CategoryID); I'd prefer to use that over myCommand.Parameters.Add("@CategoryID", SqlDbType.Int, 4).Value = CategoryID; as I have these functions being created dynamically and hope to get away from a big lookup to try to convert System.Types into SqlDbTypes. [shudder] It seems that ADO.NET makes an implicit conversion to the valid type. If this is correct then I can move on fat dumb and happy. Anyone have any good insight? Thanks,
I want to lookup values from a database into another database both of which are in the same sql server 2000. One databases is called GamingCommissiondb the other is called LicensingActions I need some of the tables to communicate with each other, to look values from one another. Example I need the Termination table to look up values from the Revocations table. Would using LinkedServers suffice??
Hi, Can anybody provide me a Lookup UDF? I need to supply columnname,Tablename and condition dynamically and I need the scalar value in return. Any help will be greatly appreciated...
Hi, I am transferring the xml data from an xml file into sql server table using ssis. To avoid any duplicate import via this ssis package, I would like to first check if data exists in the sql server table for what is about to be imported. If so then delete the existing data and then import.
Question: How do I get the field value say ID field from the file and then take this id and delete these from the table in sql server first.
Is this to do with lookup or is there an easier way to do this please? thanks
I am trying to use lookup to see if a item esists in my table ( 3 key fields ). If the lookup fails I want to insert the records. If it succeeds I have put a recordcount to catch the items that are not required. I don't think that I understand the settings for failed rows. I have tried setting the Configure Error Output to redirect, but this does not seem to work. I have the below errors.
[SQL Server Destination [151]] Error: Unable to prepare the SSIS bulk insert for data insertion.
[DTS.Pipeline] Error: component "SQL Server Destination" (151) failed the pre-execute phase and returned error code 0xC0202071.
Can someone please advise me how to set up this component to work for my application
I am using SCD and Lookup for Incremantal ETL load. But it is very interesting that my 3000 recs is becoming 2500 when I put SCD and lookup. if I replace dummy multicast then it is 3000.
Before inserting records of a recordset I would like to check that each one of these records doesn't exist in the destination table 1. If the records exist, they should be redirect to other table 2. For this purpose I use a lookup task. The referende table is the destination table 1. The green line is linked to table 2 and the red line is linked to table 1. The first execution works properly and copies de records to table 1, but on the next executions the records are copied to table 1 instead of being copied to table 2.
say i have a customer.aspx that allows a user to enter in customer data. on customer.aspx, i have dropdownSalesRep which allows the user to associate a sales rep with the customerbut some customers come to directly, and not thru a sales rep, so I want the user to be able to specify "none" Is it best to have a dummy record in my SalesReps table called "none" with an ID of say "999", or is there some other better way to deal with this?
I really need to know if it is possible to look in a Transaction Log for a specific transaction, it is for audit purposes. In the affirmative case, could you teach me how to do it or at least point me in the right direction.
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 am trying to understand how to create an SP that will do the following on a varchar field: Someone enters a [Policy No] and the result set returns the [Policy No] the user entered (if found), plus 10 [Policy No]'s above and 10 [Policy No]'s below the [Policy No] they were looking for.
I figure this has to be a common thing that others have encountered, of course maybe not on a varchar data type field, but still this must be possible. If anyone can shed some light on this SP-Lookup with either some code or a general direction to go towards - I would greatly appreciate it! Thank you, Black
I have two tables: (results) one with numeric results and one with names (importance_scale)
SELECT * FROM results
name i1 i2 i3 i4 i5 ---- ---- ---- ---- ---- john 2 1 2 2 2 anna 0 2 2 2 2 phil 2 2 2 2 2 dave 1 0 2 2 2
SELECT * FROM importance_scale
imp_value imp_name --------- ------------ 2 very important 1 important 0 not important
i1,i2 etc. value it is a imp_value from table 'importance_scale'
I would like to make report which will show all results in this format (in example only first record): name i1 i2 i3 i4 i5 etc. ----- --- --- --- --- --- john very important important very important very important very important etc.
I was thinking about using CASE I don't want use cursor. What is the best way?
Hi, this may be a basic question but i can't get my mind straight and need some advice.
I've read there's limitations on using the Lookup Task.
What i want to do is simply look if a Data exists on a table, if it does i want to update some columns (or delete the row and insert a new row), if it doesnt exists i want to inser a row.
That's all, easy as hell in sql and c#/.net
The thing is i dont know whats the best way of doing it in SSIS.
My limited ssis knowledge tells me Script Component should be able to do the work. That is, send it a variable holding the values i want to verify in the database and depending if it exists to the whats necesary.
So my question is, can i connect to the DB via Script and make my custom selects there?
If so how can i do it?
If not, what's the best way of doing that simple task?
I am using a fuzzy lookup to cleanse data from a sales line details table, during the import process. The sales order line details contains a filed called 'reference' and this is compared to a field called 'category' in another table. Using data viewers to check through the cleansing process, I notice that the fuzzy lookup doesn't seem to match i.e. tbl.salesline.reference = 'I3' -> tbl.sales.category ='I03' the above is OK, but the lookup also returns the following tbl.salesline.reference = 'I9' -> tbl.sales.category ='I01' The value I9 doesnt exist, and is miskeyed by user entry, and should have been 'I99'. I would have expected the fuzzy lookup to pickup the I99 value as at least two of the chrs are matching, but no, it picks the first 'I*' in the table. If I expand the fuzzy lookup to return more results, i.e. 5 per record, then it returns the first 5 results....I01, I02 I03 and so on. Is there a way of improving the fuzzy lookup itself?
