DTS packages fail after server migration

Hi All
My DTS packages fail after i have migrated my sql server from a NT based
network to a windows 2003 based network and unattached the old domain, I
know that the users that was once on the server as %old domain%\user will no
longer have any rights on the sql server, But how can i change the ownership
of those DTS packages to %New Domain%\New AD User.?
some input would be great.
thanksHave you checked SQLDTS.com. I believe package ownership issues are covered
on that site.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Todd" <Todd_c_wright@.hotmail.com> wrote in message
news:uVew3tSDEHA.3348@.TK2MSFTNGP11.phx.gbl...
Hi All
My DTS packages fail after i have migrated my sql server from a NT based
network to a windows 2003 based network and unattached the old domain, I
know that the users that was once on the server as %old domain%\user will no
longer have any rights on the sql server, But how can i change the ownership
of those DTS packages to %New Domain%\New AD User.?
some input would be great.
thanks|||You can change the owner using the undocumented
sp_reassign_dtspackageowner but the package ownership
probably has nothing to do with the packages failing. You
can sometimes run into issues when someone who isn't the
owner and isn't a sysadmin attempts to modify and save
packages owned by someone else but that's about it.
You can find more info on this at the site Vyas referred you
to:
Package Ownership Issues
http://www.sqldts.com/default.aspx?212
Are these scheduled packages that are failing or do they
fail when run manually as well? What are the errors?
-Sue
On Fri, 19 Mar 2004 09:56:27 +1300, "Todd"
<Todd_c_wright@.hotmail.com> wrote:

>Hi All
>My DTS packages fail after i have migrated my sql server from a NT based
>network to a windows 2003 based network and unattached the old domain, I
>know that the users that was once on the server as %old domain%\user will n
o
>longer have any rights on the sql server, But how can i change the ownershi
p
>of those DTS packages to %New Domain%\New AD User.?
> some input would be great.
> thanks
>

DTS package behaviour when used by > 1 User?

Hi all

I have a DTS package that users of the database can run which basically acts like a 'live update' (as the database is based on a values produced from another system) and it takes roughly 30 or so seconds to run...

The dts package is not going to have a particularly large hit rate but i am interested in knowing what will happen if a user (user 1) attempts to run the package when it is in already in use by another user (user 2) ?

Will User 1 simply have to wait untill the package completes for User 1?

OR

Will a new seperate 'instance' of the package be used by User 1?

Any info would be very helpful

CheersYou will have problems
the second DTS will start before the first stop

