DTS connecting to CA Datacom

I am trying to import data from a CA Datacom database on a mainframe using SQL Server2000 Data Transformation Services. I can sign in with my username and password and see all the CA Datacom tables listed in DTS. When I select a table I get an error:

"Your provider does not support all the interfaces/methods required by DTS."

I am using the ODBC driver: CA Datacom/DB version 78.73.7970.

I have read in some other posts on the internet of people using this ODBC driver and being able to import data into SQL Server. Anybody have any ideas? Anybody doing this successfully?

your input is greatly appreciated.Ok.. I figured it out. For anyone else that may ever try doing this:

Instead of selecting the CA /Datacom DB driver in the pulldown list in DTS, I chose "Other ODBC data source" and this let me download tables.

DTS and SQL Server

Is it possible to run Data Transformation Services (DTS) on demand by having a user click on a button in an application with SQl Server?
I am creating a project in vb.net where I want the user to be able to hit an "update" button and data will be updated to my SQL Server table by DTS.

Also is it possible to check for duplicate entries before running DTS? E.g I want to append to my table but exclude duplicate entries UNLESS the data has changed for that entry.>>Is it possible to run Data Transformation Services (DTS) on demand

Yes. http://msdn.microsoft.com/msdnmag/issues/04/01/WebQA/

>>Also is it possible to check for duplicate entries before running DTS

Make this a step in your dts package

DTS (Oracle to SQL Server 2000)

I am trying to convert an application database from Oracle to SQL Server 2000. I am using Data Transformation Services utility provided by SQL Server. I am converting data on table-by-table basis. However, whenever I convert DATE fields (form Oracle) into DATETIME fields (to SQL Server 2000), my tasks fails. Does anyone knows the reason and, more important, a solution to this problem ?Hi

I'm also working with an Oracle Database.

In my configuration it works.
I used a Transform data task and the transformation I used is a copy column.

So

Oracle:
numeric/field1, datetime/field2 >> source in Transform data task

SQL Server 2000:
numeric/field1, datetime/field2 >> destination in Transform data task

Transformations: let SQL Server do automapping, copy column.

Greetings
Jon@.s

DTS (Data Transformation Services)

Given:
I'm using a DTS Package to import data into a table. Each row from a text
file gets placed into a corresponding table.
What I need to do:
I'd like to modify the DTS Package to change the data saved to a table based
on values in the records from the text file. In otherwords,
if a value from the text file > 150 then set a value in the table to 1...
if a value from the text file < 150 then set a value in the table to 2.
Thank You
Hi Steve
You might be able to make this column a calculated one! e.g.
CREATE TABLE MyTest ( id int not null identity(1,1),
textval text,
size as DATALENGTH(textval) )
INSERT INTO MyTest ( Textval )
SELECT 'ABC'
UNION ALL SELECT 'BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB BBBBBBBBBB'
UNION ALL SELECT
'CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC'
SELECT * FROM mytest
UPDATE MyTest SET Textval = 'A' WHERE id = 3
SELECT * FROM mytest
CREATE TABLE MyTest2 ( id int not null identity(1,1),
textval text,
size as CASE WHEN DATALENGTH(textval) > 10 THEN 1 ELSE 0 END )
INSERT INTO MyTest2 ( Textval )
SELECT 'ABC'
UNION ALL SELECT 'BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB BBBBBBBBBB'
UNION ALL SELECT
'CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC'
SELECT * FROM mytest2
UPDATE MyTest2 SET Textval = 'A' WHERE id = 3
SELECT * FROM mytest2
Otherwise you can do this is an ActiveX transformation
http://www.sqldts.com/default.aspx?279,5
John
"SteveS" wrote:

> Given:
> I'm using a DTS Package to import data into a table. Each row from a text
> file gets placed into a corresponding table.
> What I need to do:
> I'd like to modify the DTS Package to change the data saved to a table based
> on values in the records from the text file. In otherwords,
> if a value from the text file > 150 then set a value in the table to 1...
> if a value from the text file < 150 then set a value in the table to 2.
> --
> Thank You
|||ActiveX transformation is the simple option for this one!!!
Thanks,
Sree
"SteveS" wrote:

> Given:
> I'm using a DTS Package to import data into a table. Each row from a text
> file gets placed into a corresponding table.
> What I need to do:
> I'd like to modify the DTS Package to change the data saved to a table based
> on values in the records from the text file. In otherwords,
> if a value from the text file > 150 then set a value in the table to 1...
> if a value from the text file < 150 then set a value in the table to 2.
> --
> Thank You

