In my Integration Scenario I have a source field 'CompanyTypeCode' which is nullable in 'tblCompany' and There is a specific lookup Table 'tblCompanyType' to map the codes with descriptions for this field. When I want to transform this table, The NULL Entries fail in Lookup and I ran into problem. Please suggest a clean solution for bypassing the lookup when the entry in source is NULL. (If I bypass the lookup, the flow changes since there is not an extra column from the lookup component any more, therefore I must have another OLEDB Destination if the code is NULL).
I am using a script component to retrieve some values from a server. This script component using Script task which uses VB.NET code. I have tested the VB.NET code and it returns a non null value for a string field 'sentence'. The 'sentence' feild is then passed on to an OLE DB Command transformation to be stored into the database along with a couple of other feilds.
The OLE DB Command transformation uses a stored procedure to store these feilds into the database. But when I check my table in the database, all other feilds are stored properly other than the 'sentence' feild which is null.
I'm using SQL Server 2000. I have an EXTERNAL_FUNDING table and an EXTERNAL_FUNDING_TYPE table. The DDL for the tables is listed below. Each EXTERNAL_FUNDING record has a type field, which is a foreign key to a record in the EXTERNAL_FUNDING_TYPE table. However, I would like for the type to be allowed to be null. When displaying the data, if the type is null, I'd like to show blank text. Here is the query I'm trying to use: SELECT EXTERNAL_FUNDING.id, date_requested as [Request Date],source as [Source/Sponsor],EXTERNAL_FUNDING_TYPE.type AS [Type],amount as [Amount], granted as [Granted] FROM EXTERNAL_FUNDING,EXTERNAL_FUNDING_TYPE WHERE (project_id = @project_id) AND (EXTERNAL_FUNDING.type = EXTERNAL_FUNDING_TYPE.id) ORDER BY date_requested ,source,type
But this query does not return the records with NULL for the type field. Can someone explain to me how I can also return the rows with a NULL type? Thanks!
Can you please tell me the way to configure the LOOK UP transformation so that it will ignore all the null values ? I want to configure a Look up component for the column "Col1" as follows
All the NULL values of Col1 should not be considered for look-up process. They should be passed to the downstream component as valid rows. All NOT NULL values of Col1 should be processed by the Look up component. If there is no matching value present for any NOT NULL value of Col1 then it should be directed to error output.
I have a column defined as smalldatetime. Default length (4), and "allow NULLS" is checked.In the Enterprise Manager UI, when i enter data into that table row, if i just tab past that column, all is well, and the value is represented in the UI as <NULL>.The problem comes once i ever enter a date into that column. Say i have entered a date (all is well), and now i want to remove that entry and go back to NULL (after the date value has been committed, different entry session, say).How is that done?It seems to me, once a date has ever been entered into that column, now, if i try to remove it, i get the error "The value you entered is not consistant with the data type or length of the column, or over grid buffer limit". I have tried deleting the value, entering spaces, entering the string NULL or the string <NULL>; maybe some other tries as well, but none works, i always get that error message and am not allowed to proceed past that cell until i restore a date value to it. I want to get back to <NULL>.Anybody know?Thank you.Tom
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:
Hey. I need to substitute a value from a table if the input var is null. This is fine if the value coming from table is not null. But, it the table value is also null, it doesn't work. The problem I'm getting is in the isnull line which is in Dark green color because @inFileVersion is set to null explicitly and when the isnull function evaluates, value returned from DR.FileVersion is also null which is correct. I want the null=null to return true which is why i set ansi_nulls off. But it doesn't return anything. And the select statement should return something but in my case it returns null. If I comment the isnull statements in the where clause, everything works fine. Please tell me what am I doing wrong. Is it possible to do this without setting the ansi_nulls to off??? Thank you
set ansi_nulls off
go
declare
@inFileName VARCHAR (100),
@inFileSize INT,
@Id int,
@inlanguageid INT,
@inFileVersion VARCHAR (100),
@ExeState int
set @inFileName = 'A0006337.EXE'
set @inFileSize = 28796
set @Id= 1
set @inlanguageid =null
set @inFileVersion =NULL
set @ExeState =0
select Dr.StateID from table1 dR
where
DR.[FileName] = @inFileName
AND DR.FileSize =@inFileSize
AND DR.FileVersion = isnull(@inFileVersion,DR.FileVersion)
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.
Can someone please help me solve the following error? I am not sure of the version my hosting company is using and where to get the manual. Could not add the entry because: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '-id, title, entry, date_entered) VALUES ('testing', 'testing', . The query was INSERT INTO blog_entries (blog-id, title, entry, date_entered) VALUES ('testing', 'testing', 'testing'.
PHP Code:
if ($dbh = mysql_connect ('localhost', 'blu6592_money', 'rich')) { print '<p>Successfully connected to MySQL.</p>';
if (mysql_select_db ('blu6592_myblog')) { print '<p>The database has been selected.</p>'; } else { die ('<p>Could not select the database because: <b>' . mysql_error() . '</b></p>'); }
if (mysql_query ($query)) { print '<p>The blog entry has been added.</p>'; } else { print "<p>Could not add the entry because: <b>" . mysql_error() . "</b>. The query was $query.</p>"; }
} else { die ('<p>Could not connect to MySQL because: <b>' . mysql_error() . '</b></p>'); }
(I don't post here often, so in case I'm violating long-standing taboosof this newsgroup, I apologize in advance for calling a relation atable, using nulls, and other ignorant, destructive, and comtemptibleterminology.)I have a table that's keeping a sort of running log of different typesof changes to pieces of data. The table has a foreign key of the databeing changed, the foreign key for the type of change occuring, someinformation about the change in a couple more columns, and a timestampfor each entry. So it's:dataIDeventIDeventInfotimestampWhat I'd like to do, if at all possible, is a single SQL query that,given a dataID, returns the most recent eventInfo and timestamp foreach eventID. Is this possible?Many thanks.-Eric
"High priority system task thread: Operating system error Exception 0xAE encountered."
we are seeing this on one of our development servers, running SQL 2005 Enterprise SP1. The symptom is that after the sql service has been running a while (order of days), this error starts getting written to the sql server log file over and over, and eventually the logfile fills the drive.
Apparently it's fixed by this hotfix, but our ops team doesn't want to install this - they'd rather just cycle the service and delete the log when it happens.
I was wondering if anyone else has seen it might know the root cause so we might be able to workaround it without cycling the service. I know, lame question. The correct answer is "install the hotfix" or "install sp2" but our ops team won't do that for now...
I have a table with name C1_Subscribers with three fields (1)-MobileNumber [varchar] (2)-ReceivedTime [datetime] (3)-Status [char]. Now here how to remove duplicate entry of same mobile number to MobileNumber field?
In my report i have CNAME parameter , which allows null value. I checked Allow null value check box in report parameter properties.
when i preview the report , it displays checked NULL check box beside CNAME parameter . I want to give some meaningful name(i.e.ALLCustomers) to this checkbox instead of NULL.
In the flat file SampleID and Product are populated in the first row only, rest of the rows only have values for Rep_Number, Protein, Fat, Solids.
SampleID and Product are blank for the rest of the rows. So my task is to fill those blank rows with the first row that has the sampleID and Product and load into the table.
Hello, I have a table of names/dates as such: Class 1 1/1/2007Class 1 1/3/2007Class 1 1/5/2007Class 2 2/1/2007Class 2 2/3/2007Class 3 3/1/2007 What I want to do is select only the nearest entry from the list for each distinct class, so the results are like this: Class 1 1/1/2007Class 2 2/1/2007Class 3 3/1/2007 So basically, I want the first upcoming class in the list for each distinct class name. How do I do this? I'm using SQL 2005 Express. Thanks.
How do i do that... in sch lab, the lecturer said: Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click Try SqlDataSource2.SelectParameters("Title").DefaultValue = txtProductTitle.Text GridView1.DataBind() If GridView1.Rows.Count > 0 Then //if no repeated Label1.Text = "Existing Record Found. Please enter another Software." Else 'SqlDataSource1.Insert() 'Label1.Text = txtProductTitle.Text & " successfully added to the database." End If Catch ex As Exception Label1.ForeColor = Drawing.Color.Red Label1.Text = "An error occured!" & ControlChars.NewLine & ControlChars.NewLine & _ ex.Message.ToString() End Try End Sub but its not working for me... the GridView1.Rows.Count is always 0, if there is a repeat, it should be > 0
Hi, I know this is probably very simple but I am pretty new to this and have tried looking but cant seem to get the search criteria right. I have a database with a storeDate field which is of shortdatetime type. I am connecting to the database (MSSQL) via a stored procedure and returning all the records. I then use the code foreach (DataRow dr in ds.Tables[0].Rows) { DateTime dtTo = DateTime.Now; DateTime dtFrom = DateTime.Parse(dr["storeDate"].ToString()); TimeSpan diff = dtTo.Subtract(dtFrom); } I am basically trying to find out the age of the database entry by subtracting it from the current DateTime so i can delete records over a certain age. The problem (at least one of them!) is retrieving the "storeDate" object from the database and storing it in the dtFrom object. I have tried just assigning it directly as dtFrom = dr["storeDate"] and various other methods but I just don't know enough to assign it! Can anyone help me with this or spot any other mistakes in this process of removing old files automatically. Greatly Appreciated,Sean.
Hi,I'm creating a database using SQL Server 2005 Express Edition (Comes with Visual Web Developer). The table which I am creating has the following Fields - all don't allow nulls:IDUserIdDateDescription(UserId is a foreign key to asp_net_Users as I am supporting user accounts)Basically what I need to do is create a page where I as an Administrator can log onto and enter just the text for the field Description. Then once I upload this I wish all users to visit the site and view this Description on a page however with it also listing the Administrator who wrote it along with the Date. I wish both of these fields to be added automatically (UserId to display the User Name and the Date to display the date and time with which the Description was added - However these need to be editable by the Administrator if he/she wishes to change them).Can anyone point me in the right direction on the steps needed to create this scenario?Thanks for any helpDaniel
Here's some code that says it should identify if a user already exists in my database. I have changed the code to match my database, but it seems to have somewhat the opposite affect, rejecting all names (even new ones) or accepting all names (including existing ones). The switch in situations occurs in the "if" statement towardsd the end, when I change the sign of objDR.RecordsAffected. Do you have any idea what could be wrong? Thanks. Function DoesUserExist(ByVal userName As String) As Boolean Dim connectionString As String = "server='(local)Netsdk'; trusted_connection=true; Database='AuthorizedUsers'" Dim sqlConnection As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection(connectionString)
Dim queryString As String = "SELECT [Users].[UserName] FROM [Users] WHERE ([Users].[UserName] = @UserName)" Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(queryString, sqlConnection) Dim Cmd as New SQLCommand(queryString, sqlConnection) With Cmd.Parameters .Add(New SQLParameter("@username", username)) End With
sqlConnection.Open Dim blHasRows As Boolean Dim objDR As System.Data.SqlClient.SqlDataReader = Cmd.ExecuteReader(system.data.CommandBehavior.CloseConnection)
if objDR.RecordsAffected > 0 then blHasRows="True" else blHasRows="False" End If
To insert entries into a table. The table has a primary key based on a field 'ID'. When inserting into the destination table, I want to make sure that the new entry will overwrite the old entry.
Howdy all. Im trying to write a query that will track a data modification grouped by employer ID and transaction date. I don't know if Im asking it right so here is what I have, plus my current and desired outputs.
Newbie here, just wanted to get that out of the way first and foremost.
Im trying to set up a DB in SQL 2000 that allows me to enter lots of text into a column.
I read one site where it said to use the NTText datatype with a length of 16. I did that, but everytime I enter alot of text into that column I get <Long Text> and I dont know how to output that or even read it.
Do any of you guys know how to either:
1. get the text out of the <long text> or 2. a better way to store that much data.