(maybe it's not problem it depends what they do)|||That was the answer i was hoping for really.

Basically this shouldn't be a problem for my scenario. The Users of the System are accountants scattered at various locations around the country and each of them Updates the their own locations data (via the dts package)and so this will not conflict with any other user updating the DB as it is a different locations data.

I was worried that if the user had to wait for the dts package to finish then i may occasionally have timeout issues (not that i haven't got a backup for this).

Thanks for your help, mucho appreciated.

DTS package "Index Not Found Error"

The Scenario is we have a development machine that is actually a virtual machine and we have a test server. Virtual machine is XP professional based machine and Test Server is Windows Server 2003 based machine.

We have created a DTS package in Sql Server 2000 which copies data from 7 foxpro .dbf files to Sql Server 2000 based Database. This package when we test at our Development machine(Virtual Machine) works very fine, but when same DTS package is executed at Test Server it gives an Error, Below is the error which we found in DTS Logs.

Package failed because Step 'DTSStep_DTSDataPumpTask_1' failed.
**************************************************************************
Step Error Source: Microsoft JET Database Engine
Step Error Description:Index not found.
Step Error code: 80040E21
Step Error Help File:
Step Error Help Context ID:5003015
***********************************************************************

All the tables are located at the same place. and in both the machine source path remains the same for DTS package.
One observation is that some time on test server DTS package copies data from 2 or 3 tables but fails for remaining and sometime it may fail for all.
Checks have been made to ensure that there is no access violation issue.

Looking forward for a quick Help

Thanks

Pranjal Sharma

One of the reason:

A Data Transformation Services (DTS) package that was created on a Microsoft Windows 2000 computer may not run when scheduled on a server without Microsoft Data Access Components (MDAC) version 2.5 installed. The same problem occurs if you run the package on the server with DTSRun

|||

But we have created the DTS package in Windows XP professional and not on Windows 2000 machine. One more interesting thing is that among 7 tasks few execute and few fail. I hope this would help you in thinking further about the query.

Thanks

Pranjal Sharma

|||You might need to check the MDAC version of server and on the machine where these DTS packages were created, also look at the DTS log for more information on this failure.|||

I tried checking the Versions. on the development machine MDAC version is 2.81 and on Test server it is 2.82. The log gives error which i provided in my first post.

But one thing i observed is that, If we give a select query which contains a Where clause, Then DTS package gives error and if we use select * from <tablename> then DTS is executed properly.

DTS Package

I have created a DTS package that connects to the database fills a temp table with data based on my sql statement, generates a text file with the data in the temp table, and after the file is generated the temp table to truncated. The package has been added as a job to run daily, but the problem is sometimes the job will not generate data in the text file. I get an email with the file attached as soon as the job ends, and if i see that no data is in the file I run the package manually, and for what ever reason the file will then have data. Can someone tell me why the job will sometimes pull data, and sometimes not. But manualy running it will always pull data.

Thanks in adavance for helping!!

Is it possible that the dependency for the text file generation is not quite 'right', and that the file is generated before the temp table is completed?|||Also enable the DTS package log to see the underlying events during the data administration.

DTS load master file to different tables

We need to load a "master" flat file to SQL Server tables. The file is a dump from mainframe. Based on a field called "record_type", each record in the file has different columns. I would use the following as an example (the real file is much more complicated than this, but you get the idea):

For instance, my file has:

20M02221984PAPepsi1000
23F11121987MD1000
01M09182003TXCocacola1100
34F03041970DC900

If "M", the fields are "age", "gender", "birthdate", "state", "salary"

If "F", the fields are "age", "gender", "birthdate", "state", "company", "salary"

We need to load the file (only one file) into two different tables, M_table, and F_table. But I have researched and discovered in DTS the source (TEXT file) can not be queried against to filter on the gender field.

Since each record may have different number of fields, I cannot really load the flat file into a "staging" table.

Does anyone has any idea on how to achieve this? Thanks in advance!!!It should be:

If "F", the fields are "age", "gender", "birthdate", "state", "salary"

If "M", the fields are "age", "gender", "birthdate", "state", "company", "salary"|||Since your data sets are obviously not fixed-length, you could use a temporary master table into which you import all data sets, e.g. into only one column of varchar(nnn).

Then use queries with string-functions to split the data into the correct number and type of columns for the respective tables M_table and F_table.|||kbk's solution is probably simpler (and therefore usually better). You may also consider a Data Driven Query task. It won't be entirely straightforward and it will require a substantial amount of work in the ActiveX script component (and it's performance will be slower).

But other than these drawbacks, it may work!

Regards,

hmscott

DTS Global Variables Behaviour with > 1 User

Hi all

I have a DTS package that users of the database can run which basically acts like a 'live update' (as the database is based on a values produced from another system) and it takes roughly 30 or so seconds to run...

The dts package is not going to have a particularly large hit rate but i am interested in knowing what will happen if a user (user 1) attempts to run the package when it is in already in use by another user (user 2) ?

The dts package is from a sp using the dtsrun utility (passes in global variables).

I know that a second DTS will start before the first has finished but what i am not sure about is what will happen to the Global Variables when the second DTS package starts - i.e. will starting a second instance of the package with different variables have an effect on the first DTS's global variables while it is running?

I hope the answer is "No Tom, it work fine!"DTS is a weird beast to describe, because there are so many different contexts. The short answer is that each of the instances of a DTS package will have their own global variables.

-PatP|||Pat

Thanks for the help. I really appreciate it.

DTS Global Variables

Hello,

In the properties for a DTS package, I have a string variable. I need to execute an update statement based on this variable. I was going to use Execute SQL, but I didn't know now to get a value from the variables section.

I store the value there because I will be using dtsrun or the DTS library to execute the package, which I can change this value upon execution. How do I execute an update based on the value?

BrianAre you saying that you need to use this string variable in your WHERE clause? If so, you should be able to use a question mark (?) where you need to use that string value, then use the Parameters button to tell DTS which global variable to use for the parameter.

Terri|||Hey,

That was correct. THanks.

Brian|||Cool! I have found DTS to be one of the most powerful yet frustrating tools to use. Could they make it any more obtuse? Took me 2 years to realize there was such thing as global variables, which then opened up a whole new world.

Anyway, Marcin Policht has written a nice series on SQL Server 2000 DTS. There are 12 parts to it, and part 5 is:SQL Server 2000 DTS Part 5 - DTS Designer Tasks and Global Variables. Definitely worth bookmarking.

Terri