We have some data source configured with ODBC connection.

Is there anyway I can use like cursor in DTS package to access this ODBC data source?

Or any way in stored procedure to access this ODBC data source and copy them over to local SQL server.


Thanks,



HG

If all you want to do is take all the tables and all the data then the
easiest way is to use

On the database into which you want to import

Right Click | All Tasks |Import Data

Specify the source as your ODBC source and the destination will be filled in
already for you.

It then gives you the ability to specify multiple tables there.

--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"HG" <anonymous (AT) discussions (DOT) microsoft.com> wrote

If I ues DTS import/export , any way to setup the trasaction control for the ODBC source.
Like in SQL Begin Transaction ... Commit. Seems I try in DTS ODBC connection, would't work.

Thanks,

HG

No not in the Wizard (Import/Export). Save the package to a SQL Server
instead of running it. You can then implement transactions using the
following method

1. Enable transactions at the package level (Package Properties)
2. On Success between each task
3. In the workflow properties for each task (check "Join transaction if
present","Rollback Transaction on Failure")
4. Make sure MSDTC is started

NOTE: Not everything supports joining a transaction

--

----------------------------
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org



"DTS ODBC trasaction" <anonymous (AT) discussions (DOT) microsoft.com> wrote