Dear p2p participants.

RE: [iMoving Data from Sybase (ODBC) to SQL Server using VB.NET][/i]

I wonder if you can help me with the following problem.

I would like to transfer data from a ODBC Database i.e. Sybase to my SQL Server 2000 database. I can run this from a DTS Import/Export wizard however I would like to put this into an application so that end users do require permissions to complete this task.

The problem is that the source data is not the same every time therefore a part of the application allows the user to enter a SELECT Statement which will query the source database and then it will need to dynamically create the tables on the destination database and then populate them with the data.

Is there any good examples on how to achieve the above?

I have looked at the generated bas file which maps the table as part of the script i.e.

    oCustomTask1.Name = "Create Table TEST_V324_Data Task"
    oCustomTask1.Description = "Create Table TEST_V324_Data Task"
    oCustomTask1.SQLStatement = "CREATE TABLE [TEST_V324_Data] (" & vbCrLf
    oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & "[TraId] varchar (10) NOT NULL, " & vbCrLf
    oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & "[OwnTab] varchar (9) NOT NULL, " & vbCrLf
    oCustomTask1.SQLStatement = oCustomTask1.SQLStatement
....

However since we do not now which tables are going to be selected we cannot hard code the mapping.

Any information / guidance would be greatly appreciated.

thank you in advance.

thanks




Jitu Tivari

__________________
Jitu Tivari

Like I asked before:

WHY are you doing it this way? Why can't you just Query Sybase- why do you need it in SQL server?

Why can't you just use a DTS package to transfer ALL of the data out of Sybase and they Query Sql Server?

What your trying to do seems so outside of a normal proccess- there are no examples for it. I expect it to be difficult to do.. And I'd like to come up with another SOLUTION to the business challenge you have, rather than encourage you to do something the wrong way.


Hal Levy
Web Developer, PDI Inc.

NOT a Wiley/Wrox Employee

Hi

Thank you for your reply.

We are not allowed to use the SYBASE database. currently we are outputing the data into a text file and then comparing them.

do you know of any good docs/Books/tutorials on how to compare the datasets and then re-apply them to the database?

I do not mind using the Import/Export Wizard in \MSSQL server however in the future i would like to automate that as much as possible.


thanks


Jitu Tivari

Jitu Tivari

You can create a job that runs the DTS package.

Hal Levy
Web Developer, PDI Inc.

NOT a Wiley/Wrox Employee