Re: SQL Server DTS From Sybase

• Previous message: Allan Mitchell: "Re: SQL Server DTS From Sybase"
• In reply to: Allan Mitchell: "Re: SQL Server DTS From Sybase"
• Messages sorted by: [ date ] [ thread ]

Date: Fri, 3 Dec 2004 06:30:56 -0000

Oh one more thing

The code

Set oDataPump = oPkg.Tasks("DTSStep_DTSExecuteSQLTask_2").CustomTask

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@no-spam.sqldts.com> wrote in message 
news:uRwP0$P2EHA.924@TK2MSFTNGP14.phx.gbl...
> 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").CustomTask
>
> 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@pacifictabla.com> wrote in message 
> news:2642ccfe.0412022212.2391115d@posting.google.com...
>> 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").CustomTask
>>
>> 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@no-spam.sqldts.com> wrote in message 
>> news:<#kT8UjL2EHA.524@TK2MSFTNGP09.phx.gbl>...
>>> 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@pacifictabla.com> wrote in message
>>> news:2642ccfe.0412021331.79cec058@posting.google.com...
>>> > 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!
>
> 

• Previous message: Allan Mitchell: "Re: SQL Server DTS From Sybase"
• In reply to: Allan Mitchell: "Re: SQL Server DTS From Sybase"
• Messages sorted by: [ date ] [ thread ]