DTS package with inputs

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.
--
Paul G
Software engineer.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
"Paul" wrote:
> 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.
> --
> Paul G
> Software engineer.|||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?
--
Paul G
Software engineer.
"John Bell" wrote:
> 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
> "Paul" wrote:
> > 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.
> > --
> > Paul G
> > Software engineer.|||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
"Paul" wrote:
> 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?
> --
> Paul G
> Software engineer.
>
> "John Bell" wrote:
> > 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
> >
> > "Paul" wrote:
> >
> > > 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.
> > > --
> > > Paul G
> > > Software engineer.|||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.
--
Paul G
Software engineer.
"John Bell" wrote:
> 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
> "Paul" wrote:
> > 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?
> > --
> > Paul G
> > Software engineer.
> >
> >
> > "John Bell" wrote:
> >
> > > 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
> > >
> > > "Paul" wrote:
> > >
> > > > 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.
> > > > --
> > > > Paul G
> > > > Software engineer.