DTS (Data Transformation Services)

Given:
I'm using a DTS Package to import data into a table. Each row from a text
file gets placed into a corresponding table.
What I need to do:
I'd like to modify the DTS Package to change the data saved to a table based
on values in the records from the text file. In otherwords,
if a value from the text file > 150 then set a value in the table to 1...
if a value from the text file < 150 then set a value in the table to 2.
Thank YouHi Steve
You might be able to make this column a calculated one! e.g.
CREATE TABLE MyTest ( id int not null identity(1,1),
textval text,
size as DATALENGTH(textval) )
INSERT INTO MyTest ( Textval )
SELECT 'ABC'
UNION ALL SELECT 'BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB
BBBBBBBBBBBBBBBBBBB
B'
UNION ALL SELECT
'CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
CCCCC'
SELECT * FROM mytest
UPDATE MyTest SET Textval = 'A' WHERE id = 3
SELECT * FROM mytest
CREATE TABLE MyTest2 ( id int not null identity(1,1),
textval text,
size as CASE WHEN DATALENGTH(textval) > 10 THEN 1 ELSE 0 END )
INSERT INTO MyTest2 ( Textval )
SELECT 'ABC'
UNION ALL SELECT 'BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB
BBBBBBBBBBBBBBBBBBB
B'
UNION ALL SELECT
'CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
CCCCC'
SELECT * FROM mytest2
UPDATE MyTest2 SET Textval = 'A' WHERE id = 3
SELECT * FROM mytest2
Otherwise you can do this is an ActiveX transformation
http://www.sqldts.com/default.aspx?279,5
John
"SteveS" wrote:

> Given:
> I'm using a DTS Package to import data into a table. Each row from a text
> file gets placed into a corresponding table.
> What I need to do:
> I'd like to modify the DTS Package to change the data saved to a table bas
ed
> on values in the records from the text file. In otherwords,
> if a value from the text file > 150 then set a value in the table to 1...
> if a value from the text file < 150 then set a value in the table to 2.
> --
> Thank You|||ActiveX transformation is the simple option for this one!!!
Thanks,
Sree
"SteveS" wrote:

> Given:
> I'm using a DTS Package to import data into a table. Each row from a text
> file gets placed into a corresponding table.
> What I need to do:
> I'd like to modify the DTS Package to change the data saved to a table bas
ed
> on values in the records from the text file. In otherwords,
> if a value from the text file > 150 then set a value in the table to 1...
> if a value from the text file < 150 then set a value in the table to 2.
> --
> Thank You

DTS (Data Transformation Services)

Given:
I'm using a DTS Package to import data into a table. Each row from a text
file gets placed into a corresponding table.
What I need to do:
I'd like to modify the DTS Package to change the data saved to a table based
on values in the records from the text file. In otherwords,
if a value from the text file > 150 then set a value in the table to 1...
if a value from the text file < 150 then set a value in the table to 2.
--
Thank YouHi Steve
You might be able to make this column a calculated one! e.g.
CREATE TABLE MyTest ( id int not null identity(1,1),
textval text,
size as DATALENGTH(textval) )
INSERT INTO MyTest ( Textval )
SELECT 'ABC'
UNION ALL SELECT 'BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB'
UNION ALL SELECT
'CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC'
SELECT * FROM mytest
UPDATE MyTest SET Textval = 'A' WHERE id = 3
SELECT * FROM mytest
CREATE TABLE MyTest2 ( id int not null identity(1,1),
textval text,
size as CASE WHEN DATALENGTH(textval) > 10 THEN 1 ELSE 0 END )
INSERT INTO MyTest2 ( Textval )
SELECT 'ABC'
UNION ALL SELECT 'BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB'
UNION ALL SELECT
'CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC'
SELECT * FROM mytest2
UPDATE MyTest2 SET Textval = 'A' WHERE id = 3
SELECT * FROM mytest2
Otherwise you can do this is an ActiveX transformation
http://www.sqldts.com/default.aspx?279,5
John
"SteveS" wrote:
> Given:
> I'm using a DTS Package to import data into a table. Each row from a text
> file gets placed into a corresponding table.
> What I need to do:
> I'd like to modify the DTS Package to change the data saved to a table based
> on values in the records from the text file. In otherwords,
> if a value from the text file > 150 then set a value in the table to 1...
> if a value from the text file < 150 then set a value in the table to 2.
> --
> Thank You|||ActiveX transformation is the simple option for this one!!!
Thanks,
Sree
"SteveS" wrote:
> Given:
> I'm using a DTS Package to import data into a table. Each row from a text
> file gets placed into a corresponding table.
> What I need to do:
> I'd like to modify the DTS Package to change the data saved to a table based
> on values in the records from the text file. In otherwords,
> if a value from the text file > 150 then set a value in the table to 1...
> if a value from the text file < 150 then set a value in the table to 2.
> --
> Thank You

