DTS QUERY AND HELP

I’ve not had much experience with DTS packages before.I am using SQL Server 2000 and I have a table I wish to update.I have a spreadsheet with the updated records – this does not include all records and does not include all columns within the table,

I just wanted to know if it is still possible to update certain fields without having to include all the records and all the columns e.g.

Table 1

IDNAMESURNAME

1TEDSMITH

2FREDPERRY

3JOEYOUNG

Spreadsheet

IDSURNAME

2CASH

3WILLIS

Result

IDNAMESURNAME

1TEDSMITH

2FREDCASH

3JOEYOUNG

And the million-dollar question – please could you provide the steps to do this?

Take a look at the Data Driven Query Component|||

Can anyone provide any more information on this for me?

Thanks

|||If you need th enext level of help thn its probably best to move this thread to the SSIS forum|||

Look for "Building a Data Driven Query" in SQL Server 2000 Book Online, also found here: http://msdn2.microsoft.com/en-us/library/aa933507(sql.80).aspx

You'll want to follow the first example: "In the first example, every source row leads to the update of a destination row. Only one query type, an Update query, is used."

sqlsql

DTS package won't run as a scheduled job

Hi,
We have recently begun to experience a problem that is stumping us. We
have a DTS package that is run every morning as a scheduled job but
always fails. The job runs perfectly fine when kicked off manually
(either all at once from the Enterprise Manager or one step at a time
from DTS). The job history says it failed on step 1, but we've
determined that at least the first few steps run successfully. I've
attempted to turn package logging on but we never get any results (I've
tried to save the log both in the SQL Server log as well as a local
text file ... nothing ever appears in either location).
This is extremely frustrating and we're all getting tired of waking up
at 3am to kick the job off. Any ideas?
Thanks!!This article may help.
http://support.microsoft.com/kb/269074/EN-US/
Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
"Rich" wrote:
| Hi,
|
| We have recently begun to experience a problem that is stumping us. We
| have a DTS package that is run every morning as a scheduled job but
| always fails. The job runs perfectly fine when kicked off manually
| (either all at once from the Enterprise Manager or one step at a time
| from DTS). The job history says it failed on step 1, but we've
| determined that at least the first few steps run successfully. I've
| attempted to turn package logging on but we never get any results (I've
| tried to save the log both in the SQL Server log as well as a local
| text file ... nothing ever appears in either location).
|
| This is extremely frustrating and we're all getting tired of waking up
| at 3am to kick the job off. Any ideas?
|
| Thanks!!
||||Hi Dave,
Thanks for the article. I've read it and checked a few things out on
our SQL Server 2000 installation. The job was running under a different
account than I would have expected, but the account has full access to
the system and is a member of the system administrator group. Just to
be safe, I changed the owner of the job to the Administrator account
(which is what our other DTS packages run under). However, this didn't
work either.
I then deleted the scheduled job and recreated it on the server
desktop. The owner is again the Administrator account. It is scheduled
to run tonight, but I highly doubt anything is going to change.
Are there some security settings I could double-check perhaps?
Thanks,
-Rich
Dave Patrick wrote:
> This article may help.
> http://support.microsoft.com/kb/269074/EN-US/
> --
> Regards,
> Dave Patrick ...Please no email replies - reply in newsgroup.
> Microsoft Certified Professional
> Microsoft MVP [Windows]
> http://www.microsoft.com/protect
> "Rich" wrote:
> | Hi,
> |
> | We have recently begun to experience a problem that is stumping us. We
> | have a DTS package that is run every morning as a scheduled job but
> | always fails. The job runs perfectly fine when kicked off manually
> | (either all at once from the Enterprise Manager or one step at a time
> | from DTS). The job history says it failed on step 1, but we've
> | determined that at least the first few steps run successfully. I've
> | attempted to turn package logging on but we never get any results (I've
> | tried to save the log both in the SQL Server log as well as a local
> | text file ... nothing ever appears in either location).
> |
> | This is extremely frustrating and we're all getting tired of waking up
> | at 3am to kick the job off. Any ideas?
> |
> | Thanks!!
> ||||Something here may help.
http://www.sqldts.com/?106
Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
"Rich" wrote:
| Hi Dave,
|
| Thanks for the article. I've read it and checked a few things out on
| our SQL Server 2000 installation. The job was running under a different
| account than I would have expected, but the account has full access to
| the system and is a member of the system administrator group. Just to
| be safe, I changed the owner of the job to the Administrator account
| (which is what our other DTS packages run under). However, this didn't
| work either.
|
| I then deleted the scheduled job and recreated it on the server
| desktop. The owner is again the Administrator account. It is scheduled
| to run tonight, but I highly doubt anything is going to change.
|
| Are there some security settings I could double-check perhaps?
|
| Thanks,
| -Rich|||Thanks Dave,
I've looked at a few things suggested in the link you provided but
nothing worked. Finally, I created a new login for the windows
Administrator account and added it to the SysAdmin role. This worked!
Now, this stumps me, because the Windows workgroup "Administrators" is
already a member of SysAdmin, and the Windows Administrator account is
already assigned to the Administrators role... so it should have had
permissions already. Any thoughts on this?
Additionally, I mentioned that the job was not recording its log file.
This was mistaken - I was looking under the SQL Server logs (under
"Management"), but I just found them under DTS, "package logs".
However, there are no entries for when the package ran as a scheduled
job ... a log entry was recorded only when I ran the job manually. This
was really puzzling to me, but then I realized that it had to be a
security problem. The job wasn't getting anywhere, because the SQL
Agent was running under an account that didn't have the right
permissions applied (apparently).
-Rich
Dave Patrick wrote:
> Something here may help.
> http://www.sqldts.com/?106
> --
> Regards,
> Dave Patrick ...Please no email replies - reply in newsgroup.
> Microsoft Certified Professional
> Microsoft MVP [Windows]
> http://www.microsoft.com/protect
> "Rich" wrote:
> | Hi Dave,
> |
> | Thanks for the article. I've read it and checked a few things out on
> | our SQL Server 2000 installation. The job was running under a different
> | account than I would have expected, but the account has full access to
> | the system and is a member of the system administrator group. Just to
> | be safe, I changed the owner of the job to the Administrator account
> | (which is what our other DTS packages run under). However, this didn't
> | work either.
> |
> | I then deleted the scheduled job and recreated it on the server
> | desktop. The owner is again the Administrator account. It is scheduled
> | to run tonight, but I highly doubt anything is going to change.
> |
> | Are there some security settings I could double-check perhaps?
> |
> | Thanks,
> | -Rich|||They may know more about this here.
http://www.microsoft.com/communitie...oft.com/protect
"Rich" wrote:
| Thanks Dave,
|
| I've looked at a few things suggested in the link you provided but
| nothing worked. Finally, I created a new login for the windows
| Administrator account and added it to the SysAdmin role. This worked!
| Now, this stumps me, because the Windows workgroup "Administrators" is
| already a member of SysAdmin, and the Windows Administrator account is
| already assigned to the Administrators role... so it should have had
| permissions already. Any thoughts on this?
|
| Additionally, I mentioned that the job was not recording its log file.
| This was mistaken - I was looking under the SQL Server logs (under
| "Management"), but I just found them under DTS, "package logs".
| However, there are no entries for when the package ran as a scheduled
| job ... a log entry was recorded only when I ran the job manually. This
| was really puzzling to me, but then I realized that it had to be a
| security problem. The job wasn't getting anywhere, because the SQL
| Agent was running under an account that didn't have the right
| permissions applied (apparently).
|
| -Rich

