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.
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.
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?
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]
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')
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?
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:
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 ...
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 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)
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)
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.
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..
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?
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?
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.
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)
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
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.
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!
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?
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*).
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?
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
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
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.