DTS "execute on main package thread" issue

here is the error i get:

Microsoft Data Transformation Services (DTS) Package
0
(1:Replicate D318_DOCKET) SubStep 'DTSStep_DTSDataPumpTask_2' failed with the following error:
Provider generated code execution exception: EXCEPTION_ACCESS_VIOLATION

the package runs fine by itself from within Enterprise Manager but when i try to run the package from a Visual Basic project, i get this error. i tried to be careful to make sure i checked the "execute on main package thread" checkbox but it didnt make a difference...

has anyone seen this?I haven't seen it, but it looks like a permissioning problem. When you run the DTS package from VB are you running it with the same user details are when you run it from within Enterprise Manager?|||http://search.microsoft.com/search/results.aspx?na=84&st=a&View=msdn&qu=Provider+generated+code+execution+exception%3A+ EXCEPTION_ACCESS_VIOLATION&qp=&qa=&qn=&c=&s=4

DST and Notification Services

I need some clarification on Daylight Saving Time changes for SQL
Server 2005.
I read MicroSoft Article 931815.
Per the doc, I have determined that I have Notification Services
installed (determined via registry).
However, I do not seem to have any Notification Services to stop (step
1) and not sure what name to use for NSMainDatabase (running the osql
script in step 5).
>From the SQL Server Management Studio, I can determine that I have
version 9.0.242.0 of Notification Services, but it tells me that NO
instances are registered on this machine.
So, please advise if you have any input. I am about 80% confident that
I don't need to do this fix.
Thanks,
RogerIf you do not have an instance of Notification Services deployed, then you
do not have to worry about patching. The binary files of Notification
Services are not affected. You have to apply the update only when you create
an instance. If there are no services running and no NS* databases, then
really nothing to do. Also, for instances deployed after SQL Server 2005 SP2
is installed you do not have to do anything, as instances created with SP2
will have the correct DST info.
HTH,
Plamen Ratchev
http://www.SQLStudio.com|||On Mar 6, 2:59 pm, "Plamen Ratchev" <Pla...@.SQLStudio.com> wrote:
> If you do not have an instance of Notification Services deployed, then you
> do not have to worry about patching. The binary files of Notification
> Services are not affected. You have to apply the update only when you crea
te
> an instance. If there are no services running and no NS* databases, then
> really nothing to do. Also, for instances deployed after SQL Server 2005 S
P2
> is installed you do not have to do anything, as instances created with SP2
> will have the correct DST info.
> HTH,
> Plamen Ratchevhttp://www.SQLStudio.com
I thank you for your comments. Much appreciated.

DST and Notification Services

I need some clarification on Daylight Saving Time changes for SQL
Server 2005.
I read MicroSoft Article 931815.
Per the doc, I have determined that I have Notification Services
installed (determined via registry).
However, I do not seem to have any Notification Services to stop (step
1) and not sure what name to use for NSMainDatabase (running the osql
script in step 5).
>From the SQL Server Management Studio, I can determine that I have
version 9.0.242.0 of Notification Services, but it tells me that NO
instances are registered on this machine.
So, please advise if you have any input. I am about 80% confident that
I don't need to do this fix.
Thanks,
RogerIf you do not have an instance of Notification Services deployed, then you
do not have to worry about patching. The binary files of Notification
Services are not affected. You have to apply the update only when you create
an instance. If there are no services running and no NS* databases, then
really nothing to do. Also, for instances deployed after SQL Server 2005 SP2
is installed you do not have to do anything, as instances created with SP2
will have the correct DST info.
HTH,
Plamen Ratchev
http://www.SQLStudio.com|||On Mar 6, 2:59 pm, "Plamen Ratchev" <Pla...@.SQLStudio.com> wrote:
> If you do not have an instance of Notification Services deployed, then you
> do not have to worry about patching. The binary files of Notification
> Services are not affected. You have to apply the update only when you create
> an instance. If there are no services running and no NS* databases, then
> really nothing to do. Also, for instances deployed after SQL Server 2005 SP2
> is installed you do not have to do anything, as instances created with SP2
> will have the correct DST info.
> HTH,
> Plamen Ratchevhttp://www.SQLStudio.com
I thank you for your comments. Much appreciated.