DTS From SQL Server 2000 to Btrieve

Hi,

This is my 1st time out here so please bear with me.

I am trying to transfer data from a SQL Server 2000 DB to a Btrieve file. I have everything set up and used the Import/Export Utility to create the DTS Package. Everything seems to wotk fine until I hit a field in the SQL Server 2000 DB that has a signle quote (') in a name field. Btrieve seems to bomb on that record. If I take the single quote out, it works fine until the next name with a single quote. What can I do to get these records imported?

Thanks much,
Rayuse the following function

ltrim(replace(columnname,'''',''))

you are replacing a single quote in a column with a null and you trimming that with ltrim function.......|||Thanks for the info, but I actually need the single quote. I have set my transformation to change the signle quote into a ~ (tilde). My C program the changes the ~ back to a single quote when updating the main data file from the temp one SQL Server updated.

Ray Fantasia

DTS from Informix database

Hi,

I want to transfer data from an Informix database using DTS. What driver do I need to use for that? What are the settings for the Data Source Name? Has anyone of you accomplished DTS from Informix to SQL Server?

Let me know if you have any ideas that you can share.

Thank you.

VivekYou can get the informix oledb provider from the informix client sdk cd - then use that provider in your dts package.|||There is a known bug in that package. Check out IBM website for more information on that. It will save you a lot headaches.|||Which version and bugs ...|||Also, as with any informix product - make sure you read the release notes.|||How much will I have to pay for this driver that you are talking about?|||Also what version of the Informix Client SDK should I get hold of? I have no experience in Informix.|||Here is the link to ibm/informix client sdk (includes release notes):

link (http://www-306.ibm.com/software/data/informix/tools/csdk/)

DTS from AS 400 to Sql server

Hi DBA's:

I need to transfer data from my source database which is Client Access iSeries AS 400
running DB 400 to my database in SQL Server 2000. Here is what I need to do -

There are files in the Production Enviornment on the DB 400. I need to connect to these files from my SQL Server 2000 database using DTS Wizard and tranfer these files into the tables of my SQL Server 2000 database. Can someone walk me through with the options on the source side of the DTS that I need to choose in order to connect to my AS 400 and get the data.

Thanks in anticipation.I'm assuming you mean DB2, not DB 400...

Look up sp_addlinkedserver in BOL

DTS failed to run automaticlly

Hello,
I created a DTS to transfer data from one SQL server to another SQL server.
However, I can only run the job manually. If I scheduled this job to be run
everyday automaticlly, It failed.
Please help.
Thanks.
FuhlSounds like a permissioning problem. When you manually run the package it executes using your username and permisions when it executes as a job I believe it runs as the SQL Agent (not certain on this but it is definately not using your permissions).

HTH|||rokslide is right.
When the dts or jobs run automatically they use SQL Server Agents startup accounts credentials. Just that that the SQL Agent startup account has permissions to do the job what you have put in the DTS.

Also is should have permission to write on the other server don't forget that.

DTS Error

I am having some problem creating a new DTS task or modifying an existing
one. My goal is to transfer data from a table (tableX) to a text file
(export.txt). I can not define the columns I want to transfer in the
Destination tab under the Transfer Data Task Properties. If I use the Define
Columns… button, Enterprise Manager crashes. If I try to define the column
s
manually, I get the following message:
A DTSTransferCopy must specify no columns (signifying a sequential 1-to-1
mapping of all columns) or the same number of source and destination columns
.
Please help.
ThanksHi
If you are only wanting to transfer a subset of the source tables columns
then you will need to restrict the columns in the source by specifying a SQL
query that selects the columns that your require.
John
"Emma" <Emma@.discussions.microsoft.com> wrote in message
news:088B07FA-328C-469A-A9D4-83C0A6730FE9@.microsoft.com...
>I am having some problem creating a new DTS task or modifying an existing
> one. My goal is to transfer data from a table (tableX) to a text file
> (export.txt). I can not define the columns I want to transfer in the
> Destination tab under the Transfer Data Task Properties. If I use the
> Define
> Columns. button, Enterprise Manager crashes. If I try to define the
> columns
> manually, I get the following message:
> A DTSTransferCopy must specify no columns (signifying a sequential 1-to-1
> mapping of all columns) or the same number of source and destination
> columns.
> Please help.
> Thanks
>

DTS DB2 Dates 0001-01-01

Hi,

I am trying to transfer a table from DB2 to SQLServer 2000 through a DTS package. The DB2 table contains fields with default dates of "0001-01-01". The DTS package errors out whenever it reads this date as "invalid data value". In SQLServer 2000, the date fields are of type ShortDateTime. I have searched the Internet but did not find a workable solution. Please, can anyone help me find a solution?

Older applications sometimes used dates like '9999-12-31' or '0001-01-01' to signify that the date was either NULL, invalid or not-entered. If DB2 is using that date as a valid point in time then the following would not work. However if it is a sentinal value used to represent NULL then you could transform such dates into NULL on-the-fly as you extract them from DB2; in pseudocode:

SELECT

NullIf(theDate, '0001-01-01') as theDate, <other fields>

FROM theTable

(You would need to find out the equivalent function for NullIf in DB2 parlance) This would have the effect of leaving all dates alone except for '0001-01-01' which would be translated to NULL, which would keep SSIS happy.

If you translated it to NULL then you would have to be careful that you did not break other business rules.

|||Thanks but I don't have any control over the creation of DB2 tables hence I was looking for DTS to do the trick. Can DTS handle this?|||In the source component of your DTS package, use the SELECT statement and conversion functions as detailed in my previous reply.|||

Thanks.

I am rather new to DTS and am trying to get an example of creating a package using vbscript. I have looked at Books on line but the examples are a little confusing for me right now. Do you by chance have any sample code that shows how to do something similar like this ?

Appreciate your help.

DTS data transfer and Update

My DTS package transfers data mostly codes from temp to master table. The master table has 10 description fields for each of the codes. These description fields are then populated using update statements joined with 10 description tables. However, the update process is so inefficient since a single update statement takes several hours to finish.

The second option is to populate description fields as codes are transferred from temp to master table using 10 left outer joins with description tables. But the result was unexpected. It's giving me more than twice as many records as there are in the temp table.

What would be the best (efficient) approach for this situation? Would greately appreciate any help/thoughts.

thanks.I think I would create a view, unload the vie and bcp the data into the table in native format

Do the code tables contain keys?|||a single update statement takes several hours to finish

Then you must be doing something wrong...

The master table has 10 description fields

This sounds like awful design! Fancy posting the DDL of the table in question?sql

DTS between SQL 7 and SQL 2000

Hi!!!
I am a newbie and need know how can I use the DTS to transfer one DB from
SQL 7 which has CP850 as a code page to a SQL 2000 which has the collation
SQL_Latin1_General_CI_AS.
What I need to check during DTS operation to preserve SQL 7 code page in
order to avoid weird characters in SQL 2000?
Thanks for any information.> I am a newbie and need know how can I use the DTS to transfer one DB from
> SQL 7 which has CP850 as a code page to a SQL 2000 which has the collation
> SQL_Latin1_General_CI_AS.
> What I need to check during DTS operation to preserve SQL 7 code page in
> order to avoid weird characters in SQL 2000?
In SQL 2000, you can create a database with different collation than servers
default collation. So you can have a database on SQL 2000 with the same
collation that is present on SQL 7.0.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com

DTS between SQL 7 and SQL 2000

Hi!!!
I am a newbie and need know how can I use the DTS to transfer one DB from
SQL 7 which has CP850 as a code page to a SQL 2000 which has the collation
SQL_Latin1_General_CI_AS.
What I need to check during DTS operation to preserve SQL 7 code page in
order to avoid weird characters in SQL 2000?
Thanks for any information.> I am a newbie and need know how can I use the DTS to transfer one DB from
> SQL 7 which has CP850 as a code page to a SQL 2000 which has the collation
> SQL_Latin1_General_CI_AS.
> What I need to check during DTS operation to preserve SQL 7 code page in
> order to avoid weird characters in SQL 2000?
In SQL 2000, you can create a database with different collation than servers
default collation. So you can have a database on SQL 2000 with the same
collation that is present on SQL 7.0.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com

DTS between SQL 7 and SQL 2000

Hi!!!
I am a newbie and need know how can I use the DTS to transfer one DB from
SQL 7 which has CP850 as a code page to a SQL 2000 which has the collation
SQL_Latin1_General_CI_AS.
What I need to check during DTS operation to preserve SQL 7 code page in
order to avoid weird characters in SQL 2000?
Thanks for any information.
> I am a newbie and need know how can I use the DTS to transfer one DB from
> SQL 7 which has CP850 as a code page to a SQL 2000 which has the collation
> SQL_Latin1_General_CI_AS.
> What I need to check during DTS operation to preserve SQL 7 code page in
> order to avoid weird characters in SQL 2000?
In SQL 2000, you can create a database with different collation than servers
default collation. So you can have a database on SQL 2000 with the same
collation that is present on SQL 7.0.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com

DTS - Strange Behavior

Hi,
I've created quite a few DTS packages that open up VPN connections to remote
SQL Servers, transfer data to local SQL Servers, writes audit records, then
close the VPN connections. Everything has been working well. Saturday all
of the packages (scheduled as jobs) began to fail. Friday we did do some
physical server migrations but the SQL Servers were not touched. Nothing
else has changed. When I examined the DTS packages I noticed that if I
execute the package manually none of the tasks ever execute; they stay in a
"Waiting" state with a yellow icon. This occurs for both Meta Data and SQL
Server stored packages. If I open the package in design mode, I get the
same "Waiting" behavior. Eventually an error is returned in either case
which states:
Error Source: Microsoft OLE DB Provider for SQL Server
Error Description: [DBNETLIB][ConnectionOpen(Connect)).] SQL Server does
not exist or access is denied.
After the error for each step it lists "Not Run" with a white X in a black
circle. In the past successful execution of the package would list success
(green icon) for each step moving down through the package as each step
succeeded (as expected).
The funny thing is if I open the package up in design mode and execute any
individual step, the the step executes successfully. I.e., individual steps
work but not the whole DTS package whether opened in design mode or not.
If I create a new test DTS package, the package works as expected. If I
save one of the existing DTS packages out to a DTS file and import it back
into DTS and save it under a different name, same failure behavior as listed
above. If I create a new DTS package and copy/paste the steps individually
(from a failing package) into a new package and then save it, the package
works as expected.
Any ideas? I need to get this working again because were talking about
millions of records. And preferably, I would rather not spend two days
recreating the same packages over again.
Thanks
Jerry
PS - The double post is in leu of time constraints.
Also,
This is Windows 2000 SP4 SQL Server 2000 SP4. No errors are reported in the
DTS error log.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23A1ebigFGHA.3700@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I've created quite a few DTS packages that open up VPN connections to
> remote SQL Servers, transfer data to local SQL Servers, writes audit
> records, then close the VPN connections. Everything has been working
> well. Saturday all of the packages (scheduled as jobs) began to fail.
> Friday we did do some physical server migrations but the SQL Servers were
> not touched. Nothing else has changed. When I examined the DTS packages
> I noticed that if I execute the package manually none of the tasks ever
> execute; they stay in a "Waiting" state with a yellow icon. This occurs
> for both Meta Data and SQL Server stored packages. If I open the package
> in design mode, I get the same "Waiting" behavior. Eventually an error is
> returned in either case which states:
> Error Source: Microsoft OLE DB Provider for SQL Server
> Error Description: [DBNETLIB][ConnectionOpen(Connect)).] SQL Server does
> not exist or access is denied.
> After the error for each step it lists "Not Run" with a white X in a black
> circle. In the past successful execution of the package would list
> success (green icon) for each step moving down through the package as each
> step succeeded (as expected).
> The funny thing is if I open the package up in design mode and execute any
> individual step, the the step executes successfully. I.e., individual
> steps work but not the whole DTS package whether opened in design mode or
> not.
> If I create a new test DTS package, the package works as expected. If I
> save one of the existing DTS packages out to a DTS file and import it back
> into DTS and save it under a different name, same failure behavior as
> listed above. If I create a new DTS package and copy/paste the steps
> individually (from a failing package) into a new package and then save it,
> the package works as expected.
> Any ideas? I need to get this working again because were talking about
> millions of records. And preferably, I would rather not spend two days
> recreating the same packages over again.
> Thanks
> Jerry
> PS - The double post is in leu of time constraints.
>
|||Also,
This is Windows 2000 SP4 SQL Server 2000 SP4. No errors are reported in the
DTS error log.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23A1ebigFGHA.3700@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I've created quite a few DTS packages that open up VPN connections to
> remote SQL Servers, transfer data to local SQL Servers, writes audit
> records, then close the VPN connections. Everything has been working
> well. Saturday all of the packages (scheduled as jobs) began to fail.
> Friday we did do some physical server migrations but the SQL Servers were
> not touched. Nothing else has changed. When I examined the DTS packages
> I noticed that if I execute the package manually none of the tasks ever
> execute; they stay in a "Waiting" state with a yellow icon. This occurs
> for both Meta Data and SQL Server stored packages. If I open the package
> in design mode, I get the same "Waiting" behavior. Eventually an error is
> returned in either case which states:
> Error Source: Microsoft OLE DB Provider for SQL Server
> Error Description: [DBNETLIB][ConnectionOpen(Connect)).] SQL Server does
> not exist or access is denied.
> After the error for each step it lists "Not Run" with a white X in a black
> circle. In the past successful execution of the package would list
> success (green icon) for each step moving down through the package as each
> step succeeded (as expected).
> The funny thing is if I open the package up in design mode and execute any
> individual step, the the step executes successfully. I.e., individual
> steps work but not the whole DTS package whether opened in design mode or
> not.
> If I create a new test DTS package, the package works as expected. If I
> save one of the existing DTS packages out to a DTS file and import it back
> into DTS and save it under a different name, same failure behavior as
> listed above. If I create a new DTS package and copy/paste the steps
> individually (from a failing package) into a new package and then save it,
> the package works as expected.
> Any ideas? I need to get this working again because were talking about
> millions of records. And preferably, I would rather not spend two days
> recreating the same packages over again.
> Thanks
> Jerry
> PS - The double post is in leu of time constraints.
>
|||Got it. For future views of this post:
The issue lied in the fact that logging for the package execution was going
to another SQL Server that was taken down (testing box). Updating the
logging to the prod box (which should have occurred at the begining) fixed
the issue.
Jerry
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23A1ebigFGHA.3700@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I've created quite a few DTS packages that open up VPN connections to
> remote SQL Servers, transfer data to local SQL Servers, writes audit
> records, then close the VPN connections. Everything has been working
> well. Saturday all of the packages (scheduled as jobs) began to fail.
> Friday we did do some physical server migrations but the SQL Servers were
> not touched. Nothing else has changed. When I examined the DTS packages
> I noticed that if I execute the package manually none of the tasks ever
> execute; they stay in a "Waiting" state with a yellow icon. This occurs
> for both Meta Data and SQL Server stored packages. If I open the package
> in design mode, I get the same "Waiting" behavior. Eventually an error is
> returned in either case which states:
> Error Source: Microsoft OLE DB Provider for SQL Server
> Error Description: [DBNETLIB][ConnectionOpen(Connect)).] SQL Server does
> not exist or access is denied.
> After the error for each step it lists "Not Run" with a white X in a black
> circle. In the past successful execution of the package would list
> success (green icon) for each step moving down through the package as each
> step succeeded (as expected).
> The funny thing is if I open the package up in design mode and execute any
> individual step, the the step executes successfully. I.e., individual
> steps work but not the whole DTS package whether opened in design mode or
> not.
> If I create a new test DTS package, the package works as expected. If I
> save one of the existing DTS packages out to a DTS file and import it back
> into DTS and save it under a different name, same failure behavior as
> listed above. If I create a new DTS package and copy/paste the steps
> individually (from a failing package) into a new package and then save it,
> the package works as expected.
> Any ideas? I need to get this working again because were talking about
> millions of records. And preferably, I would rather not spend two days
> recreating the same packages over again.
> Thanks
> Jerry
> PS - The double post is in leu of time constraints.
>

DTS - Strange Behavior

Hi,
I've created quite a few DTS packages that open up VPN connections to remote
SQL Servers, transfer data to local SQL Servers, writes audit records, then
close the VPN connections. Everything has been working well. Saturday all
of the packages (scheduled as jobs) began to fail. Friday we did do some
physical server migrations but the SQL Servers were not touched. Nothing
else has changed. When I examined the DTS packages I noticed that if I
execute the package manually none of the tasks ever execute; they stay in a
"Waiting" state with a yellow icon. This occurs for both Meta Data and SQL
Server stored packages. If I open the package in design mode, I get the
same "Waiting" behavior. Eventually an error is returned in either case
which states:
Error Source: Microsoft OLE DB Provider for SQL Server
Error Description: [DBNETLIB][ConnectionOpen(Connect)).] SQL Server
does
not exist or access is denied.
After the error for each step it lists "Not Run" with a white X in a black
circle. In the past successful execution of the package would list success
(green icon) for each step moving down through the package as each step
succeeded (as expected).
The funny thing is if I open the package up in design mode and execute any
individual step, the the step executes successfully. I.e., individual steps
work but not the whole DTS package whether opened in design mode or not.
If I create a new test DTS package, the package works as expected. If I
save one of the existing DTS packages out to a DTS file and import it back
into DTS and save it under a different name, same failure behavior as listed
above. If I create a new DTS package and copy/paste the steps individually
(from a failing package) into a new package and then save it, the package
works as expected.
Any ideas? I need to get this working again because were talking about
millions of records. And preferably, I would rather not spend two days
recreating the same packages over again.
Thanks
Jerry
PS - The double post is in leu of time constraints.Also,
This is Windows 2000 SP4 SQL Server 2000 SP4. No errors are reported in the
DTS error log.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23A1ebigFGHA.3700@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I've created quite a few DTS packages that open up VPN connections to
> remote SQL Servers, transfer data to local SQL Servers, writes audit
> records, then close the VPN connections. Everything has been working
> well. Saturday all of the packages (scheduled as jobs) began to fail.
> Friday we did do some physical server migrations but the SQL Servers were
> not touched. Nothing else has changed. When I examined the DTS packages
> I noticed that if I execute the package manually none of the tasks ever
> execute; they stay in a "Waiting" state with a yellow icon. This occurs
> for both Meta Data and SQL Server stored packages. If I open the package
> in design mode, I get the same "Waiting" behavior. Eventually an error is
> returned in either case which states:
> Error Source: Microsoft OLE DB Provider for SQL Server
> Error Description: [DBNETLIB][ConnectionOpen(Connect)).] SQL Serv
er does
> not exist or access is denied.
> After the error for each step it lists "Not Run" with a white X in a black
> circle. In the past successful execution of the package would list
> success (green icon) for each step moving down through the package as each
> step succeeded (as expected).
> The funny thing is if I open the package up in design mode and execute any
> individual step, the the step executes successfully. I.e., individual
> steps work but not the whole DTS package whether opened in design mode or
> not.
> If I create a new test DTS package, the package works as expected. If I
> save one of the existing DTS packages out to a DTS file and import it back
> into DTS and save it under a different name, same failure behavior as
> listed above. If I create a new DTS package and copy/paste the steps
> individually (from a failing package) into a new package and then save it,
> the package works as expected.
> Any ideas? I need to get this working again because were talking about
> millions of records. And preferably, I would rather not spend two days
> recreating the same packages over again.
> Thanks
> Jerry
> PS - The double post is in leu of time constraints.
>|||Also,
This is Windows 2000 SP4 SQL Server 2000 SP4. No errors are reported in the
DTS error log.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23A1ebigFGHA.3700@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I've created quite a few DTS packages that open up VPN connections to
> remote SQL Servers, transfer data to local SQL Servers, writes audit
> records, then close the VPN connections. Everything has been working
> well. Saturday all of the packages (scheduled as jobs) began to fail.
> Friday we did do some physical server migrations but the SQL Servers were
> not touched. Nothing else has changed. When I examined the DTS packages
> I noticed that if I execute the package manually none of the tasks ever
> execute; they stay in a "Waiting" state with a yellow icon. This occurs
> for both Meta Data and SQL Server stored packages. If I open the package
> in design mode, I get the same "Waiting" behavior. Eventually an error is
> returned in either case which states:
> Error Source: Microsoft OLE DB Provider for SQL Server
> Error Description: [DBNETLIB][ConnectionOpen(Connect)).] SQL Serv
er does
> not exist or access is denied.
> After the error for each step it lists "Not Run" with a white X in a black
> circle. In the past successful execution of the package would list
> success (green icon) for each step moving down through the package as each
> step succeeded (as expected).
> The funny thing is if I open the package up in design mode and execute any
> individual step, the the step executes successfully. I.e., individual
> steps work but not the whole DTS package whether opened in design mode or
> not.
> If I create a new test DTS package, the package works as expected. If I
> save one of the existing DTS packages out to a DTS file and import it back
> into DTS and save it under a different name, same failure behavior as
> listed above. If I create a new DTS package and copy/paste the steps
> individually (from a failing package) into a new package and then save it,
> the package works as expected.
> Any ideas? I need to get this working again because were talking about
> millions of records. And preferably, I would rather not spend two days
> recreating the same packages over again.
> Thanks
> Jerry
> PS - The double post is in leu of time constraints.
>|||Got it. For future views of this post:
The issue lied in the fact that logging for the package execution was going
to another SQL Server that was taken down (testing box). Updating the
logging to the prod box (which should have occurred at the begining) fixed
the issue.
Jerry
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23A1ebigFGHA.3700@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I've created quite a few DTS packages that open up VPN connections to
> remote SQL Servers, transfer data to local SQL Servers, writes audit
> records, then close the VPN connections. Everything has been working
> well. Saturday all of the packages (scheduled as jobs) began to fail.
> Friday we did do some physical server migrations but the SQL Servers were
> not touched. Nothing else has changed. When I examined the DTS packages
> I noticed that if I execute the package manually none of the tasks ever
> execute; they stay in a "Waiting" state with a yellow icon. This occurs
> for both Meta Data and SQL Server stored packages. If I open the package
> in design mode, I get the same "Waiting" behavior. Eventually an error is
> returned in either case which states:
> Error Source: Microsoft OLE DB Provider for SQL Server
> Error Description: [DBNETLIB][ConnectionOpen(Connect)).] SQL Serv
er does
> not exist or access is denied.
> After the error for each step it lists "Not Run" with a white X in a black
> circle. In the past successful execution of the package would list
> success (green icon) for each step moving down through the package as each
> step succeeded (as expected).
> The funny thing is if I open the package up in design mode and execute any
> individual step, the the step executes successfully. I.e., individual
> steps work but not the whole DTS package whether opened in design mode or
> not.
> If I create a new test DTS package, the package works as expected. If I
> save one of the existing DTS packages out to a DTS file and import it back
> into DTS and save it under a different name, same failure behavior as
> listed above. If I create a new DTS package and copy/paste the steps
> individually (from a failing package) into a new package and then save it,
> the package works as expected.
> Any ideas? I need to get this working again because were talking about
> millions of records. And preferably, I would rather not spend two days
> recreating the same packages over again.
> Thanks
> Jerry
> PS - The double post is in leu of time constraints.
>

DTS - Strange Behavior

Hi,
I've created quite a few DTS packages that open up VPN connections to remote
SQL Servers, transfer data to local SQL Servers, writes audit records, then
close the VPN connections. Everything has been working well. Saturday all
of the packages (scheduled as jobs) began to fail. Friday we did do some
physical server migrations but the SQL Servers were not touched. Nothing
else has changed. When I examined the DTS packages I noticed that if I
execute the package manually none of the tasks ever execute; they stay in a
"Waiting" state with a yellow icon. This occurs for both Meta Data and SQL
Server stored packages. If I open the package in design mode, I get the
same "Waiting" behavior. Eventually an error is returned in either case
which states:
Error Source: Microsoft OLE DB Provider for SQL Server
Error Description: [DBNETLIB][ConnectionOpen(Connect)).] SQL Server does
not exist or access is denied.
After the error for each step it lists "Not Run" with a white X in a black
circle. In the past successful execution of the package would list success
(green icon) for each step moving down through the package as each step
succeeded (as expected).
The funny thing is if I open the package up in design mode and execute any
individual step, the the step executes successfully. I.e., individual steps
work but not the whole DTS package whether opened in design mode or not.
If I create a new test DTS package, the package works as expected. If I
save one of the existing DTS packages out to a DTS file and import it back
into DTS and save it under a different name, same failure behavior as listed
above. If I create a new DTS package and copy/paste the steps individually
(from a failing package) into a new package and then save it, the package
works as expected.
Any ideas? I need to get this working again because were talking about
millions of records. And preferably, I would rather not spend two days
recreating the same packages over again.
Thanks
Jerry
PS - The double post is in leu of time constraints.Also,
This is Windows 2000 SP4 SQL Server 2000 SP4. No errors are reported in the
DTS error log.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23A1ebigFGHA.3700@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I've created quite a few DTS packages that open up VPN connections to
> remote SQL Servers, transfer data to local SQL Servers, writes audit
> records, then close the VPN connections. Everything has been working
> well. Saturday all of the packages (scheduled as jobs) began to fail.
> Friday we did do some physical server migrations but the SQL Servers were
> not touched. Nothing else has changed. When I examined the DTS packages
> I noticed that if I execute the package manually none of the tasks ever
> execute; they stay in a "Waiting" state with a yellow icon. This occurs
> for both Meta Data and SQL Server stored packages. If I open the package
> in design mode, I get the same "Waiting" behavior. Eventually an error is
> returned in either case which states:
> Error Source: Microsoft OLE DB Provider for SQL Server
> Error Description: [DBNETLIB][ConnectionOpen(Connect)).] SQL Server does
> not exist or access is denied.
> After the error for each step it lists "Not Run" with a white X in a black
> circle. In the past successful execution of the package would list
> success (green icon) for each step moving down through the package as each
> step succeeded (as expected).
> The funny thing is if I open the package up in design mode and execute any
> individual step, the the step executes successfully. I.e., individual
> steps work but not the whole DTS package whether opened in design mode or
> not.
> If I create a new test DTS package, the package works as expected. If I
> save one of the existing DTS packages out to a DTS file and import it back
> into DTS and save it under a different name, same failure behavior as
> listed above. If I create a new DTS package and copy/paste the steps
> individually (from a failing package) into a new package and then save it,
> the package works as expected.
> Any ideas? I need to get this working again because were talking about
> millions of records. And preferably, I would rather not spend two days
> recreating the same packages over again.
> Thanks
> Jerry
> PS - The double post is in leu of time constraints.
>|||Also,
This is Windows 2000 SP4 SQL Server 2000 SP4. No errors are reported in the
DTS error log.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23A1ebigFGHA.3700@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I've created quite a few DTS packages that open up VPN connections to
> remote SQL Servers, transfer data to local SQL Servers, writes audit
> records, then close the VPN connections. Everything has been working
> well. Saturday all of the packages (scheduled as jobs) began to fail.
> Friday we did do some physical server migrations but the SQL Servers were
> not touched. Nothing else has changed. When I examined the DTS packages
> I noticed that if I execute the package manually none of the tasks ever
> execute; they stay in a "Waiting" state with a yellow icon. This occurs
> for both Meta Data and SQL Server stored packages. If I open the package
> in design mode, I get the same "Waiting" behavior. Eventually an error is
> returned in either case which states:
> Error Source: Microsoft OLE DB Provider for SQL Server
> Error Description: [DBNETLIB][ConnectionOpen(Connect)).] SQL Server does
> not exist or access is denied.
> After the error for each step it lists "Not Run" with a white X in a black
> circle. In the past successful execution of the package would list
> success (green icon) for each step moving down through the package as each
> step succeeded (as expected).
> The funny thing is if I open the package up in design mode and execute any
> individual step, the the step executes successfully. I.e., individual
> steps work but not the whole DTS package whether opened in design mode or
> not.
> If I create a new test DTS package, the package works as expected. If I
> save one of the existing DTS packages out to a DTS file and import it back
> into DTS and save it under a different name, same failure behavior as
> listed above. If I create a new DTS package and copy/paste the steps
> individually (from a failing package) into a new package and then save it,
> the package works as expected.
> Any ideas? I need to get this working again because were talking about
> millions of records. And preferably, I would rather not spend two days
> recreating the same packages over again.
> Thanks
> Jerry
> PS - The double post is in leu of time constraints.
>|||Got it. For future views of this post:
The issue lied in the fact that logging for the package execution was going
to another SQL Server that was taken down (testing box). Updating the
logging to the prod box (which should have occurred at the begining) fixed
the issue.
Jerry
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23A1ebigFGHA.3700@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I've created quite a few DTS packages that open up VPN connections to
> remote SQL Servers, transfer data to local SQL Servers, writes audit
> records, then close the VPN connections. Everything has been working
> well. Saturday all of the packages (scheduled as jobs) began to fail.
> Friday we did do some physical server migrations but the SQL Servers were
> not touched. Nothing else has changed. When I examined the DTS packages
> I noticed that if I execute the package manually none of the tasks ever
> execute; they stay in a "Waiting" state with a yellow icon. This occurs
> for both Meta Data and SQL Server stored packages. If I open the package
> in design mode, I get the same "Waiting" behavior. Eventually an error is
> returned in either case which states:
> Error Source: Microsoft OLE DB Provider for SQL Server
> Error Description: [DBNETLIB][ConnectionOpen(Connect)).] SQL Server does
> not exist or access is denied.
> After the error for each step it lists "Not Run" with a white X in a black
> circle. In the past successful execution of the package would list
> success (green icon) for each step moving down through the package as each
> step succeeded (as expected).
> The funny thing is if I open the package up in design mode and execute any
> individual step, the the step executes successfully. I.e., individual
> steps work but not the whole DTS package whether opened in design mode or
> not.
> If I create a new test DTS package, the package works as expected. If I
> save one of the existing DTS packages out to a DTS file and import it back
> into DTS and save it under a different name, same failure behavior as
> listed above. If I create a new DTS package and copy/paste the steps
> individually (from a failing package) into a new package and then save it,
> the package works as expected.
> Any ideas? I need to get this working again because were talking about
> millions of records. And preferably, I would rather not spend two days
> recreating the same packages over again.
> Thanks
> Jerry
> PS - The double post is in leu of time constraints.
>

DTS - Recovery Model

SQL Server 2000 SP3.
Prior to SP3 the recovery model was switched to simple during transfer (Copy object task) and changed back to the previouis setting after DTS was complete.
Nice thing because performance was increased and T-Log was keep small.

Now I assume that the recovery model is switched to bulk-logged causing the T-Log to explode, to be onest not in all my databases.

1.Is my interpretation regarding recovery model correct?
2.Does anybody knows the reason of this change?

Any suggestion is really appreciate.
Thank you very much - kind regards.

FrancoYikes!

If I read this correctly:

1. You have a DTS package
2. The DTS package changes the Database logging option from Full (or something other than Simple) to Simple
3. You import your data
4. The DTS package then switches the Database logging option back to its original setting

As far as I know, nothing regarding the database recovery model was changed in SP3. There were, however, many security changes to SP3. It may be possible that the switch to the Simple model is failing due to a permissions issue (the context under which DTS is running may not be permitted to alter database settings).

You do realize, of course, that your backups and transaction logs are useless after the DTS package runs? I mean, you can recover your data up to the point that the DTS package starts, but everything after that is toast?

I'd like to ask others on the forum if this is a common practice; I use full logging myself and I do run into issues with an oversized transaction log, but I have it on a separate partition and I generally don't worry about it too much. I'd be interested to hear other experiences.

Hugh Scott
Originally posted by franco
SQL Server 2000 SP3.
Prior to SP3 the recovery model was switched to simple during transfer (Copy object task) and changed back to the previouis setting after DTS was complete.
Nice thing because performance was increased and T-Log was keep small.

Now I assume that the recovery model is switched to bulk-logged causing the T-Log to explode, to be onest not in all my databases.

1.Is my interpretation regarding recovery model correct?
2.Does anybody knows the reason of this change?

Any suggestion is really appreciate.
Thank you very much - kind regards.

Franco|||This is the explanation I have from another forum that makes light on the subject:

The problem was that as well as switching on select into/bulk copy it also performed a

dump tran databasename with no_log

This invalidated your log backup chain and required a full database backup to be performed immediately to maintain recoverability. It was never intended to do this and there was no real indication on this (it was in the event log but it was not a documented side effect of the copy objects task) so this bug was fixed in SP3. As the transaction log is no longer truncated the log chain is kept valid. However it still uses bcp functionality and should be minimally logged BUT your transaction log backups will be much bigger. I have done testing to see that this was fixed but must say I haven't checked the log sizes. I will do some testing and post back. Hopefully my above rambling explains why this change was made.

HTH
Jasper Smith|||RE:
Yikes!

If I read this correctly:

1. You have a DTS package
2. The DTS package changes the Database logging option from Full (or something other than Simple) to Simple
3. You import your data
4. The DTS package then switches the Database logging option back to its original setting

Q1 I'd like to ask others on the forum if this is a common practice; I use full logging myself and I do run into issues with an oversized transaction log, but I have it on a separate partition and I generally don't worry about it too much. I'd be interested to hear other experiences. Hugh Scott

A1 As with many things, much depends on available resources, the nature of the application / purpose of the DB, the size / importance / purpose of the loads involved, etc., etc., etc..

For example, in some mission critical (and resource / budget constrained) production environments sometimes load schemes amount to something like: Full (final TL dump) --> Simple (dbo / single user) --> (perform data loads) --> Diff dump --> Full (multi user). Obviously, if large numbers of OLTP inserts / updates, etc. must be allowed in a fully logged fashion (7/24) such a scheme is not appropriate.

Dts

I have 3 separate DTS to transfer tables.
They transfer tables from 3 different Access databases
into one SQL server database.
But I would like to make them into one dTS so that I can
just check on error log file.
How can I do this?
Thanks for any suggestions.Basic copy-pasting of the entire contents of your DTS packages into one would do the trick because a DTS can have many separate connections and many "tasks" even if they are not related.

Now, you might want to keep them separate because if one fails (because of data transformation for example), you could still run the other two. If you keep them separate, use 'dtsrun' with the SQL Server Agent to run all your packages in different steps (and setting the "On failure" option to "Go to the next step") hence -kind of- grouping them together.

Good luck,

SC|||You can enable DTS package logging as specified by BOL:
To enable package logging

Open the Data Transformation Services (DTS) package for which you want to create a log.

On the Package menu, click Properties to display the DTS Package Properties dialog box.

Do one of the following:
Save package logs to Microsoft SQL Server by clicking the Logging tab, selecting the Log package execution to SQLServer check box, and then clicking an available server on which to save the package logs.

Security Note When possible, use Windows Authentication.

Save package logs to SQL Server 2000 Meta Data Services by clicking the Advanced tab, and then selecting the Show lineage variables as source columns and Write lineage to repository check boxes. On the Package menu, click Save As, and then in the Save DTS Package dialog box, in the Location list, select Meta Data Services.
... which will help you to assess the package(s) information.|||Thanks for all your responses.

DTS

Hi,
(i).I wanted to transfer data from SQL server to access through code.
or Activex Script.
(ii) how can i devide a table in two parts means one table but
vertually two tables.
from
Killer
doller
www.sqldts.com
"doller" <sufianarif@.gmail.com> wrote in message
news:1125895448.595557.6480@.g44g2000cwa.googlegrou ps.com...
> Hi,
> (i).I wanted to transfer data from SQL server to access through code.
> or Activex Script.
> (ii) how can i devide a table in two parts means one table but
> vertually two tables.
>
> from
> Killer
>
|||Hi Uri,
Canu pls tell me the exact link to that page where sqldts.com have
informagtion about me question.
And what about my second question.
from
doller
|||(i).I wanted to transfer data from SQL server to access through code.
or Activex Script
"doller" <sufianarif@.gmail.com> wrote in message
news:1125988732.096701.123580@.z14g2000cwz.googlegr oups.com...
> Hi Uri,
> Canu pls tell me the exact link to that page where sqldts.com have
> informagtion about me question.
> And what about my second question.
> from
> doller
>

DTS

What are you talking about?
I started a transfer with the DTS wizard then I
>realized that I had the name spelled wrong in the to part
Whats the to part?

>Cannot create file 'C:\MSSQL7\DATA\dbGrade.mdf' because
>it alredy exists.
Then dont try to re-create it.

>When I check the test server this file does exist.
>What do I need to do to get my transfer to go?
>Can I just delete the above mentioned file?
>What is the purpose of the data folder?
Of course you can. But keep in mind this is a db file. It
sounds though as you just created it so it's probably
empty. Drop the db from Enterprise Manager.
Why are you exporting to a file anyways?

>--Original Message--
>I am transfering data from a production server to a test
>sever. I started a transfer with the DTS wizard then I
>realized that I had the name spelled wrong in the to part
>so I removed that db from the drop down list. Now when I
>try to do another transfer I get the following error
>message.
>Cannot create file 'C:\MSSQL7\DATA\dbGrade.mdf' because
>it alredy exists.
>When I check the test server this file does exist.
>What do I need to do to get my transfer to go?
>Can I just delete the above mentioned file?
>What is the purpose of the data folder?
>.
>
The to part that I was refering to is on the Chose Destination page of the DTS wizard in the database drop down list. In that list it has a new option. After selecting that I typed in the name of the database that I want to create with the data transfer
. I am not trying to create the file that gives me the error I assume that this is something that is created by the DTS.

DTS

I used the DTS to transfer data to a new server, I received an error message "Bulk copiy failure", but in checking my destination datanbase data was indeed transfered.
Is it safe to assume that there are no errors, or what is the source of the error?
Thanks
You can receive this error if there are blank lines in the end of the
file you are transferring and the destination has non-nullable columns.
You should try to eliminate blank rows from the source file, use an
ActiveX task to skip rows that are blank or bring the data into a
holding table with all nullable columns then copy the data from there to
your real tables.
Regards,
William D. Bartholomew
http://blog.bartholomew.id.au/
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

DTS

How do we configure DTS package to transfer more than 1 table data transfer from a Source to Destination Server?
I have about 100 odd tables in Source & need to do data transfer for all within 1 go. Is this possible?If you are on SQL Server 2000 and the source and destination
are both SQL Server(7 or above), you can use the Copy SQL
Server Objects task.
-Sue
On Wed, 5 Nov 2003 13:11:07 -0800, "Prasanna"
<anonymous@.discussions.microsoft.com> wrote:
>How do we configure DTS package to transfer more than 1 table data transfer from a Source to Destination Server?
>I have about 100 odd tables in Source & need to do data transfer for all within 1 go. Is this possible?|||The simplest way is USe the DTS wizard.
or
In , SQLEM,go to "Tools" and then CLick "DTS" and then Use
the Export/Import. (This will also get you the DTS Wizard)
Very simple and user friendly wizard.You can easily do it.
SQLVarad(MCDBA-1999,MCSE-1999)
>--Original Message--
>If you are on SQL Server 2000 and the source and
destination
>are both SQL Server(7 or above), you can use the Copy SQL
>Server Objects task.
>-Sue
>On Wed, 5 Nov 2003 13:11:07 -0800, "Prasanna"
><anonymous@.discussions.microsoft.com> wrote:
>>How do we configure DTS package to transfer more than 1
table data transfer from a Source to Destination Server?
>>I have about 100 odd tables in Source & need to do data
transfer for all within 1 go. Is this possible?
>.
>

DTS

I am transfering data from a production server to a test
sever. I started a transfer with the DTS wizard then I
realized that I had the name spelled wrong in the to part
so I removed that db from the drop down list. Now when I
try to do another transfer I get the following error
message.
Cannot create file 'C:\MSSQL7\DATA\dbGrade.mdf' because
it alredy exists.
When I check the test server this file does exist.
What do I need to do to get my transfer to go?
Can I just delete the above mentioned file?
What is the purpose of the data folder?What are you talking about?
I started a transfer with the DTS wizard then I
>realized that I had the name spelled wrong in the to part
Whats the to part?
>Cannot create file 'C:\MSSQL7\DATA\dbGrade.mdf' because
>it alredy exists.
Then dont try to re-create it.
>When I check the test server this file does exist.
>What do I need to do to get my transfer to go?
>Can I just delete the above mentioned file?
>What is the purpose of the data folder?
Of course you can. But keep in mind this is a db file. It
sounds though as you just created it so it's probably
empty. Drop the db from Enterprise Manager.
Why are you exporting to a file anyways?
>--Original Message--
>I am transfering data from a production server to a test
>sever. I started a transfer with the DTS wizard then I
>realized that I had the name spelled wrong in the to part
>so I removed that db from the drop down list. Now when I
>try to do another transfer I get the following error
>message.
>Cannot create file 'C:\MSSQL7\DATA\dbGrade.mdf' because
>it alredy exists.
>When I check the test server this file does exist.
>What do I need to do to get my transfer to go?
>Can I just delete the above mentioned file?
>What is the purpose of the data folder?
>.
>|||The to part that I was refering to is on the Chose Destination page of the DTS wizard in the database drop down list. In that list it has a new option. After selecting that I typed in the name of the database that I want to create with the data transfer. I am not trying to create the file that gives me the error I assume that this is something that is created by the DTS.