Newby DTS Question

[mike_kilby@hotmail.com]

Hi,

I am trying to set up a DTS Package to transfer data from a remote
MySQL server to a local MS SQL database. The source tables will remain
static as will the destination however I only want to copy down data
that does not already exist in the local database.

I have two different methods of which to identify the new rows, on some
tables it is by a unique ID thus in TSQL I would say WHERE ID x.
Other tables are by date where I would want to do something like WHERE
CreateDate >= GetDate()-1

How would I go about performing such clauses using DTS?

Thanks

Mike

[Thomas R. Hummel]

Re: Newby DTS Question

mike_kilby@hotm ail.com wrote:

Hi,
>
I am trying to set up a DTS Package to transfer data from a remote
MySQL server to a local MS SQL database. The source tables will remain
static as will the destination however I only want to copy down data
that does not already exist in the local database.
>
I have two different methods of which to identify the new rows, on some
tables it is by a unique ID thus in TSQL I would say WHERE ID x.
Other tables are by date where I would want to do something like WHERE
CreateDate >= GetDate()-1
>
How would I go about performing such clauses using DTS?

Hi Mike,

Assuming that I am understanding you correctly... in your data pump you
should select "SQL Query" on the "Source" tab. Then fill in the query
using something like:

SELECT my_columns
FROM dbo.My_Table
WHERE id ?

The "?" tells DTS that you will supply it with this parameter. Now
click on the Parameters button and create a global variable to hold the
maximum ID value in your destination table. Now you just need to make
sure that the global variable gets filled *before* your data pump. You
can use the Dynamic Properties Task for this, with an "On Success"
workflow constraint between that and your data pump.

You may need to play with the dates a bit to make sure that you have
the right global variable data type/formats, etc.

HTH,
-Tom.

[mike_kilby@hotmail.com]

Re: Newby DTS Question

Thanks for your help Tom,worked a treat.



Thomas R. Hummel wrote:

mike_kilby@hotm ail.com wrote:

Hi,

I am trying to set up a DTS Package to transfer data from a remote
MySQL server to a local MS SQL database. The source tables will remain
static as will the destination however I only want to copy down data
that does not already exist in the local database.

I have two different methods of which to identify the new rows, on some
tables it is by a unique ID thus in TSQL I would say WHERE ID x.
Other tables are by date where I would want to do something like WHERE
CreateDate >= GetDate()-1

How would I go about performing such clauses using DTS?

>
Hi Mike,
>
Assuming that I am understanding you correctly... in your data pump you
should select "SQL Query" on the "Source" tab. Then fill in the query
using something like:
>
SELECT my_columns
FROM dbo.My_Table
WHERE id ?
>
The "?" tells DTS that you will supply it with this parameter. Now
click on the Parameters button and create a global variable to hold the
maximum ID value in your destination table. Now you just need to make
sure that the global variable gets filled *before* your data pump. You
can use the Dynamic Properties Task for this, with an "On Success"
workflow constraint between that and your data pump.
>
You may need to play with the dates a bit to make sure that you have
the right global variable data type/formats, etc.
>
HTH,
-Tom.