HowTo Compare Data From 2 Different Db-sources (xls,csv,sql Server, Mysql Server)?
Jan 28, 2008
Hello Guys,
I'm searching for a way to compare informations from one database against another database.
E.g. i want to check if serialnumbers of my first database (eg hardware.xls or hardware.csv ) are already stored in my sql server database.
I know i can use sql querys (Joins) for that task but i don't know how i can access 2 different databases at the same time. Do i need a tool for it? Does Excel is able to compare those data? But how? I'm searching for that thread since 2 days testing with some tools without any success...
I have to get all the customer records from one table and compare this records with another table and finally if there is same record I have to perform an update but the record does not exist I have to make an insert. How can I do it with SSIS Except writing sql or tsql scripts ?
An SSIS package to transfer data from a DB instance on SQL Server 2005 to SQL Server 2000 is extremely slow. The package uses an OLEDB Source to OLEDB Destination for data transfer which is basically one table from sql server 2005 to sql server 2000. The job takes 5 minutes to transfer about 400 rows at night when there is very little activity on the server. During the day the job almost always times out.
On SQL Server 200 instances the job ran in minutes in the old 2000 package.
Is there an alternative to this. Tranfer Objects task does not work as there is apparently a defect according to Microsoft. Please let me know if there is any other option other than using a Execute 2000 package task or using an ActiveX Script to read records from one source and to insert them into the destination source, which I am not certain how long it might take and how viable will that be?
I need to periodically import a (HUGE) table of data from an external data source (not SQL Server) into SQL Server, with the following scenarios: Some of the records in the external data source may not exist in SQL.Some of the records in the external data source may have a different value at different imports, but this records are identified univocally by the same primary key in the external datasource and in SQL Server.Some of the records in the external data source may be the same in SQL.
Due to the massive volume of the import, I would like to import only the records which are different from what I have in SQL Server (cases 1 and 2 above). In fact case 2 is the most critical.
I thought of making a query with a left outer join between the data in the external data source table (SOURCE) and the data in the SQL Server table (DESTIN). The join is done on the respective primary keys (composed keys of up to 10 columns) and one of the WHERE conditions will be that the value in SOURCE is different from the value in DESTIN.
The result of this query would be exactly what I need to import. How to do this in SSIS??? I couldn't figure out how to join tables in different data sources yet.
In fact I cannot write a stored procedure to do that, since one of the sources is in a datasources not SQL Server. I have seen the Lookup transformation in this article http://www.sqlis.com/default.aspx?311 but this is not exacltly what I want to do. Another possibility is to use the merge join, but due to the sorting I believe its performances would be terrible!
I've encountered a few problems using SSIS against non-SQL Server data sources and was hoping that others might have some experience. Google searches and browsing MSDN hasn't led me to a solution, so any advice on the following is appreciated:
1) When using the "Data Source Views" wizard to add a data source view from an ODBC data source, only tables appear in the object listing. Views do not. (I've also observed this with SQL Server 2005 databases as well, but it's a bigger issue when you can't use the native SqlClient, as is the case for many ODBC-only databases.) ODBC traces show that both table and view metadata is being returned to SSIS correctly, so it appears as though SSIS is filtering out views from the object listing in the wizard.
2) When creating named queries against (non-SQL Server) ODBC data sources, SSIS appears to use the SQL Server SQL syntax for referencing schemas/objects (e.g. "SELECT * FROM [schema].[table]", rather than "SELECT * FROM schema.table"). This isn't valid SQL in many databases. Am I missing something? Is there a way to change this through some configuration setting?
I am new to SSIS. I would like to know if I want to transfer data from one Oracle schema to another Oracle schema and also to do scheduling of the packages, can I still use SSIS? If yes, what are the components that need to be installed on the database server and the development environment? I hope I don't need the full SQL Server database installation in order to use SSIS.
I have a project where I am creating a test environment. My objective is to give the client staff a set of instructions to follow to make sure the test mirrors production. I have migrated the SRS databases over to the new test server. All of the reports are there as is the security but where I run into an issue is the data sources for SRS. They are all pointed at the production servers. Is there an easy way to make the change possibly using a SQL update statement or exporting all the reports to XML and doing a find and replace. The shared data sources I went through manually and changed but there were 40 data sources. Now I realized some of the reports have embedded data connections. There are hundreds of reports to go through.
I am wanting to change from using a query to move this data to using an SSIS Data Flow. I am familiar with using Merge Join to combine the two tables (H & S in this case), but I'm not sure where I can use the ISNULL in the manner described above. Is there a way to do it in the Merge Join? Do I have to do it after the Merge Join?
1. Data Warehouse 2. OLAP CUBE in Analysis Services
My question is - If my Data Warehouse is changed (Having Append Data) - My OLAP Cube will have the Append Data?
It's possible, my OLAP Cube always having Append Data if my Data Warehouse is changed? If yes, how to do it without re-deploy and re-process my Analysis Services Project....
Hi My project is in .NET 2003 i.e. framework 1.1 and database in SQLServer 2000. But the reports have been developed using SQLServer 2005 Reporting Services. Now when I am trying to deploy them through deployment project of .NET its giving me following error:"Using other editions of SQL Server for report data sources and/or the report server database" is not supported in this edition of Reporting Services. Now I am really confused with this. Can any one please guide me regarding this ASAP. Thanks, Falguni
I apologize for not doing the legwork to see if i can answer my own question, but I am close to a loosely planned SQL 2005 migration and don't have time and resources to test my own theories.
Is there a way for a Reporting Services 2000 server to connect to SQL server 2005 databases? I've tried creating a new data source and changing a report to this data source, but it seems like the report is still using the old data source. I'm guessing i might need to register new data providers on the 2000 RS server and then change the existsing data sources.
Can anyone shed light on why I cannot get away from named pipes in my SQL2005 Reporting Services Data Sources? Can it not just use TCP/IP? My configuration uses two servers, one in which the reportserver is setup on, and another which hosts the database that is reported against. We are trying to avoid having to open up named pipes on the server that stores the data.
Any help would be appreciated.
An error has occurred during report processing.
Cannot create a connection to data source 'wc_datasource'.
an error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
Hi All, We will replace our website which currently build in php and mysql into .net and sql server 2005. What is the best way to migrate all the user account from current database in mysql into sql server, also considering that there is different database table and fields? Thanks in advance.
problem is I don't have unfettered access to the MS SQL Server(7), so I need to send instructions on how to do it to a client.
After reading up a little, I figured "select view into ##tmp_table", and then dump the table, which, if I'm right, means I'll have an SQL dump that I can quite easily import into MySQL.
Problem,: What's the syntax to dump that temp table, or is there a better way to import the data into MySQL
I'm trying to import data from mySQL database to SQL server 2005. I installed mySQL odbc driver (3.51) and configured system DSN. However when I start SQL server import/export wizard mysql is not an option in data source dropdown. What do I miss?
Hello group,i am relatively new to SQL-Server database, but i have lots ofexperience with DB2 and Oracle Database. One of my tasks is setting upa replication between a Mysql-Database running on Linux and one of ourSQL-Servers.How do i achieve this ?If i understand the documentation correctly you have to program thereplication mechanism for yourself or you have to use some third partytool.Could anyone please outline, how to set up the replication mechanism(pointing me to some web-site should be enough) and also tell me ifthere is any third party tool.Thanks in advance and greetings from ViennaUli
Has anyone successfully used cherry's oledb provider for MYSQL to create a linked server from MS SQLserver 2005 to a Linux red hat platform running MYSQL.
I can not get it to work.
I've created a UDL which tests fine. it looks like this
[oledb]
; Everything after this line is an OLE DB initstring
Hello, Finally making the move to ASP.NET! I've been advised that to begin with it is probably best to start using SQL Server 2005 with ASP.NET. All my sites are currently using MySQL. Can anyone advise a way for me to import all the data from a MySQL database to a SQL Server 2005 database. Apologies if this isn't directly related to ASP.NET but any help would be greatly appreciated. Thanks
Problem: Moving data from mysql to sql server 2005
I am trying to pull data over from mysql to sql server. First the import wizard greys out so I have to put in 1 query at a time which is pain. and second it does not even work! it takes me through the end of the wizard for me to click finish and then says oops it does not work. there was an error!
Anyway i tried going through the ssis route cuz its going to be a nightly job. i used the ado.net odbc connection. It worked but the performance is really not acceptable. it took 5 mins to import 24000 rows where as dts was taking 1 sec to do this. i wish i could use the native mysql odbc 3.51 connector and import. can some one give me step by step instructions on how to do that ?
I hear someone mentioned of using excute sql task which can use mysql odbc 3.51 driver. but since i am new how do i get it to work. say for example in the excute sql task i run a statement like select * from addr. then what?
cuz eventually i want the result to be saved in a sql server table called addr. How can i get the result from that excute sql task and put it inside of an addr table in sql server. should i save the result to a variable of type object. but then how do i get the data from object and tell sql server in the designer that the result contains these columns and it needs to map to these columns in the addr table of sql server.
Very confused. i wish the first option would have given me results which an enterprise ETL gives. but apparently it is too slow that it wont be acceptable in a production envrioment. when i will have millions of rows coming in .
hey all, i want to know Equivalent of Load Data Infile of MySQL in SQL Server 2005? in mysql we can direclty load file with Load Data infile query. my question is how to do this in sql server 2005? thanks.
HI All, I want to pull data from mysql dbms to sql servr 2005, i have wrote the following codes but it takes more than an hour and half which is not visible. Thus is there any consideration to consider and reduced time it takes. For your information i am going to use SSIS packags, there is no any tranformation, it is direct dump. Here is the code i am using,
SELECT * FROM OPENQUERY (Server_1,'
SELECT t3.Column11 as Column1, Column12 as Column2, Column13 as Column3, Column14 as Column4, Column15 as Column5, Column16 as Column6, Column17 as Column7, Column18/1000 as Column8 FROM table1 t1 INNER JOIN table2 t2 ON t1.ColumnId = t2.columnID INNER JOIN Table3 t3 ON t2.columnId = t3.columnID WHERE t1.Column4 > Sometime ')
I am trying to compare the data from 2 different servers. It is the same table. I did a select * and put the data into 2 different text files. I then used the Windows NT findstr utility to compare them to find the differences. I used the command findstr /v /i /x /g:file1 file2 > file3, where file1 is the larger table, and file2 is the smaller one. I want to find the records in file2 which are different or do not exist. I recieved no data back. I have ASCII characters in the text files. My question is does anyone know of a better way to do this, or how to make this command work?
Is there a efficient way to compare two different columns of 2 different rows in a data set as shown below.
For eg: I would like to DateDiff between Date2 of RowID 1 and Date1 of RowID 2 of IDNo 123. After this comparision , if datediff between two dates are <=14 then i want to update 1 else 0 in IsDateDiffLess14 of RowID1 . In below example its 0 because datediff of two dates >=14. So, want to compare the Date2 and Date1 in this sequence for the same IDNo. For RowID 6 there is only 1 row and no other row to compare, in this case IsDateDiffLess14 should be updated with 0.
I am in process to develop TSql code to identify change in data.
I read about Binary_checksum and hashbyte. Some people say hashbyte is better than binay_checksum as chances of collision are less.
But if we may consider following, chances exist in hashbyte too. My question is what is the best way to compare data to identify change (I can't configure CDC) ?
--In Parent table(@ParentTable) there are 4 and 5 items each for product 101 and 102 respectively
INSERT INTO @ParentTable(ProductID,ItemID) VALUES(101,1234),(101,1578),(101,1590),(101,1237) INSERT INTO @ParentTable(ProductID,ItemID) VALUES(102,5465),(102,5466),(102,5474),(102,5489),(102,6543)
--child products 701 and 901 are derived from parent products(partially) 101 and 102 respectively
INSERT INTO @ChildParentTable(ParentID,ProductID,ItemID) VALUES(101,701,1234),(101,701,1590),(101,701,1578) INSERT INTO @ChildParentTable(ParentID,ProductID,ItemID) VALUES(901,102,5465),(901,102,5474),(901,102,8976)
--Here what I need is
--For product 701 there is one item missing ie 1237 which exists in its parent 101
--and For product 901 there are 3 items missing ie 5466,5489 and 6543 which exists in its parent 102
--and extra item exists ie 8976 so my result table should lokk like this
I'm trying to use linked server to import big amount of data(around 9 million rows) from mysql to a sql 2005 table. I plan to create a stored procedure and use job agent to run it at off-peak time everyday. The SP is like:
..... Truncate table sqltblA;
Insert Into sqltblA(col1, col2, col3) Select col1, col2, col3 From OpenQuery('mysql', 'Select col1, col2, col3 From mytblB') .....
But when I tried to CREATE the above SP from management studio, seems the sql server was trying to get all data from table mytblB and ended up with failure after several minutes hang. The error message is sth. like "running out memeory". BTW, during that several minutes hang, I can't query mytblB using mysql's tool as well. it seems to me that table got locked.
However if i try to change the returned result to 100,000 rows by changing script to
Insert Into sqltblA(col1, col2, col3) Select col1, col2, col3 From OpenQuery('mysql', 'Select col1, col2, col3 From mytblB Limit 100000') The SP could be created without any problem and running the SP could get the data in table sqltblA. But that's not what I want, I need all rows instead of a certain amount of rows.
Does anyone know what's going on there when I try to CREATE that SP and any solution to it?
Plus I tried to use SSIS as well. When I'm configuring the DataReader source, which uses ADO.NET's ODBC option to connect to mysql, if its sqlcommand is "select * from mytblB" without the Limit key word, then the configuration page will hang there as well and table mytblB is not accessible by using mysql tool. I'm just sick of wasting time on that and that's why I chose to use SP istead of SSIS. But same result. :-(
I'm using SQL Express with Advance Services & I get this error when I try to deploy my reports. Why do I get this error
An error has occurred during report processing. (rsProcessingAborted)
Cannot create a connection to data source 'EDPSYS'. (rsErrorOpeningConnection)
The feature: "Remote access to report data sources and/or the report server database" is not supported in this edition of Reporting Services. (rsOperationNotSupported)
