DTS package with inputs

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Paul - 18 Mar 2008 21:02 GMT

Hi I am using mssql 2000 and have been performing a data port manually that
consists of running a query on an sql database (using a date range as an
input) and then connecting to an Oracle database and appending a table in the
Oracle database with the data from the SQL query. The SQL query writes the
data to a table and I then use access to connect to this table and the
destination table in the Oracle database and run a simple query inside of
access to append the data into the Oracle table. I would like to create a
dts package that does this but the initial query requires 2 input parameters,
start date and end date, anyhow just wondering if you can still build a dts
package that when run from Enterprise manager would prompt for the date
inputs and then perform the sql query as well as the data append to the
Oracle table.
Thanks.

Signature

Paul G
Software engineer.

Reply to this Message

John Bell - 19 Mar 2008 09:19 GMT

Hi Paul

You can set global variables on the command line such as (this has wrapped):

DECLARE @datefrom CHAR(10)
DECLARE @dateto CHAR(10)
DECLARE @cmd varchar(2200)
DECLARE @cmdroot varchar(2000)
DECLARE @stat int

SET @cmdroot = 'DTSRun /S "' + @@SERVERNAME + '" /W "0" /E /N "' + DB_NAME()
+ ' Monthly Export" '

SELECT @dateto =
CONVERT(CHAR(10),DATEADD(dd,1-DATEPART(dd,GetDate()),CAST(FLOOR(CAST(getdate() AS FLOAT)) AS DATETIME)),121),
@datefrom =
CONVERT(CHAR(10),DATEADD(m,-1,DATEADD(dd,1-DATEPART(dd,GetDate()),CAST(FLOOR(CAST(getdate() AS FLOAT)) AS DATETIME))),121)

SET @cmd = @cmdroot + ' /A "DateFrom":"7"="' + @datefrom + '" /A
"DateTo":"7"="' + @dateto + '"'

EXEC @stat = master..xp_cmdshell @cmd
SET @stat = COALESCE(@stat,@@ERROR)

John

> Hi I am using mssql 2000 and have been performing a data port manually that
> consists of running a query on an sql database (using a date range as an
[quoted text clipped - 9 lines]
> Oracle table.
> Thanks.

Reply to this Message

Paul - 20 Mar 2008 00:41 GMT

Hi thanks for the response, for some reason I did not receive the
notification of replies. So would you place the variable declarations as
well as the provided code in the actual SQL job as the command in a step, or
would this be placed in one of the queries of the dts package?

Signature

Paul G
Software engineer.

> Hi Paul
>
[quoted text clipped - 35 lines]
> > Oracle table.
> > Thanks.

Reply to this Message

John Bell - 20 Mar 2008 08:46 GMT

Hi Paul

The values would need to be global variables and to assign them withing the
job would require an activeX task. It is easier and probably a more flexible
design if you have a job that you pass the dates required to it. Then if at
some point you need a different date range or run it manually then it is easy
to do.

I would put the code into a stored procedure and then run that from the SQL
Agent job step.

If you are using stored procedures withing the DTS package then you can have
parameters that maped to the global variables see
http://www.sqldts.com/234.aspx

John

> Hi thanks for the response, for some reason I did not receive the
> notification of replies. So would you place the variable declarations as
[quoted text clipped - 40 lines]
> > > Oracle table.
> > > Thanks.

Reply to this Message

Paul - 21 Mar 2008 00:14 GMT

ok thanks for the additional information. I will probably have to run this
manually for some time as it is necessary to perform some manual data checks
first. Eventually hope to automate and schedule the task. Will take a look
at the provided link.

Signature

Paul G
Software engineer.

> Hi Paul
>
[quoted text clipped - 57 lines]
> > > > Oracle table.
> > > > Thanks.

Reply to this Message