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?
Thanks in advance.
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
)
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.
Your help will be appreciated.
Thanks,
Paraclete
I'd like to know if it is possible to automatically (or through ActiveX programming) trigger a DTS transformation task to refresh it's query
and auto re-map its source to destination columns based on the query.
I have a DTS package that exports all columns from a table
(Select * from table) with no special column transformations when
copying to the text file destination. I'd like to be able to trigger
a refresh of the column mapping. The "Define Columns" "Populate from Source"
buttons on the Transform Data Task component does this but I'd like to achieve this action as a separate task.
Thank you kindly,
Sonya Lisi
Hi,
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."
Anyone had that problem?
I am using Microsoft's OLE DB Provider for DB2.
Thanks
Hi,
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?
Please suggest a solution for this too.
Thanks in advance.
Regards,
Aman Anand
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.
Thanks.
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)
Ideal Lookup transform query:
select distinct surrogate_key
,business_key
from dimension d inner join
t on d.business_key = t.c
where t.z = @filter
Hi All, am new to IS 2005 but i've worked with DTS packages in 2k, i like to know if its possible to do multiple data transforms in IS, i want to copy 2 tables data from one database to another database.
View 4 Replies View Related
I understand that only Enterprise Edition has support for Fuzzy Lookup and Fuzzy Grouping transforms, but we will likely be migrating to Standard shortly - Enterprise would be complete overkill for our organisation and needs, and is beyond our budget constraints too.
I have been using these in the CTP of Katmai, and have found them exceptionally useful for my work.
Any chance of these transforms being made available as an "add-on" for Standard (or other versions)?
It does not seem that it is something inextricably tied to the different versions - just a licencing decision, right?
Or would the proposal be to go with Standard and one additional purchase of Developer Edition?
Sham
Derived column that transforms a CASE statement?
CASE
WHEN [MAIN_GAME] IS NULL AND combo.[CAT_PRODUCT] ='25' THEN 'Standalone'
WHEN [MAIN_GAME] IS NULL AND combo.[CAT_PRODUCT] <> '25' THEN 'No main game'
ELSE LOWER(prod.[TX_PRODUCT_NAME])
END AS [TX_MAIN_GAME]
CAT_MAIN_GAME = nvarchar(6)
FK_GAME = nvarchar(2)
I implemented a custom source adaptor. I want to be able to associate custom properties with each of the output columns. I want them to be passed downsteam. The idea is to be able to retrieve these information in a downstream custom transformations of ours and process the various columns accordingly. How do I go about doing this?I noticed that the IDTSCustomProperty90 seems to have a local scope only.
View 1 Replies View RelatedHi,
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???
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
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.
Pls help !!
Regards
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.
View 2 Replies View RelatedTwo 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
View 1 Replies View Related
Hi,
I have a Dimension Dim_Customer with these fields:
CustomerKey (mandatory)
CustomerAccount
CompanyKey (mandatory)
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....
anyone who can help?
che
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.
Thanks everyone
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.
Any ideas on what else could be causing this?
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.
The enterprise edition of SQL server includes some advanced BI features, for example the fuzzy lookup feature of IS. If the IS package lives on an enterprise edition of SQL server and the database the package it is targeting lives on a standard edition of SQL server can the advanced features be used? Can you run a fuzzy look against a database on a standard edition of SQL server when th IS package lives on an enterprise edition of SQL server? THANKS!
View 1 Replies View RelatedI 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.
Any idea on what I should try next ?
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 ...
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"?
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.
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.
I am trying to search a SQL database using a TextBox and a DropDownList. The textbox being where the user enters there search query and the dropdownlist allowing them witch column to search. If I hardcode in the table column to search everything works fine but as soon as I parameterize the column to search I no longer get any results. While stepping through the code both parameters do get assigned the correct values to make the sql statement valid. Any advice would be appreciated.
.... code that does not work .....
string connection = ConfigurationManager.ConnectionStrings["serverlist"].ConnectionString; string sqlquery = "SELECT server_id, hostname, os, description, owner FROM server_info WHERE @SearchItem=@SearchQuery";
conn = new SqlConnection(connection); comm = new SqlCommand(sqlquery, conn);
String searchQuery = searchBox.Text; String searchItem = DropDownList1.SelectedValue;
comm.Parameters.Add("@SearchQuery", System.Data.SqlDbType.VarChar); comm.Parameters["@SearchQuery"].Value = searchQuery; comm.Parameters.Add("@SearchItem", System.Data.SqlDbType.VarChar); comm.Parameters["@SearchItem"].Value = searchItem;
......
Hi all,
I am trying to write an sql query in my web application to select some records from the database using parameters. I use SqlClient for that. The query has a WHERE clause where its supposed to values from the various dropdownlists that I have in my application. I bind the SelectedValue from the dropdownlists to the appropriate parameters and the SQL query does not work if all the conditions are satified in the WHERE clause.
Can I have an SQL query where I can provide all the parameters in the WHERE clause (with AND conditions) and it takes only those values where there is some value from the dropdownlist and ignore the rest of the parameters.
This is what I am trying to do: private DataSet GetSalesDataBas()
{
DataSet ds = new DataSet();
SqlConnection connection = new SqlConnection("...");
SqlCommand command = connection.CreateCommand();
SqlParameter prm_1 = new SqlParameter();
SqlParameter prm_2 = new SqlParameter();
SqlParameter prm_3 = new SqlParameter();
SqlParameter prm_4 = new SqlParameter();
SqlParameter prm_5 = new SqlParameter();
SqlParameter prm_6 = new SqlParameter();
SqlParameter prm_7 = new SqlParameter();
prm_1.ParameterName = "@pNum";
prm_2.ParameterName = "@sNum";
prm_3.ParameterName = "@paramLoc";
prm_4.ParameterName = "@paramDist";
prm_5.ParameterName = "@paramPNum";
prm_6.ParameterName = "@paramDesc";
prm_7.ParameterName = "@paramBuild";
prm_1.Value = TextBox2.Text.Trim();
prm_2.Value = TextBox3.Text.Trim();
prm_3.Value = DropDownList15.SelectedValue;
prm_4.Value = DropDownList12.SelectedValue;
prm_5.Value = DropDownList21.SelectedValue;
prm_6.Value = DropDownList13.SelectedValue;
prm_7.Value = DropDownList18.SelectedValue;
command.Parameters.Add(prm_1);
command.Parameters.Add(prm_2);
command.Parameters.Add(prm_3);
command.Parameters.Add(prm_4);
command.Parameters.Add(prm_5);
command.Parameters.Add(prm_6);
command.Parameters.Add(prm_7);
string strWhere = "";
if (DropDownList23.SelectedIndex == 0)//if no sort field selected
{
strWhere = @" WHERE ""UserName""='" + userName + "' AND ([Status] LIKE 'REF%A' OR [Status] LIKE 'A%')" +
" AND m.[Part Number] LIKE '%' + @pNum + '%'" +
" AND m.[Serial Number] LIKE '%' + @sNum + '%'" +
" AND (Location = @paramLoc" +
" AND m.[District] = @paramDist" +
" AND m.[Part Number]= @paramPNum" +
" AND m.[Description]= @paramDesc" +
" AND m.[Building]= @paramBuild" +
")";
}
else
{
strWhere = @" WHERE ""UserName""='" + userName + "' AND ([Status] LIKE 'REF%A' OR [Status] LIKE 'A%')" +
" AND m.[Part Number] LIKE '%' + @pNum + '%'" +
" AND m.[Serial Number] LIKE '%' + @sNum + '%'" +
" AND (Location= @paramLoc" +
" AND m.[District]= @paramDist" +
" AND m.[Part Number]= @paramPNum" +
" AND m.[Description]= @paramDesc" +
" AND m.[Building]= @paramBuild" +
")" +
" ORDER BY " + DropDownList23.SelectedValue + " " + DropDownList24.SelectedValue;
}
string sqlSalesData = @"SELECT * FROM Main m" + strWhere;
command.CommandText = sqlSalesData;
SqlDataAdapter salesOrderAdapter = new SqlDataAdapter(command);
salesOrderAdapter.Fill(ds, "Main");
salesOrderAdapter.Dispose();
command.Dispose();
return ds;
}
I am creating a package within which I have a query step that needs to accept
a value from a variable step that has already been run, but I cant get it to work and the books online dont help!
The query is simple
insert into table1
select col1 = cola,
col2 = colb,
col3 = ?
from tableA
Even though the variable that I want use has been set, I cant get the query to recognise there are any parameters. If I click the parameter button in the query set-up, I get a message saying that col3 is an invalid column name and then a box saying that an error occured when parsing the statement fro parameters !
Can anybody help ?
Thanks
Tom
I'm not sure if any1 can answer is on this forum, but any help would be VERY appriceted....
I am creating a report using Visual Studio.Net..I want to write a query using parameters BUT in this case I dont no what the parameter will be...
ie the user can enter a customer account OR a customer group
and sort can be on product OR total sales...
Any ideas?!
Thanks!!! :)