August 12, 2009 at 11:26 am
i'm working on a package where I get data from a Sybase server and import it to a MS SQL server.
The query has a variable that is a select max date from a table in the destination database.
it's currently running as a stored procedure using openrowset on windows 2000, sql 2000 and some sybase driver from 1998. we're in the process of moving it to SQL 2005 and i'm trying to optimize it to how SSIS works.
is there an easy way to do this in SSIS? my plan b is to just import all 5 million rows or so and just do the clean up on the SQL side
August 12, 2009 at 12:23 pm
Hi...
I have a similar scenario...whereby I import from Progress 9.1E (our production system) into SQL (our reporting system)... I use the MAX(IDNO) from the SQL table as the variable in my import from Progress.
This is a process we use to use replicate SQL with Progress on 5 min intervals....across more than a dozen 10M+ row Progress tables
I "played" with SSIS packages and variables etc in an attempt to get this working, but failed miserably after many long nights.. :crying:...I just couldnt seem to get my head around the intricacies (and/or) complexities of SSIS...I was used to SQL2K DTS
and was now completely lost !
I solved my issue by using SSIS but with using "Execute SQL task" for each table and putting them all into one scheduled package.
The TSQL in the task utilises variables and creates an OPENQUERY statement against a linked server to the Progress db
My main problem after that was to ensure that the variable I was using in the OPENQUERY made use of an appropriate index on the Progress db...otherwise :exclamationmark:
If you are intersted I can post some example code...other than that I wish you good luck and I will be following this thread to hopefully learn something.
Kind regards Graham
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
August 13, 2009 at 9:13 am
going to try a different strategy
It's only 5 million rows or so, and i'm going to create one nightly job to transfer the data to SQL and leave all DML and data sorting in separate jobs. Going to modify them to read from the local copy of the data instead of going to Sybase
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply