DTS Question

Hi,

I made a DTS which appends data coming infrom a view to an exisiting table.So far no problem and all goes well.

I am facing a problem due to the format of the date that is coming in (getting appended) and while going through BOL, came across the following topic:

mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%2 0Server\80\Tools\Books\howtosql.chm::/ht_dts_trns_97ou.htm

I tired the above tips but it appears that if I try to do this in my DTS (which appends data),the logic of the DTS will change. A single arrow also gets added whichI think represents a simple mapping/transformation rather than a append. To clarify my point, please note the attached image which represents that the data is being appended (due to the many sided arrows pointing to the source and destination - visible under the Transformations tab of my DTS).

Sincerely hoping that my post is clear, can someone help me find how to make changes in a DTS (which appends data) and ensuring that thelogic remains the same i.e. it should append data.

Many TIAHow exactly is your current DTS package appending the destination table ? Do you have some logic right in the transformation ?

Cheers,
-Kilka|||the only thing your diagram shows is that you have a transformation that has more source columns then destination columns and there is nothing wrong with that.

As for the appending of data, well that really depends on what you are doing prior to your transformation. Are you deleting the existing data or not? This isn't really part of the transformation itself...

DTS packet size

I have a dts package that transfers data from a view into
a table structure on another server. Occassionly the
scheduled package will fail with the following error:

Error = -2147467259 (80004005) Error string:
[DBNETLIB][ConnectionRead (recv()).]General network error.
Check your network documentation. Error source:
Microsoft OLE DB Provider for SQL Server Help
file: Help context: 0 Error Detail
Records: Error: -2147467259 (80004005); Provider
Error: 11 (B) Error string: [DBNETLIB]
[ConnectionRead (recv()).]General network error. Check
your network documentation. Error source: Microsoft
OLE DB Provider for SQL Server Help file: Help
context: 0 DTSRun OnFinish: Copy Data from PS...
Process Exit Code 1. The step failed.

Once the package fails I will rerun it several times
without any modifications and eventually it will run to
completion successfully. One change that I have made while
troubleshooting is to change the default network package
size from 1 to 1024 or 2048. Although this has worked I'm
not confident that the package wouldn't have run without
that change.

Has anyone seen this error before or anything similar?Default network packet size is 4096. How did you manage to make it 1?|||Under the Data Link Connection tab the default packet size is 1. It gives you the option to Edit the value so I bumped it up to 2048.

DTS Package Owner

I have to modify the DTS package owner that is displayed in the local
packages view, the current owner no longer works for the company so I need
to reassign ownership. I am not a sql expert by any means.
Can anyone help me with this?
It is SQL Server 2000 sp4 running on Windows 2003 sp1.
Paul Bergson
http://www.pbbergs.com
Please no e-mails, any questions should be posted in the NewsGroup
This posting is provided "AS IS" with no warranties, and confers no rights.
Paul - please take a look here: http://www.sqldts.com/default.aspx?212
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||This is what I was looking for.
Thanks
Paul Bergson
http://www.pbbergs.com
Please no e-mails, any questions should be posted in the NewsGroup
This posting is provided "AS IS" with no warranties, and confers no rights.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:ulsvIajEHHA.992@.TK2MSFTNGP03.phx.gbl...
> Paul - please take a look here: http://www.sqldts.com/default.aspx?212
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>

DTS Package Owner

Hello there,

Im trying to change the owership of the DTS package but am a little
confused.

sp_changeobjectowner changes the ownership of table, view, or stored
procedure in the current database...

How could i change the DTS package ownership?

Thank you in advance.

Leo

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Leo Kozhushnik <anonymous@.devdex.com> wrote in message news:<3f1390c1$0$202$75868355@.news.frii.net>...
> Hello there,
> Im trying to change the owership of the DTS package but am a little
> confused.
> sp_changeobjectowner changes the ownership of table, view, or stored
> procedure in the current database...
> How could i change the DTS package ownership?
> Thank you in advance.
> Leo

In most cases, it doesn't really matter who owns the package - who is
executing it is more important. However, there is an undocumented
stored procedure called msdb..sp_reassign_dtspackageowner, which you
can use.

You might want to look at http://www.sqldts.com - in the
Administration and Management section of the FAQ, there is an article
on package ownership issues, including a wrapper procedure for
sp_reassign_dtspackageowner.

Of course, since this procedure is undocumented, you should be careful
using it, and preferably never in production code - its behaviour may
change in future releases with no warning.

Simon|||Leo Kozhushnik <anonymous@.devdex.com> wrote in message news:<3f1390c1$0$202$75868355@.news.frii.net>...
> Hello there,
> Im trying to change the owership of the DTS package but am a little
> confused.
> sp_changeobjectowner changes the ownership of table, view, or stored
> procedure in the current database...
> How could i change the DTS package ownership?
> Thank you in advance.
> Leo
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

Hi Leo, use the undocumented SP:

sp_reassign_dtspackageowner [@.name =] 'name',
[@.id =] 'id',
[@.newloginname =] 'newloginname'

[@.name =] 'name'
The package name.

[@.id =] 'id'
This is the uniqueidentifier for the package. A name may not
necessarily be unique.

[@.newloginname =] 'newloginname'
The new Owner name. SQL Server login example 'sa', NT Integrated
example 'Domain\Username'

Regards,
Thomas
http://www.sqlscripter.com

DTS Package Owner

I have to modify the DTS package owner that is displayed in the local
packages view, the current owner no longer works for the company so I need
to reassign ownership. I am not a sql expert by any means.
Can anyone help me with this?
It is SQL Server 2000 sp4 running on Windows 2003 sp1.
--
Paul Bergson
http://www.pbbergs.com
Please no e-mails, any questions should be posted in the NewsGroup
This posting is provided "AS IS" with no warranties, and confers no rights.Paul - please take a look here: http://www.sqldts.com/default.aspx?212
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||This is what I was looking for.
Thanks
--
Paul Bergson
http://www.pbbergs.com
Please no e-mails, any questions should be posted in the NewsGroup
This posting is provided "AS IS" with no warranties, and confers no rights.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:ulsvIajEHHA.992@.TK2MSFTNGP03.phx.gbl...
> Paul - please take a look here: http://www.sqldts.com/default.aspx?212
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>

DTS Package Owner

I have to modify the DTS package owner that is displayed in the local
packages view, the current owner no longer works for the company so I need
to reassign ownership. I am not a sql expert by any means.
Can anyone help me with this?
It is SQL Server 2000 sp4 running on Windows 2003 sp1.
Paul Bergson
http://www.pbbergs.com
Please no e-mails, any questions should be posted in the NewsGroup
This posting is provided "AS IS" with no warranties, and confers no rights.Paul - please take a look here: http://www.sqldts.com/default.aspx?212
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||This is what I was looking for.
Thanks
Paul Bergson
http://www.pbbergs.com
Please no e-mails, any questions should be posted in the NewsGroup
This posting is provided "AS IS" with no warranties, and confers no rights.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:ulsvIajEHHA.992@.TK2MSFTNGP03.phx.gbl...
> Paul - please take a look here: http://www.sqldts.com/default.aspx?212
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>

DTS Package Error: Login failed for user sa

I am trying to edit a DTS package which is a transfer of data between two tables (connections)

i can view the properties of either connection but i get this error when i try to edit the transformation section of a pakcage that is supposed to transfer data from one table to another.

Login failed for user 'sa'

i am running enterprise manager on the server itself so i shouldn't have any connection issues.

if i try to change the authentication on either connection from "use SQL authentication" to "use windows NT authentication" i get this error:

' Cannot generate SSPI context 'Note: There are too many unknowns here to intelligently troubleshoot the situation:

S1 [I am trying to edit a DTS package which is a transfer of data between two tables (connections)]
Q1 Are both tables on the same server or on different servers?

S2 [i can view the properties of either connection but i get this error when i try to edit the transformation section of a pakcage that is supposed to transfer data from one table to another.
Login failed for user 'sa' i am running enterprise manager on the server itself so i shouldn't have any connection issues.]
Q2 i Have you tried entering the login password for sa
ii What is the result then?
iii Can you sucessfully login using say, Query Analyzer as sa?

S3 if i try to change the authentication on either connection from "use SQL authentication" to "use windows NT authentication" i get this error:' Cannot generate SSPI context '
Q3 Are your Sql Server(s) set to use integrated, standard, mixed, etc. authentication?

DTS Package

Hi,

I create DTS Package, when I do the Transform Data Task Properties, the Table/View (Source Tab), and Table Name (Destination Tab), how can I see the whole table name? Especially, Table Name in Destination Tab, it show [database name].[dbo].[table name], I only see the 1st char of the table name in the dropdown list. Could you please let me know how can I resize the dropdownlist in order to see the table name?

Thanks.If you select that column on destination tab, using key board left arrow key you can move to the end of the value.

DTS only copies first row

Hi,

I'm trying to copy results from a view on one table to another table in a different db using DTS in SQL Server Management Studio

Both source and destination db's are version 8.0.194

Everything reports as working, but when I check the results, only one row has copied - DTS reports that 1167 rows were copied.

This happens to different sources and destinations so I doubt it's my query.

When I perfrom the same DTS task using the old enterprise manager DTS in SQL Server 2000, everything copies fine.

Obviously one answer is "Use SQL Server 2000 Enterprise Manager then. " but I'd rather upgrade to 2005 and Management Studio permanently.

Any ideas?

I don't know what the problem may be but you need to identify exactly where the problem is occurring. My first avenue of investigation would be to put a data viewer immediately prior to your destiantion to see how many rows are flowing into it. Check that the number displayed in teh GUI is correct.

-Jamie