XL Source Editor Excluding Numeric Records
Dec 11, 2007
Hi All,
I have a XL source file which contains the 1st column value is some of Numeric and alpha numeric (ex. 1,1A,1B,2,2A,2B),
i have fired the select statement (select * from [sch 1 a$A9155] where f1 is not null ) in XL source editor, its not showing the numeric records means 1 and 2 were excluding from the above select statement i think it was due to combining numeric and alpha numeric.
I need both records should select , please anyone can help me to sort out the issue.
Thanks in advance
Thanks,
syed
View 6 Replies
ADVERTISEMENT
Sep 13, 2001
hi I have a table which contain an id field as a char(20)
The content of this field is combination of string and numbers as follow
id flag
--------- -----
38383
88585
18834
x4820
z4892
t9494
I need to update the flag field where first character in the id field is not numeric. HOw can I do that.
thanks for your hlep
Thanks
AL
View 2 Replies
View Related
Apr 24, 2008
Hello,
I'm trying to figure out the best way to approach this seemingly simple task in Reporting Services 2005, but have come up with nothing but frustration. Any help would be appreciated!
Here's the basic idea:
SELECT Server.Database1.Employee, Server.Database1.HomeDept, Server.Database2.WorkedDept
FROM Server.Database1, Server.Database2
I need to exclude certain combinations of HomeDept/WorkedDept (about 11)...such as 1015/2223, 1015/2226, 2002/4422, 2002/8742, 2002/2342, etc. I want any other combinations of HomeDept 1015 & 2002 to be selected (which would be hundreds).
I've tried:
WHERE (Server.Database1.HomeDept <> '1015' AND Server.Database2.WorkedDept NOT IN ('2223','2226')) AND Server.Database1.HomeDept <> '2002' AND Server.Database2.WorkedDept NOT IN ('4422','8742','2342'))
(and all combinations of NOT IN, <>, and parenthesis placement...I even tried to do each exception separately)
What happens is that all instances of HomeDept 1015 and 2002 and all instances of WorkedDept 2223, 2226, 4422, 8742, and 2342 are excluded.
The SQL Query Designer will remove parenthesis so that the HomeDept exclusion is processed separately from the WorkedDept.
I've tried to do the SQL in the Generic Query Designer, so the SQL Query Designer doesn't reformat it, and it still returns the same results.
This behavior only seems to happen when using <> for both, NOT IN for both, or a combo of NOT and <>. If I used = for the HomeDept, the exclusion of the WorkedDept worked fine.
Any suggestions?
View 1 Replies
View Related
Apr 18, 2008
and generating a report from an SQL table, and need to know how to exclude records that are "duplicates". Not duplicates in a sense that every field is identical, but duplicates in a sense where everything except the unique identifier is identical. Is there a quick and easy way to do this?
View 5 Replies
View Related
Dec 20, 2006
hi,
I am using SSIS to extract data from sql server and import into MDB file. In
the process, under data flow task, I have used OLE DB Source Editor as source. Here
i have choosen SQL Command as mode of data population. In the box below i
have typed the following statements.
"Exec Site_Address"
I have used many temperory tables in this procedure.
When i run this procedure in the query analyzer window i get the desired data which has to be imported to an MDB. After typing the above statements and when i
click the button preview i can see the data. But when i click the
Columns.... i dont see anything there. I am unable to see any columns there.
This is getting to my nerves because, when i use OLE DB as Destination i am
unable to map the columns and i get an error.
I dont know how to solve this problem. cannot we map columns in temp tables .... or wat is it ??
Please help me to find a solution.
I will also paste the procedure code that i have used.
Create procedure Site_Address
as
begin
create table #Data_For_Site_Address_Table
(
unitid varchar(20),
city varchar(50),
cust_num varchar(40),
zip varchar(20),
CountryID varchar(20),
CreatedBy varchar(20)
)
-- tblcrdsiteaddress
insert into #Data_For_Site_Address_Table
select distinct * from
(select
(select top 1 fsu.ser_num
from fs_unit fsu
where ca.cust_seq <> 0 and fsu.cust_num = ca.cust_num
order by ca.city desc) as UnitID,ca.city,ca.cust_num,ca.zip,
CASE
WHEN ca.country like 'Luxembourg' THEN 'LU'
WHEN ca.country like 'Deutschland' THEN 'DE'
WHEN ca.country like 'Austria' THEN 'AT'
WHEN ca.country like 'Czech Republic' THEN 'CZ'
WHEN ca.country like 'Denmark' THEN 'DK'
WHEN ca.country like 'CHINA' THEN 'CN'
WHEN ca.country like 'CROATIA' THEN 'HR'
WHEN ca.country like 'Egypt' THEN 'EG'
WHEN ca.country like 'Germany' THEN 'DE'
WHEN ca.country like 'Hungary' THEN 'HU'
WHEN ca.country like 'Jordan' THEN 'JO'
WHEN ca.country like 'Korea, Republic Of' THEN 'KR'
WHEN ca.country like 'Poland' THEN 'PL'
WHEN ca.country like 'Switzerland' THEN 'CH'
WHEN ca.country like 'United Kingdom' THEN 'GB'
ELSE '- N/A -' END AS CountryID, CA.CreatedBy
from custaddr ca
) al
where unitid is not null
Select TT.Unitid as Short_Site_Name, TT.City as Site_Name,'N.A' as Street_Po_Box,TT.Zip as Postal_Code_City, Null as State_Region,
TT.CountryID as CountryID,Null as Zone, Null as Note, TT.CreatedBy as UserID, GetDate() as Date, 'A' as [Action]
From #Data_For_Site_Address_Table TT
END
Thanks.
Rgds,
Meher Krishna.V
View 9 Replies
View Related
Aug 31, 2015
Using MSSQL 2012
I have a simple select query and I need to eliminate records whose values from 2 different fields match. I thought I had this working, but if one of those fields in my data IS NULL it filters out those records. If I comment out my last line then my number record shows, if I include that statement that record drops. The only thing I see in my data is the Name and PName are both NULL in the data for that particular number. Just need to filter out any records where it finds those 3 Names that also have "Default" as the PName, then include everything else even if Name or Pname is NULL.
Below is my where clause.
WHERE [DETERMINATION] <> 'Denied'
AND [Number] ='A150731000039'
---- Removes incorrect records where these names match----
AND ([Name] NOT IN ('GLASSMAN','NANCY','LUDEMANN') AND [PName] = 'DEFAULT')
View 4 Replies
View Related
Apr 23, 2008
I have query like below that I am using as a OLE DB source
Set NOCOUNT ON
Select *
Into #temp1
from A
Select *
Into #temp2
From B
Select * from #temp1 a
Join #temp2 b on a.episode_key = b.episode_key
I can see the preview data , but when I click columns, there are no available external columns..
Howcan I fix this issue?
View 8 Replies
View Related
Dec 7, 2006
I am working on a situation similar to 'Get all from Table A that isn't in Table B' http://www.sqlis.com/default.aspx?311
I noticed that if one column's name of source table changes,(say Year to Year2) I have to modify all 'data flow transformations' in the task.
I am new to SSIS.
thanks! -ZZ
View 8 Replies
View Related
Sep 2, 2006
i have a data flow configured to use a ole db source. the ole db source uses an ole db connection manager pointing to the adventureworks db which suceeded when i tested its connection. the data access mode of the ole db source is "sql command". below is the sql command text:
SELECT SpecialOfferID, Description
DiscountPct, Type, Category, StartDate,
EndDate, MinQty, MaxQty, ModifiedDate
FROM Sales.SpecialOffer
WHERE ModifiedDate >= ? AND ModifiedDate < ?
this query uses 2 paramaters, each of which is mapped to a datetime variable which falls with the range of the ModifiedDate column as follows:
Parameter0: User::ExtractStartDate
Parameter1: User::ExtractStopDate
ExtractStartDate is set to 7/1/2001 and ExtractStopDate is set to 3/31/2004. however, i get the following error when i press the preview button in the ole db source editor: "there was an error displaying the preview. additional information: no value given for one or more required parameters (microsoft sql native client)".
as far as i can tell, i have the ole db source configured correctly. thus, i can't figure out why this error is being generated. has anyone else experienced this issue? if so, were you able to resolve it? is this a bug?
thanks in advance.
View 5 Replies
View Related
May 11, 2007
I want to import data from a remote MySql Database
So, I created an New Connection (ADO.Net), connection Test is successful but when I try to set the sql string in the advanced editor i get the following error:
Error at Data Flow Task [DataReader Source [2182]]: System.Data.Odbc.OdbcExeption: ERROR [HY010][MySQL][ODBC 3.51 Driver][mysqld-4.1.10a]
ERROR [HY010][MySQL][ODBC 3.51 Driver][mysqld-4.1.10a]
ERROR [HY010][MySQL][ODBC 3.51 Driver][mysqld-4.1.10a]
ERROR [HY010][MySQL][ODBC 3.51 Driver][mysqld-4.1.10a]
......
bei System.Data.Odbc.OdbcDataReader.NextResult(Boolean.disposing, Boolean allresults)
bei System.Data.Odbc.OdbcDataReader.Close(Boolean Disposing)
bei System.Data.Odbc.OdbcDataReader.Close()
bei Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.ReinitializeMetaData()
bei Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.ReinitializeMetaData(IDTSManagedComponentWrapper90 wrapper)
the sql string is very simple, this should not be the problem:
SELECT Objektnr FROM m02a_tblObjektstamm
Thank you very much for your support
Martin
View 8 Replies
View Related
Mar 23, 2006
Can i extend the "Query Builder" dialog of OLEDB Source Editor for developing custom source component?
View 1 Replies
View Related
Sep 29, 2012
Is there a way to disable to automatic renaming of source tables by the View editor? (SLQ Server 2008 R2)
I have a view that is using several sub-queries (including a "rank/partition") and even though each sub-query is contained in ( ) and given an alias, the view designer automatically adds the _1, _2 to all of the tables it thinks are duplicates, which then invalidates the and explicit field calls in CASE WHEN statements ...
View 1 Replies
View Related
Oct 25, 2007
Greetings everyone,
I am seeing a particular problem in the XML Source Editor "Columns" configuration where it is not persisting the "Output name" selection.
Control Flow Tab:
1. I use a "Exec SQL Command" to drop, create, or alter the destination tables in the database that I want to be repository for the inbound XML data. The data types are fairly straightforward.
2. I add a singular "Data Flow"
Data Flow Tab:
1. I add a "XML Source" task, and assign a well-defined XML file. I then use the "Generate XSD" option in the "Connection manager"; and I am fairly satisfied with the generated XSD.
2. I create "OLE DB Destination"
3. I wire the "XML Source" to the "OLE DB Destination". In the "XML Source" in the "Columns".
4. I go to the dropdown list of "Output name" and see the list ordered with the various complex-types that I want to map and transfer to a target table.
For the sake of this report, I select the 5th one down on the list (for which I already have a target table) - let's call this "Mesh"
5. In the "Input Output" dialog, I select the "output" to be the desired 5th item, "Mesh"
6. I check all my mappings so that they map one-to-one ... XML name entries match SQL table destination mapping entries; correct types; correct size
7. Check the metadata and it all looks good.
8. When I hit "Debug" to test the package the failure occurs at the "XML Source". The error report comes back saying that it failed because "field xxx in Contributor was truncated". However, "Contributor" corresponds to the 1st name in the dropdown list presented in "Columns" "Output name:".
If I select return to Step 4, when I open up "Columns" I see that my previous selection of the 5th item on the list named "Mesh" was not persisted, but invariably and no matter how often I select item #5 "Mesh" and save to ensure that selection sticks, it is not persisted.
I hand-edited the .dtsx file and only then was I able to make this stick. However, if I ever re-save the package this non-persistency pops up again.
Am I doing something wrong here or is this a known defect? As I have several dozen XSD mappings that I want to transfer to tables, hand-editing is not something I relish.
I look forward to your reply.
RudyC
View 1 Replies
View Related
Sep 8, 2009
I have a Connection manager to an Excel workbook. I've set it up as Microsoft.Jet.OLEDB.4.0 Excel 8.0;HDR=NoWhen I create the DataFlow OLE DB source, the Name of Table or View dropdown shows only 3 items:
Resources$ Resources$_ and Volumes$_
My workbook has 2 tabs (Resources and Volumes; Volumes is hidden), two named ranges of the same name (Resources and Volumes) and a bunch of other named ranges.
Why does the drop down only show these three things, and only the first one (the first tab Resources$) actually works.
The other two give a nasty message (0xC02020E8 Microsoft.SqlServer.DTSPipelineWrap)
Why can't I see my named ranges?New user to 2005
View 7 Replies
View Related
Feb 28, 2007
Please... any ideas? Is this a footprint config issue?
TITLE: Microsoft Visual Studio
There was an error displaying the preview.
ADDITIONAL INFORMATION:
Could not load file or assembly
'Microsoft.SqlServer.SQLTaskConnectionsWrap, Version=9.3.242.0,
Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its
dependencies. The system cannot find the file specified.
(Microsoft.DataTransformationServices.Design)
BUTTONS:
OK
===================================
There was an error displaying the preview. (Microsoft Visual Studio)
===================================
Could not load file or assembly
'Microsoft.SqlServer.SQLTaskConnectionsWrap, Version=9.3.242.0,
Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its
dependencies. The system cannot find the file specified.
(Microsoft.DataTransformationServices.Design)
------------------------------
Program Location:
at
Microsoft.DataTransformationServices.Design.PipelineUtils.ShowDataPreview(String
sqlStatement, ConnectionManager connectionManager, Control
parentWindow, IServiceProvider serviceProvider,
IDTSExternalMetadataColumnCollection90 externalColumns)
at
Microsoft.DataTransformationServices.DataFlowUI.DataFlowConnectionPage.previewButton_Click(Object
sender, EventArgs e)
View 1 Replies
View Related
Nov 10, 2010
I have a package which has an Excel source with the 'Data access mode' set to SQL command and then a sql select statement. When I try and hit the 'Preview...' button below the 'SQL command text' window I get the following error:
"Error at Standard Data Flow Tasks [source tasks name]: No column information was returned by the SQL command"
Ordinarily this would be down to the fact that my SQL is shocking, I hit the 'Preview...' button whilst the workbook the source is pointing at was open and it works fine??
I can't figure this out, but needless to say the package errors with a NEEDSNEWMETADATA when I try and run it.
View 17 Replies
View Related
Dec 13, 2007
Hi All,
Xl file containts column A, B, C and D
A B C D
1 L1 100 200
1A L2 250 350
1B L3 150 887
2 L4 100 200
2A L5 250 350
3 L6 100 200
3A L7 250 350
3B L8 150 887
XL Source i have write a query Select f1,f2,f3,f4 from [sheet1$a18]
In preview i am getting the following records :
A B C D
Null L1 100 200
1A L2 250 350
1B L3 150 887
Null L4 100 200
2A L5 250 350
Null L6 100 200
3A L7 250 350
3B L8 150 887
The issue is here why the XL source returning Null value against the numeric value pls. somebody help me to sort out this issue.
Thanks in advance,
Syed
View 1 Replies
View Related
Oct 26, 2006
i've been reading some problems with excel source data being force as
numeric type when there are string/numeric type in the data, but adding
IMEX=1 to the extended properties will fix this problem...this is true
but not in my case...
say my excel file have about 40 rows..if row 1-39 in column A are all
NULL and row 40 has a string in it, the string in row 40 will not be
converted and the excel source is forcing this column A data type to be
numeric..having IMEX=1 in there does not work..however..if i add a
string anywhere in row 1-8 in column A, the the string in row 40 will
be converted because the external data type now is a string..
anyone know how to solve this problem?
View 3 Replies
View Related
May 8, 2009
I'm using SSIS 2005 Enterprise edition, I'm creating a package that reads an excel (xls) file using the "excel source" component, and it dumps the data into an OLEDB destination (a sql server). When I drag the excel source component and create the excel connection to my file the component automatically reads the columns and their datatypes.
The problem is that I have a column which has numeric data and the package uploads as NULL every number that starts with a zero. (note: in excel this column is formatted as "text", despite it has only numbers, because it's the only way excel maintains the left sided zeros).
So I checked the data types by right clicking the excel source component -> show advanced editor and my surprise is that this column's data type is detected as double-precision float, and it doesn't let me change it. URL... but it only works when the first row of data has a number beginning with zero on this column. How to get the data imported correctly?
View 15 Replies
View Related
Apr 2, 2007
Hi,
Let's say I have 1000 registered users in database table and each of them has numeric ranking value.
How can I get the position of each user in comparison to other users ranking value?
View 6 Replies
View Related
Jun 10, 2014
when I run below query I got Error of Arithmetic overflow error converting numeric to data type numeric
declare @a numeric(16,4)
set @a=99362600999900.0000
The 99362600999900 value before numeric is 14 and variable that i declared is of 16 length. Then why this error is coming ? When I set Length 18 then error removed.
View 2 Replies
View Related
Mar 21, 2006
Guys
I'm getting the above when trying to populate a variable. The values in question are :
@N = 21
@SumXY = -1303765191530058.2251000000
@SumXSumY = -5338556963168643.7875000000
When I run, SELECT (@N * @SumXY) - (@SumXSumY * @SumXSumY) in QA I get the result OK which is -28500190448996439680147097583285.072256 ie 32 places to left of decimal and 6 to the right
When I try the following ie to populate a variable with that value I get the error -
SELECT R2Top = (@N * @SumXY) - (@SumXSumY * @SumXSumY)@R2Top is NUMERIC (38, 10)
Any ideas ??
View 6 Replies
View Related
Sep 19, 2006
The problem started off as:
I have a webservice written in VB.NET that simply returns a System.Data.Dataset. I want to use the dataset in SSIS.
So i decided to do this:
Save the returned dataset returned by the WS to an XML file. so it is sitting there, and looks like a fully valid XML file
<?xml version="1.0" encoding="utf-16"?>
<DataSet>
<xs:schema id="NewDataSet" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
<xs:element name="NewDataSet" msdata:IsDataSet="true" msdata:UseCurrentLocale="true">
<xs:complexType>
<xs:choice minOccurs="0" maxOccurs="unbounded">
<xs:element name="Table">
<xs:complexType>
<xs:sequence>
<xs:element name="Name" type="xs:string" minOccurs="0" />
<xs:element name="SourceRecord" type="xs:string" minOccurs="0" />
<xs:element name="CombinedTitle" type="xs:string" minOccurs="0" />
......
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:choice>
</xs:complexType>
</xs:element>
</xs:schema>
<diffgr:diffgram xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1">
<NewDataSet>
<Table diffgr:id="Table1" msdata:rowOrder="0">
<Name>Member</Name>
<SourceRecord>99998</SourceRecord>
<CombinedTitle>Mr</CombinedTitle>
<CombinedFirst>Sample</CombinedFirst>
........
</Table>
</NewDataSet>
</diffgr:diffgram>
</DataSet>
I then use the XML source connection to connect to it. It sees all of the columns correctly, but when i run and put a watch on it, or try to output the results to a .csv file, no records come through.
Any ideas on why there aren't any rows comming through?
i'm using SQL2005 with no SP1
View 10 Replies
View Related
Oct 24, 2007
Hi,
I have one column in which i have Alpha-numeric data like
COLUMN X
-----------------------
+91 (876) 098 6789
1-567-987-7655
.
.
.
.
so on.
I want to remove Non-numeric characters from above (space,'(',')',+,........)
i want to write something generic (suppose some function to which i pass the column)
thanks in advance,
Mandip
View 18 Replies
View Related
Jul 20, 2006
I need to replace Access Val() functions with similiar function in sql.
i.e. Return 123 from the statement: SELECT functionname(123mls)
Return 4.56 from the satement: SELECT functionname(4.56tonnes)
Any one with ideas please
Thanks
George
View 1 Replies
View Related
Apr 12, 2007
I have a simple data flow where I am trying to import data from an xml file into a SQL Server table. I have an xsd that seems to work because the XML Source can pick up all the elements in the xml file. The problem is that the process executes successfully without any rows being imported. The xml file has lots of data. But no messages or warnings are given to suggest why no rows are being written to the table.
View 21 Replies
View Related
Oct 18, 2007
All,
I am having a brain-fart or something. I need help!!!
I'm developing a transform that selects data from a SQL script, then transforms that data, and then needs to update the data back into the same source table. The transform is working great, but I can't figure out the update table part. I've got it adding the transformed records to the table, but that's not what I need to do. I need to update the table with the changed data.
Any sample code or blog links would be greatly appreciated!
Thanks for the help!
-Scott
View 6 Replies
View Related
Sep 10, 2006
I have created a data warehouse that pulls information from an ODBC source into a SQL database. The schema in the destination matches the source, and the packages clear the destination tables, then append all the records from the source. This is simpler than updating, appending new, and deleting on each table to get them in sync since there is no modify timestamp in the source.
There are cases where I just want to append records from the source table that do not already exist in the destination table, without clearing the destination table first.
How can this be done with a SSIS job? Also, how can the job be run from a Windows Forms application?
View 4 Replies
View Related
Jan 29, 2008
Hi,
I have an SSIS package that runs each day from a live data source to create a data mart, which is then used for various things including SSAS and SSRS.
The problem is that certain records that will eventually go on to form fact tables are deleted from the live system (not a very robost database in the first place, hence the SSIS!) but these are not reflected in the SSIS transformation, creating plus figures when compared to the live system.
I currently use type 1 slowly changing dimension processes in each data flow (of which there are about 35) but I realise that this only updates records and does not delete.
The solution I have in place is to truncate the fact tables in the mart before the run starts using an Execute SQL task. This solves the problem though to me seems a little heavy-handed and renders the slowly changing dimension processes redundant (as it is currently only run once a day).
My question is, is there a better method of dealing with the above scenario? If there isn't, it would be a nice feature to add to future versions (*nudge nudge*).
Thanks in advance :-)
View 1 Replies
View Related
May 14, 2008
Hi
I am using a XML source and an oldedb destination to load an XML file into a database, the XML and corresponding XSD are provided by a different system and I dont have any control on them.
In the data flow I can see all the tables in the schema and I can map the columns, however when I run the package it validates fine, and then completes without going into the execution mode, its all green but doesnt load any data. Its weird and I cannot figure out if it is an XML or XSD problem, any idea?
Thanks
View 5 Replies
View Related
Aug 27, 2007
Hi, I am copying records in a table. The source table and the target table are the same. I need the value from the id-field from both the source and target row. Is there a way to do this with one query?
I tried the following, but it doesn't seem to work:
INSERT tableOne (value1, value2, value3)
OUTPUT source.id, inserted.id
SELECT value1, value2, value3 FROM tableOne AS source
WHERE ID = @number
View 2 Replies
View Related
Jul 20, 2005
Hi,I used sp_addlinkedserver to link to a remote server through ODBC.When I execute select count(*) from LinkSrv.SI.DBO.SIHeader in SQL QueryAnalyzer. It returns 13705 records. But when I execute select * fromLinkSrv.SI.DBO.SIHeader. It only return 885 records. If I specify somecolumns, select ODCOMP, ODPONO, ODVDCD from LinkSrv.SI.DBO.SIHeader.It returns more records, 1213 records.I guess there is something limit the return storage, but I can notfind it.Any suggestion will be appreciated. Thank you
View 3 Replies
View Related
Jul 20, 2006
I have the Excel Connection Manager and Source to read the contents from an Excel file. For some reason couple of numeric fields from the Excel worksheet are brought over as nulls even though they have a value of 300 and 150. I am not sure why this is happening. I looked into the format of the fields and they are set to General in Excel, I tried setting them to numeric and that did not help.
All the other content from the excel file is coming thru except for the 2 numeric fields.
I tried to bring the contents from the excel source to a text file in csv format and for some reason the 2 numeric fields came out as blank.
Any inputs on getting this addressed will be much appreciated.
Thanks,
Manisha
View 5 Replies
View Related
