| Author |
Topic |
|
dupati1
Posting Yak Master
123 Posts |
 Posted - 2005-03-31 : 16:31:58
|
| Hello All,I have to create a DTS package that transfers data from a Sybase Database to SQL server 2000 database.I made all the connections and i also created a simple DTS package that just moves a single table data from source to the destination. It works fine. Here is my question with an example:Table Name:AccountMaster-has around 100 columns, 100K records. Its in Sybase DB. Its a live database.Now i want to move this table to SQL Server1. What is the best and efficient way to create this DTS package so that i can schedule it to run every midnight.I have unique field which is the Account Number. I thought of updating only the newly added Account Numbers but I ALSO NEED TO TRACK THE CHANGES TO ANY OF THE COLUMNS FOR THE EXISTING ACCOUNTS.this is where i got stuck. What would be efficient method to transfer this kind of table from source to destination DB??Any suggestions.Thanks in advance |
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2005-03-31 : 20:06:15
|
| okay, try this:Schedule your DTS package using the built in SQL Agent scheduler. The easiest way to do this is to right click your DTS package and select "Schedule Package". Go through the wizard to setup the schedule you want and you are done. Second, you can base your transform on a query. that way you can write a query to only bring over the records that don't already exist. To do this, you would create your two connection objects (sybase for the source, sql server for the destination) and then create a transform between them. Edit the properties of the transform and then select the Query radio button. Put in your query and you are all set.-ec |
 |
