Hi:
I want to import data from a Sybase database via a SQL Server DTS package.
The database views in Sybase I will be importing from all have different
names according to the date of the data in that view. For example:

database.dbo.SybaseView20041122

only has data for Nov. 22, 2004. I want to put all the data from these
tables on a daily basis into a table called "DataFromYesterday". So in the
previous example the package would run on Nov. 23, 2004 and figure out what
the view name is for yesterday and put the data into the table I just
metioned.

If the data were in SQL Server already, I would simply set up a DTS package
and use a query to select the data:

declare @V1 varchar(4000)
declare @sql varchar(4000)
select @V1 = 'SQLServerDB.dbo.SQLTable' + convert(varchar(8), getdate()-1,
112)
select @sql = 'SELECT * FROM ' + @V1

EXEC (@sql)

This would dynamically figure out the table name and get the data from
yesterday, which would be contained in the table
"SQLServerDB.dbo.SQLTable20041201". I then output to a table called
"DataFromYesterday".

However, when I try to use the same method in a DTS package from Sybase I
get an error, saying "datatype INT not compatible with datetime", or
something like that.

Is it possible to do this and import the data from Sybase?

Thanks!

The way I would do it is

1. Build a DataPump between objects of the same structure that you will be
using
2. Add an ExecuteSQL task to create the new table
3. Add An Active Script task to the start
4. Use this article to figure out how to dynamically use Dates

How can I change the filename for a text file connection?
(http://www.sqldts.com/default.aspx?200)

5. Assign the new Table name using dates to the SourceObjectName of the
DataPump task and the new table to the DestinationObjectName of the DataPump
task

Changing the DataPump Source and Destination Tables
(http://www.sqldts.com/default.aspx?213)



--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.konesans.com - Consultancy from the people who know


"Kayda" <shop (AT) pacifictabla (DOT) com> wrote

Thanks for the quick response. However, as I'm pretty green at this I
need a little more detail. I understand SQL and VBScript, but I'm just
not sure how to put things together in DTS. I'm also consused as I'm
on a course learning Business Objects Data Integrator this week, (you
know, similar, yet different :-) ). Questions:

1. When you say "DataPump Task" do you just mean an "Execute SQL
Task". I see no "Data Pump Task" (SQL 2000). I'm confused by part of
the code in the second link:
' Get reference to the DataPump Task
Set oPkg = DTSGlobalVariables.Parent
Set oDataPump = oPkg.Tasks("DTSStep_DTSExecuteSQLTask_2").CustomTa sk

Not sure what that is doing exactly.

2. I setup 2 data connections, 1 for Sybase and 1 for the SQL Server
destination and they just stand alone in the workspace?
3. Could you just spell out how everything is linked.

FYI, there is no need to create a new table everytime. The table will
just always be there in SQL Server. I'll do is delete the data in the
table.

Thanks for all the help,

Kayda

"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

DataPump is used interchangeably with the transform Data Task ergo not the
ExecuteSQL task

Set oPkg = DTSGlobalVariables.Parent
Set oDataPump = oPkg.Tasks("DTSStep_DTSExecuteSQLTask_2").CustomTa sk

This code gets a ref to the current package on the first line and then grabs
the customTask object of a task (ExecuteSQL).

If the table will always be there then use an ExecuteSQL task to do the
DELETE.

Have a look here

DTS Tutorials
(http://www.sqldts.com/default.aspx?107)

--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.konesans.com - Consultancy from the people who know


"Kayda" <shop (AT) pacifictabla (DOT) com> wrote

Oh one more thing

The code

Set oDataPump = oPkg.Tasks("DTSStep_DTSExecuteSQLTask_2").CustomTa sk

could technically be right but will more than likely be wrong.

It looks as though the name of the task given is the name of the step. two
very different propositions.



--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.konesans.com - Consultancy from the people who know


"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote