Re: SSIS - can SSIS pkg recognize a drive letter within SMS job?

Hi Barry,
Sorry for delaying my response. I noticed that you might have a long time
to perform a test with SP2, so I decided to request our Lab to help me
establish a fresh test environment with SQL Server 2005 without SP2
yesterday. However the environment was just finished this afternoon.

To my surprise, after my test I found that my former solution could also
work on the SQL Server 2005 without SP2. So I think that we should put SP2
aside now, this issue is caused by other reasons.

I would like to summarize my steps here and you may check to see if there
was any step ommitted at your side:
1. Created a shared folder on the remote machine (Server B) and assign
fully control permission to the my local SQL Server Agent Services (on
Server A) startup account which is a domain user;
2. On my local computer (Server A), created a mapped drive to the UNC
folder where you upload the SSIS package;
3. Uploaded the SSIS package which was simple and just contained one
"Execute SQL Task" to the shared folder;
4. On Server A, connected to SQL Server Integration Services from SSMS,
right clicked File System, clicked Import Package, selected Package
location as File System, selected Pckage path as W:\rptest.dtsx (W
represents
'\\charles-test\server_name\shared\database\sql_server\workarea\dba\server_n
ame\instance_name\db_name') , input package name as rptest, and clicked OK;
5. Tried running the package in SSMS to check if it could work. It finished
successfully on my machine.
6. On Server A, connected to Database Engine from SSMS, expanded SQL Server
Agent and created a job; under job properties window, input job name,
selected Steps, clicked New..., input Step name, selected Type as "SQL
Server Integration Services Package", selected "Run as" as "SQL Agent
Service Account", selected "Package source" as "SSIS Package Store",
selected Server as my local server name "CharlesXP", selected "Log on to
the server" as "Use Windows Authentication", selected Package as "\File
System\rptest" and clicked OK.
7. Right clicked the job and clicked "Start job at step...".
It worked without any error message thrown.

I began to doubt that if this was a permission issue at your side. For now,
I recommend that you let me know the following questions:
1. What is your SQL Server Agent service account?
2. What are the permissions that your SQL Server Agent service account has
on your remote shared folder?
3. If this issue persists, what is the result if you use a simple SSIS test
package?

For your question, I have not found any documentation talking about it,
however I think it is essentially same as deploying all the SSIS packages
into the File System folder of SQL Server Integration Services package
storage, which is actually my above method.

If you have any other questions or concerns, please feel free to let me
know.

Best regards,
Charles Wang
Microsoft Online Partner Support

PLEASE NOTE: The partner managed newsgroups are provided
to assist with break/fix issues and simple how to questions.
We also love to hear your product feedback!

Let us know what you think by posting
- from the web interface: Partner Feedback
- from your newsreader:
microsoft.private.directaccess.partnerfeedback.
We look forward to hearing from you!
======================================================
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================














.

Relevant Pages

  • Re: Can you use osql in DTS?
    ... This sounds simple enough to do in a single package. ... ActiveX Script task to build the date and time strings to populate the ... Data Pump task that executes a proc that populates a table and then ... Columnist, SQL Server Professional ...
    (microsoft.public.sqlserver.dts)
  • Re: Can you use osql in DTS?
    ... This sounds simple enough to do in a single package. ... ActiveX Script task to build the date and time strings to populate the ... Data Pump task that executes a proc that populates a table and then ... Columnist, SQL Server Professional ...
    (microsoft.public.sqlserver.programming)
  • Re: SSIS - can SSIS pkg recognize a drive letter within SMS job?
    ... Realized from your process description that you were running SSMS from your ... I normally run SSMS on our Sql Server using a remote (terminal ... Uploaded the SSIS package which was simple and just contained one ... What is your SQL Server Agent service account? ...
    (microsoft.public.sqlserver.dts)
  • Re: Migrating DTS Local Package from test to production environments v7.0
    ... The version of SQL Server on which the package was create is SQL Server ... DTS runs at the client so manually will be on the invoker's PC which in this ...
    (microsoft.public.sqlserver.dts)
  • Re: DTS Packages fail when executed from Jobs
    ... Action Plan 1 - Modify the path of Dtsrun.exe ... Expand the server that is running SQL Server in SQL Server ... Action Plan 2 - Run the DTS Package on the server rather than on ...
    (microsoft.public.sqlserver.dts)