|
|
dupati1
Posting Yak Master
123 Posts |
Posted - 2005-03-31 : 20:48:40
|
Thanks for the reply.Yes i have decided to base my Transform on query and came up with something like this:INSERT t1 (id, col1, col2, col3)SELECT id, col1, col2, col3FROM t2WHERE id NOT IN (SELECT id FROM t1)UPDATE t1SET col1 = t2.col1, col2 = t2.col2, col3 = t2.col3FROM t1 JOIN t2 ON t1.id = t2.idWHERE t1.col1 <> t2.col1 OR t1.col2 <> t2.col2 OR t1.col3 <> t2.col3 now what i dont understand is where should i put these queries...OK i performed steps something like this:1. Create first Connection - OLE SQL Driver2. Create second Connection - Other Connection-Sybase Odbc3. Clicked on Transform Data Task (a arrow pointing from sybase to SQL shows)4. Right click on the arrow and click on properties... then i am stuck at this step...should i write my query here?? can i perform both insert and update at same time??should i include any intermediary step. Please provide any suggestions...Thanks |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2005-03-31 : 21:01:36
|
| updates make this a little harder. Do you have a timestamp on your source table indicating when the data is updated or inserted? Without something like that, your query will be messy.Can you post some DDL for the tables involved, that might help us figure out a query that you can use.-ec |
|
|
|
dupati1
Posting Yak Master
123 Posts |
Posted - 2005-04-01 : 08:54:10
|
Thanks for your prompt reply again. I am afraid that i dont have a timestamp on the source table. Here is how DDL looks for table named ACCOUNT_MASTER. I am only showing a part of it.CREATE TABLE [ACCOUNT_MASTER] ([MarketId] char (5) NOT NULL, [AccountNumber] int NOT NULL, [BillCloseDay] smallint NOT NULL, [CustomerType] char (1) NOT NULL, [SSN] int NOT NULL, [Birthdate] datetime NOT NULL, [AcctStatus] char (1) NOT NULL, [NumberBills] smallint NOT NULL, [BillGroup] char (2) NOT NULL, [LastName] varchar (25) NOT NULL, [FirstName] varchar (15) NOT NULL, [Address1] varchar (25) NOT NULL, [Address2] varchar (25) NOT NULL, [City] varchar (20) NOT NULL, [State] char (2) NOT NULL, [Zip] char (9) NOT NULL, [ContactName] varchar (20) NOT NULL, [ContactPhoneNo] char (10) NOT NULL, [ActivationDate] datetime NOT NULL, [CorpAcctNumber] int NOT NULL) I have doubled checked to see if there was any time field in the table but did not find it.Also i have 20 different tables on sybase that i need to move to sql server 2000 database. do i need to create 20 different DTS packages or can i do it in one DTS package.Please provide suggestions...Thanks in advance |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2005-04-01 : 10:26:43
|
| You can copy all your tables in one DTS package, that isn't a problem. www.sqldts.com is a good site with a lot of examples that you might want to check out.As far as your query goes, you may not have any option other than to perform a full refresh of this table each time. That is probably the easiest route.Other solutions include hashing the values in your source table to determine what has changed. This may not work well or be practical since your table is so wide. You could also create an on update trigger that tracked changed rows in a summary table. you would then reference the summary table to determine the rows to pull over in yoru DTS query. You could add an additional column and have that column populated with a datetime value for the modification date.I personally would go with the complete refresh to see if that works well enough. The other solutions require a bit more work and are more complicated.-ec |
|
|
|
dupati1
Posting Yak Master
123 Posts |
Posted - 2005-04-01 : 11:24:31
|
| One last question. You said i can do tranfer all my data from sybase db to sql server db using ONE DTS package.How can i copy all tables into one DTS package??For example...Lets suppose I have two tables named Account_Master and Mobile_Phone on Sybase that i want to bring them into sql server.then what i presently did is package1: My_Acc_Master : Which shows the source as sybase and dest as sql...when i did this package it allowed me to select only one table in the drop downsimilarly i did the other package for the other table...am i missing something here...how can i specify that table 1 on syabse should save as table 1 on sql server and table 2 on sybase as table 2 on sql server??i did not see anything like this on DTS wizard...Thanks |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2005-04-01 : 12:54:49
|
| you can add multiple transforms between data sources. Each of the transforms could be for a different table.-ec |
|
|
|
dupati1
Posting Yak Master
123 Posts |
Posted - 2005-04-01 : 13:08:16
|
| thank you..i will try to do this...Also i thought its a better thing to go with the complete refresh instead of writing queries...I thought of creating a linked server and then writing stored procedures with the insert and update statements...but it was getting really messyI am not really sure how the performance differs in both the cases...Thanks |
|
|
|
PW
Yak Posting Veteran
95 Posts |
Posted - 2005-04-01 : 18:59:04
|
| If running queries, it is usually faster to 'stage' the external data into a Sql Server 2000 DB, and then run your queries against the 'staged' copy. Having the copy also allows you to create 100% fillfactor indexes that best support your subsequent queries, which you could never do against the live Sybase tables.Once staged, you can check what has changed using a CheckSum():UPDATE t1SET col1 = t2.col1, col2 = t2.col2, col3 = t2.col3FROM t1 JOIN t2 ON t1.id = t2.idWHERE CheckSum(t1.*) <> CheckSum(t2.*) |
|
|
|
dupati1
Posting Yak Master
123 Posts |
Posted - 2005-04-01 : 19:12:51
|
| Thanks PW, Sounds like a good idea. But i am unable to understand two things here: 1. Did you mean using a temporary table or a view as the stage?? i did not understand when you said stage external data??2. since we are kind of implementing two steps instead of a direct full refresh, what do you think the performance and efficiency would be??Thanks |
|
|
|
PW
Yak Posting Veteran
95 Posts |
Posted - 2005-04-01 : 19:20:11
|
| The implementation is up to you, but we use a separate 'staging' Sql2K database with permanent tables that map exactly to whatever external tables we need to import. By 'external' data I was referring to any data being pulled into Sql2K from an external data source, in your case your Sybase system.Question #2 depends on several factors. eg, if an existing account changes, do you need to keep a history of what the old data values were ? Or do you simply overwrite with the updated values ? |
|
|
|
dupati1
Posting Yak Master
123 Posts |
Posted - 2005-04-01 : 20:45:57
|
| PW, Thanks for explaining. so if i understand you correctly...Lets say i have a database named ExternalData in SQL 2000 which has all the tables that relate to respective tables on the sybase database...(Now to create this so called ExternalData DB...is it done through DTS package or using some other techinque. OR What is the best way to do this??)I totally agree with your answer on the second questions. In my project we just need the updated values and dont need to keep track of the old values.Thanks for your ideas and suggestions. |
|
|
|
PW
Yak Posting Veteran
95 Posts |
Posted - 2005-04-02 : 15:37:51
|
| >>Now to create this so called ExternalData DB...is it done through DTS package or using some other techinque. OR What is the best way to do this??)I use 1 of 2 techniques, depending on data source. Both require manually creating the database 1st.1 - If the other data source has compatible table creation DLL and has tools to allow scripting objects, I just script the CREATE TABLE DDL on the other system, then run it on the Sql2K DB2 - I set up a linked server, and run queries that look like:SELECT *INTO sql2k_staging.dbo.tablenameFROM link.db.owner.tablenameWHERE 0 = 1 -- creates an empty table structureWhat the DTS package does is TRUNCATEs all the tables, then runs the data pumps that pull the data into the tables. For large tables, I also have my DTS package run an initial script to drop indexes on the SQL2K staging tables, and a post-import script that recreates indexes at 100% fillfactor, since the tables are to be readonly. |
|
|
|
|
DTS question