Hi,
In my Excel file one column containing values like this:
Gender
Male
Female,
Empty(NULL)
In my Sqlserver the lookup table look like this:
GenderID Desc
1 Male
2 Female
Now I am using Lookup transformation. If there is no match in Lookup table I want to put null for GenderId in the main table. How to do this? Using lookup can we do this?
Thanks
Hi,
I just wanted to know if there is any way to Allow Null values while doing a lookup on a table in SSIS.
Let me elaborate the situation...
I have a flat file source that has a field called 'code'. I want to lookup in a code table to see if the code in the file is a valid code but the flat file may contain a NULL value as a 'code' (i.e. zero length string which treated as NULL by my package).
My problem is, the SSIS package tries to search for the NULL in the table and the lookup fails and an error is logged as per the business logic but actually NULL is also an acceptable value and the error should not be logged.
I tried inserting a NULL value in the lookup column but that doesn't work. I am not sure but I think I have read somewhere that two null values cannot be compared for equality. I cannot use conditional split to check the null value because I have to use a large number of lookups and a conditional split everywhere will mess up the things.
Is there any way to solve this problem?
When i do a select on my emplee table for rows with null idCompany i dont get any records
I then try to modify the table to not allow a null idCompany and i get this error message:
'Employee (aMgmt)' table
- Unable to modify table.
Cannot insert the value NULL into column 'idCompany', table 'D2.aMgmt.Tmp_Employee'; column does not allow nulls. INSERT fails.
The statement has been terminated.
This sux
is there an elegant way to use one equals sign in a where clause that returns true when both arguments are null, and returns true when neither is null but both are equal and returns false when only one is null?
View 4 Replies View RelatedI have two SSIS packages that import from the same flat file into the same SQL 2005 table. I have one flat file connection (to a comma delimited file) and one OLE DB connection (to a SQL 2005 Database). Both packages use these same two Connection Managers. The SQL table allows NULL values for all fields. The flat file has "empty values" (i.e., ,"", ) for certain columns.
The first package uses the Data Flow Task with the "Keep nulls" property of the OLE DB Destination Editor unchecked. The columns in the source and destination are identically named thus the mapping is automatically assigned and is mapped based on ordinal position (which is equivalent to the mapping using Bulk Insert). When this task is executed no null values are inserted into the SQL table for the "empty values" from the flat file. Empty string values are inserted instead of NULL.
The second package uses the Bulk Insert Task with the "KeepNulls" property for the task (shown in the Properties pane when the task in selected in the Control Flow window) set to "False". When the task is executed NULL values are inserted into the SQL table for the "empty values" from the flat file.
So using the Data Flow Task " " (i.e., blank) is inserted. Using the Bulk Insert Task NULL is inserted (i.e., nothing is inserted, the field is skipped, the value for the record is omitted).
I want to have the exact same behavior on my data in the Bulk Insert Task as I do with the Data Flow Task.
Using the Bulk Insert Task, what must I do to have the Empty String values inserted into the SQL table where there is an "empty value" in the flat file? Why & how does this occur automatically in the Data Flow Task?
From a SQL Profile Trace comparison of the two methods I do not see where the syntax of the insert command nor the statements for the preceeding captured steps has dictated this change in the behavior of the inserted "" value for the recordset. Please help me understand what is going on here and how to accomplish this using the Bulk Insert Task.
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.
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.
Do NULLs take up space on the Database? I mean is room allocated for them?
View 9 Replies View RelatedCan someone tell me what to do if several NULLS are showing up within tables. Is there an easy step to locate all NULLS and delete them within the table?
Can someone provide detailed infromation on how to delete NULLS within tables. I'm new o the SQL side so I hope I have explained what it is that I'm looking to do. Our in house programmer said that the NULLS maybe causing a problem with the software communicating with the SQL Server software.
I have a page with 100 numeric fields. SQL insert crashes lest there is a number in every field. Any way around this?
View 1 Replies View RelatedHi
i have a table t1 and wish to have to fk's now i want to be able to set it up so that only one of them can be null example:
t1
id___________fk1________fk2
1____________6__________NULL
2____________NULL_______3
3____________7__________NULL
4____________NULL_______NULL <<<<<<< This should not be allowed
thanks
james
Hi i am new to SQL, and i have a data base thats got NULLS in it
is there a query i can run, which can take the NULL value out ?
anything will be greatly appreciated.
Hi,I don't have any specific details as I do not really understand whythis is happening but we have a customer database that we have beenusing and queries on text fields have begun returning empty stringvalues instead of NULL.So for example:SELECT TAB1.DESCRIPTIONFROM TABLE1 TAB1WHERE TAB1.DESCRIPTION IS NOT NULLORDER BY 1may have returned 50 records in the past (purely an example). It isnow returning an additional record first that appears just to be ''.Now I am not too sure where to begin looking into this. ODBCconnections, collation settings? I just am not sure where to begin.The column will not have had '' inserted into it; therefore it shouldbe NULL.I have posted this mainly so I can see if anybody else has seen thissort of behaviour - I cannot see why this has suddenly happened.The collation setting on our server is different to that of thecustomer DB, but this shouldn't make a difference should it?If anybody has any ideas then I can post some more information.Thanks,Paul
View 12 Replies View Relatedcreate table t1(c1 int, c2 varchar(10))insert t1 values(1,'Hello')insert t1 values(2,'')insert t1 values(3,NULL)select *from t1c1c21Hello23NULLselect *from t1where c2 = ' 'c1c22select *from t1where ltrim(rtrim(c2)) is nullc1c23NULLThe last query should have result as following. However sql server2000 does no list row c1 = 2.c1c223NULL
View 6 Replies View RelatedHiI am probably going to regret asking this because I'm sure you are going totell me my design is bad 8-) ah well we all have to learn....anywayI often use Nulls as a marker to see if certain tasks have been completed.A typical example would be a column say invoice_valuewhen new work is entered and a new record is appended, I leave theinvoice_value column as NULL, so if I want a View for uninvoiced work Icheck for the invoice_value being equal to NULL, and alternatively if I wantinvoiced work, I check for not null. I did it this way to save putting inanother column that needed to be set to TRUE or FALSE. (I do similar thingselsewhere aswell)so far everything seems to work OK but reading some old stuff on Google Iget the feeling that NULLS should be left alone, have I done wrong?many thanksAndy
View 3 Replies View RelatedHello all
I have a colunm - Datatype : nvarchar - Contains: Numeric, characters, strings, date and NULLS
I am getting a the value of each row of this colunm in a variable say @var1
Now I am trying to do some functionalities depending on this variable.
The requirement is if this variable is not null I need to do some stuff.
So I write simple query:
IF (@var1 IS NOT NULL)
BEGIN
......
END
when i am doing this I get an Error:
Error converting data type nvarchar to numeric.
realizing this I change the condition to
IF ( LEN (@Var1) > 1)
then
IF (LEN (@Var1) <> 0)
but no use. I get the same error.
I even use cast
I get the error
Conversion failed when converting the nvarchar value 'null' to data type int.
IF(CAST (@OldValue AS INT)IS NOT NULL)
OR
IF(LEN (CAST (@OldValue AS INT)) <> 0)
Can somebody help in guiding the best practice to do this?
Hi,This should be straight forward, but I've searched high and low on the net.I have a FORM which allows me to INSERT data, SQL Server 2005 backend. I populate the all the mandatory fields. But when I click on the Insert button, it won't let me save and says:Column 'PERSONAL_ID' does not allow nullsI'm
using tableadapters, business logic layers etc. and pausing clearly
shows that the values from the form are being passed to the procedure
that I have created "AddNewRecord". And PERSONAL_ID is definitely not
NULL! It fails on Line 971: Me.Rows.Add(row)PERSONAL_ID
is a primary key which I generate. The pause also shows that for
PERSONAL_ID it says " {"Conversion from type 'DBNull' to type 'String'
is not valid."}Function AddNewRecord looks like this:<System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Insert, True)> _
Public Function AddNewRecord(ByVal PERSONAL_ID As String, ByVal SURNAME
As String, ByVal CHRISTIAN_NAME As String, ByVal SEX As String, _
ByVal FAMILY_POSITION As String, ByVal FAMILY_ID As String, ByVal
ADDRESS_1 As String, ByVal ADDRESS_2 As String, _ ByVal ADDRESS_3
As String, ByVal ADDRESS_4 As String, ByVal ADDRESS_5 As String, ByVal
ADDRESS_6 As String, ByVal POSTCODE As String, ByVal COUNTRY As String,
ByVal ORG_ID As String) As Boolean ' create a new details row instance Dim details As New smDetails.smTbl_DetailsIDDataTable Dim detail As smDetails.smTbl_DetailsIDRow = details.NewsmTbl_DetailsIDRow details.AddsmTbl_DetailsIDRow(detail)...my formview code is this: <InsertItemTemplate> PERSONAL_ID: <asp:TextBox ID="PERSONAL_IDTextBox" runat="server" Text='<%# Bind("PERSONAL_ID") %>' AutoPostBack="True" /> <br /> SURNAME: <asp:TextBox ID="SURNAMETextBox" runat="server" Text='<%# Bind("SURNAME") %>' AutoPostBack="True" /> <br /> CHRISTIAN_NAME: <asp:TextBox ID="CHRISTIAN_NAMETextBox" runat="server" Text='<%# Bind("CHRISTIAN_NAME") %>' /> <br /> SEX: <asp:TextBox ID="SEXTextBox" runat="server" Text='<%# Bind("SEX") %>' /> <br /> FAMILY_POSITION: <asp:TextBox ID="FAMILY_POSITIONTextBox" runat="server" Text='<%# Bind("FAMILY_POSITION") %>' /> <br /> FAMILY_ID: <asp:TextBox ID="FAMILY_IDTextBox" runat="server" Text='<%# Bind("FAMILY_ID") %>' /> <br /> ADDRESS_1: <asp:TextBox ID="ADDRESS_1TextBox" runat="server" Text='<%# Bind("ADDRESS_1") %>' /> <br /> ADDRESS_2: <asp:TextBox ID="ADDRESS_2TextBox" runat="server" Text='<%# Bind("ADDRESS_2") %>' /> <br /> ADDRESS_3: <asp:TextBox ID="ADDRESS_3TextBox" runat="server" Text='<%# Bind("ADDRESS_3") %>' /> <br /> ADDRESS_4: <asp:TextBox ID="ADDRESS_4TextBox" runat="server" Text='<%# Bind("ADDRESS_4") %>' /> <br /> ADDRESS_5: <asp:TextBox ID="ADDRESS_5TextBox" runat="server" Text='<%# Bind("ADDRESS_5") %>' /> <br /> ADDRESS_6: <asp:TextBox ID="ADDRESS_6TextBox" runat="server" Text='<%# Bind("ADDRESS_6") %>' /> <br /> POSTCODE: <asp:TextBox ID="POSTCODETextBox" runat="server" Text='<%# Bind("POSTCODE") %>' /> <br /> COUNTRY: <asp:TextBox ID="COUNTRYTextBox" runat="server" Text='<%# Bind("COUNTRY") %>' /> <br /> ORG_ID: <asp:TextBox ID="ORG_IDTextBox" runat="server" Text='<%# Bind("ORG_ID") %>' /> <br /> <asp:LinkButton ID="InsertButton" runat="server" CausesValidation="True" CommandName="Insert" Text="Insert" /> <asp:LinkButton ID="InsertCancelButton" runat="server" CausesValidation="False" CommandName="Cancel" Text="Cancel" /> </InsertItemTemplate> Any pointers in the right direction would be appreciated. Thanks in advanceTushar
I have the following query:
SELECT tblArticleCategory.ACategoryID, IsNull(ParentCategory, '') + IsNull( ' > ' + ACategoryName, '') AS Category from tblArticlecategory
RIGHT OUTER JOIN (SELECT ACategoryName as ParentCategory, ACategoryID from tblArticleCategory WHERE AParentID is null AND aActive=1) AS Parent
on parent.ACategoryID = tblArticleCategory.AParentID
WHERE tblArticleCategory.AActive=1ORDER BY Category
This produces an ordered list of the parent categories and subcategories from the table as long as there is a subcateogry directly below one of the parent categories. If there isn't one directly below the parent category, the parent category is dropped from the list. None of the parents with the NULLS back to back are in the list.
6 Arts & Entertainment > Dance2 Arts & Entertainment > Movies13 Computers > E-Learning4 Computers > Hardware14 Computers > Java16 Computers > Link Popularity17 Computers > Microsoft.net15 Computers > RSS5 Computers > Software8 Real Estate > Finance
How do I fix the query so that Automotive, Business, Cancer and Communications are included in the list?
I have a table as part of a replicated database where i need to change the allow nulls value (set it to not allow nulls) but when i try to save the table i get this error.
'CONTACTS' table
- Unable to modify table.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot drop the table 'dbo.CONTACTS' because it is being used for replication.
will i have to stop replication to make the change or is it worse and i have to delete the publication and start all over again?
Hi All,
I have a table with 3 columns. Product, Location and Value. The data looks like this:
NULL NULL 100
Atlanta NULL 50
Atlanta Cookie1 30
Atlanta Cookie2 20
Dallas NULL 120
Dallas Cookie1 80
Dallas Cookie2 40
This table gets filled with a Groupby with Rollup option. The NULLS show subtotals/total. Is there a way to build a query that returns the results with NULLs at the bottom of each section like:
Atlanta Cookie1 30
Atlanta Cookie2 20
Atlanta NULL 50
Dallas Cookie1 80
Dallas Cookie2 40
Dallas NULL 120
NULL NULL 100
Thanks,
Shab
Hi,
I've forgotten how to put a null value in a column of a table where a previous value existed before.
What I mean is if through EM, you look at the data (Open Table -> Return all Rows) and you see a value in a column that is nullable (for example, I want to change a column that has a 'ABC Company' to a NULL value).
How do you that - i've done it in the past ,but cannot remember
Thanks in advance
We have compare two table with populated with the same data and with the same definition except, one table allows nulls
and the other table does not. All the fields are defined as integer. The table that allows nulls takes much more data space.
Why is that? I could not find an explaination in BOL.
I have the following query in a stored procedure. If there are no rows in the history file, it returns a null. If there some setting or function that would have it return a zero if no rows are found? I use the variable to do arithmetic later on and a null messes everything up.
select@MarketTotal = sum(isnull(MarketValue,0))
fromhistory
whereEmpUID = @EmpUID and
Shares > 0
Thanks,
Ken Nicholson
I'm using ADO.NET and have noticed that it is somewhat more difficult to send/receive nulls to/from the database. What are the benefits (if any exist) with storing a null value in the db as opposed to a default value?
In particular, do you save any space by storing a null opposed to a zero or 01/01/1900? Are the nulls faster for searching/comparing when querying the db?
Basically I'm just wondering if storing nulls is really the best practice and why, so that I know whether it's worth the effort in .NET to transform my variables into a null-happy form.
Any facts/opinions are appreciated,
Inge
Documentation states that you can set the Default Value for a column which is to be used whenever a row with a null value is inserted into the table. However, it appears that the column ends up as NULL regardless of this setting when inserted field has a value of NULL.
We have a stored procedure that accepts parameters for each column on the table and initializes the incoming parameter to NULL. It then inserts the row into the table. Table definition allows NULLs in the columns, but has a Default Value of ''.
Are we missing a setting somewhere?
Hi All,
I have a table with 3 columns. Product, Location and Value. The data looks like this:
NULL NULL 100
Atlanta NULL 50
Atlanta Cookie1 30
Atlanta Cookie2 20
Dallas NULL 120
Dallas Cookie1 80
Dallas Cookie2 40
This table gets filled with a Groupby with Rollup option. The NULLS show subtotals/total. Is there a way to build a query that returns the results with NULLs at the bottom of each section like:
Atlanta Cookie1 30
Atlanta Cookie2 20
Atlanta NULL 50
Dallas Cookie1 80
Dallas Cookie2 40
Dallas NULL 120
NULL NULL 100
Thanks,
Shab
Hi,
I'm not sure why this is happening, but I'm trying to sort/order on this special field/column that I have created to allow precise ordering. However, for all of the values that are returned, if there are any NULLS, they are returned first/ahead of the values of what I want to be returned first. This field/column I created is called 'POW' (stands for Priority on Web). The values are just integers, 1-whatever, and we try to make sure that they're all unique. So for example, if we have a 1 in that field, then that row should be returned first, and if another row has 2, then it should be next. However, this is not working exactly - all of the NULL values are returned FIRST, then the proper values in the correct order. I want the NULLS to be last, because it may take some time for someone to go into each record, and assign it a unique value. My current SQL statement reads:
mySQL = "SELECT SignTypes.Id, SignTypes.SignType, SignTypes.SignDescription, SignTypes.FinalSignWidth, SignTypes.FinalSignHeight FROM SignTypes JOIN PropLocSigns ON SignTypes.Id = PropLocSigns.SignTypeID WHERE PropLocSigns.PropLocID = " & Session("PropID") & " AND (RestrictOnWeb = 0 OR RestrictOnWeb IS NULL) ORDER BY SignTypes.POW; SELECT @@ROWCOUNT"
So if someone knows of a way to put NULLS last, and still have my proper ordering, that would be great! I would think that there's some simple way of doing this, but not being an expert, I don't know. Thanks for the help!
I have several tables anywhere from 50,000 to 100,000 rows with about 100 columns in each that allow nulls. How bad will this affect performance (if any)? If it's significant, how can I replace nulls with zeros without doing it column by column? (100 columns will take too much time) thanks
View 1 Replies View Related