DTS package won't run as a scheduled job

Hi,
We have recently begun to experience a problem that is stumping us. We
have a DTS package that is run every morning as a scheduled job but
always fails. The job runs perfectly fine when kicked off manually
(either all at once from the Enterprise Manager or one step at a time
from DTS). The job history says it failed on step 1, but we've
determined that at least the first few steps run successfully. I've
attempted to turn package logging on but we never get any results (I've
tried to save the log both in the SQL Server log as well as a local
text file ... nothing ever appears in either location).
This is extremely frustrating and we're all getting tired of waking up
at 3am to kick the job off. Any ideas?
Thanks!!This article may help.
http://support.microsoft.com/kb/269074/EN-US/
--
Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
"Rich" wrote:
| Hi,
|
| We have recently begun to experience a problem that is stumping us. We
| have a DTS package that is run every morning as a scheduled job but
| always fails. The job runs perfectly fine when kicked off manually
| (either all at once from the Enterprise Manager or one step at a time
| from DTS). The job history says it failed on step 1, but we've
| determined that at least the first few steps run successfully. I've
| attempted to turn package logging on but we never get any results (I've
| tried to save the log both in the SQL Server log as well as a local
| text file ... nothing ever appears in either location).
|
| This is extremely frustrating and we're all getting tired of waking up
| at 3am to kick the job off. Any ideas?
|
| Thanks!!
||||Hi Dave,
Thanks for the article. I've read it and checked a few things out on
our SQL Server 2000 installation. The job was running under a different
account than I would have expected, but the account has full access to
the system and is a member of the system administrator group. Just to
be safe, I changed the owner of the job to the Administrator account
(which is what our other DTS packages run under). However, this didn't
work either.
I then deleted the scheduled job and recreated it on the server
desktop. The owner is again the Administrator account. It is scheduled
to run tonight, but I highly doubt anything is going to change.
Are there some security settings I could double-check perhaps?
Thanks,
-Rich
Dave Patrick wrote:
> This article may help.
> http://support.microsoft.com/kb/269074/EN-US/
> --
> Regards,
> Dave Patrick ...Please no email replies - reply in newsgroup.
> Microsoft Certified Professional
> Microsoft MVP [Windows]
> http://www.microsoft.com/protect
> "Rich" wrote:
> | Hi,
> |
> | We have recently begun to experience a problem that is stumping us. We
> | have a DTS package that is run every morning as a scheduled job but
> | always fails. The job runs perfectly fine when kicked off manually
> | (either all at once from the Enterprise Manager or one step at a time
> | from DTS). The job history says it failed on step 1, but we've
> | determined that at least the first few steps run successfully. I've
> | attempted to turn package logging on but we never get any results (I've
> | tried to save the log both in the SQL Server log as well as a local
> | text file ... nothing ever appears in either location).
> |
> | This is extremely frustrating and we're all getting tired of waking up
> | at 3am to kick the job off. Any ideas?
> |
> | Thanks!!
> ||||Something here may help.
http://www.sqldts.com/?106
--
Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
"Rich" wrote:
| Hi Dave,
|
| Thanks for the article. I've read it and checked a few things out on
| our SQL Server 2000 installation. The job was running under a different
| account than I would have expected, but the account has full access to
| the system and is a member of the system administrator group. Just to
| be safe, I changed the owner of the job to the Administrator account
| (which is what our other DTS packages run under). However, this didn't
| work either.
|
| I then deleted the scheduled job and recreated it on the server
| desktop. The owner is again the Administrator account. It is scheduled
| to run tonight, but I highly doubt anything is going to change.
|
| Are there some security settings I could double-check perhaps?
|
| Thanks,
| -Rich|||Thanks Dave,
I've looked at a few things suggested in the link you provided but
nothing worked. Finally, I created a new login for the windows
Administrator account and added it to the SysAdmin role. This worked!
Now, this stumps me, because the Windows workgroup "Administrators" is
already a member of SysAdmin, and the Windows Administrator account is
already assigned to the Administrators role... so it should have had
permissions already. Any thoughts on this?
Additionally, I mentioned that the job was not recording its log file.
This was mistaken - I was looking under the SQL Server logs (under
"Management"), but I just found them under DTS, "package logs".
However, there are no entries for when the package ran as a scheduled
job ... a log entry was recorded only when I ran the job manually. This
was really puzzling to me, but then I realized that it had to be a
security problem. The job wasn't getting anywhere, because the SQL
Agent was running under an account that didn't have the right
permissions applied (apparently).
-Rich
Dave Patrick wrote:
> Something here may help.
> http://www.sqldts.com/?106
> --
> Regards,
> Dave Patrick ...Please no email replies - reply in newsgroup.
> Microsoft Certified Professional
> Microsoft MVP [Windows]
> http://www.microsoft.com/protect
> "Rich" wrote:
> | Hi Dave,
> |
> | Thanks for the article. I've read it and checked a few things out on
> | our SQL Server 2000 installation. The job was running under a different
> | account than I would have expected, but the account has full access to
> | the system and is a member of the system administrator group. Just to
> | be safe, I changed the owner of the job to the Administrator account
> | (which is what our other DTS packages run under). However, this didn't
> | work either.
> |
> | I then deleted the scheduled job and recreated it on the server
> | desktop. The owner is again the Administrator account. It is scheduled
> | to run tonight, but I highly doubt anything is going to change.
> |
> | Are there some security settings I could double-check perhaps?
> |
> | Thanks,
> | -Rich|||They may know more about this here.
http://www.microsoft.com/communities/newsgroups/en-us/default.aspx?dg=microsoft.public.sqlserver.dts&cat=en_US_2b8e81a3-be64-42fa-bd81-c6d41de5a219&lang=en&cr=US
I just threw out that article 269074 and the link to the sqldts site since
they both helped me understand a problem I was having with trying to call a
DTS package from VB
Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
"Rich" wrote:
| Thanks Dave,
|
| I've looked at a few things suggested in the link you provided but
| nothing worked. Finally, I created a new login for the windows
| Administrator account and added it to the SysAdmin role. This worked!
| Now, this stumps me, because the Windows workgroup "Administrators" is
| already a member of SysAdmin, and the Windows Administrator account is
| already assigned to the Administrators role... so it should have had
| permissions already. Any thoughts on this?
|
| Additionally, I mentioned that the job was not recording its log file.
| This was mistaken - I was looking under the SQL Server logs (under
| "Management"), but I just found them under DTS, "package logs".
| However, there are no entries for when the package ran as a scheduled
| job ... a log entry was recorded only when I ran the job manually. This
| was really puzzling to me, but then I realized that it had to be a
| security problem. The job wasn't getting anywhere, because the SQL
| Agent was running under an account that didn't have the right
| permissions applied (apparently).
|
| -Rich

DTS Output Excel File - how to format?

Has anyone had experience formatting an excel file (i.e. run a macro) after it is (created &) outputted from a DTS package?

Also an easier question:
What is the best (easiest) way to create a unique filename in Excel with a datetimestamp in the file name (i.e. MyFile-20040608.xls)

Thanks!I will have a similar situation coming up so I am curious to see the answer. We are installing a SQL Server box in our department. It will be our own server where I will be able to create DTS jobs to export data every 15 minutes throughtout the course of the day. I plan on appending data to a table and having it do so for each and every job's output on SQL Server. The issue is I am only inhouse very early in the morning so I want to allow the client to see the reports as they run. They are tech challenged so I want to use either Excel or Access to create reports as they are run.

ddave|||I had vb developers modify a csv file while I wrotw the sql...

It was a whole store and forward app...scheduling, formatting ect...

sql just delivered the file to a location...|||>Also an easier question:
>What is the best (easiest) way to create a unique filename in Excel with a >datetimestamp in the file name (i.e. MyFile-20040608.xls)

DTS outputs it to any MyFile.xls and the next ActiveX step renames MyFile.xls file to MyFile-<date>.xls
-rohit