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

Charles,

Boy do I feel stupid !!!

Realized from your process description that you were running SSMS from your
workstation.

As dba, I normally run SSMS on our Sql Server using a remote (terminal
services) connection from my workstation to our Sql Server box.

So I tried the running the job from SSMS installed on and running from my
workstation. (even though I couldn't see the step in the job property page).
It worked !! Arrrgggggg.

Next test, I walked down to the machine room and ran the job directly on the
Sql Server console. It ran there successfully as well !!

So I set up a scheduled run for 12:45pm ... the job ran fine !!!!
Arrrrrggggggggg !!!!

So the issue seems to have something to do with executing the SSMS job via
the remote (terminal services) connection. All other jobs I've set up using
the remote terminal session to the server have worked fine until now.
Terminal services has been solid up to now ... never really considered it as
a possible problem source.

I think I'll go throw myself out the window now.

Barry
in Oregon

"Charles Wang[MSFT]" <changliw@xxxxxxxxxxxxxxxxxxxx> wrote in message
news:dalYTME0HHA.6140@xxxxxxxxxxxxxxxxxxxxxxxxx
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: SSIS - can SSIS pkg recognize a drive letter within SMS job?
    ... establish a fresh test environment with SQL Server 2005 without SP2 ... Created a shared folder on the remote machine and assign ... Uploaded the SSIS package which was simple and just contained one ... What is your SQL Server Agent service account? ...
    (microsoft.public.sqlserver.dts)
  • Re: help! scheduled package does nothing...
    ... > executing the package manually. ... > Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) ... >> Both the sqlserver and the sqlserveragent services are ran by the same ...
    (microsoft.public.sqlserver.dts)
  • RE: SQL 2005 Management Studio cant edit text rows with > 4000 ch
    ... in SSMS, I cannot do the same thing. ... a text field, if I edit the int field, it fails. ... In SQL Server no matter 2000 or 2005, the default maximum size that can be ... table name','text in row',7000", the text-type field can support maximum ...
    (microsoft.public.sqlserver.tools)
  • Re: SQL Server Management Studio Cannot Open SQL Express Files
    ... Currently I can work with the DB in SSMS but not in VS. ... I suggest you to open SQL Server Configuration Manager and see how many SQL ... If I create the database in VS2005 using the ... If I create the DB in Management Studio I cannot work with it in VS2005. ...
    (microsoft.public.sqlserver.tools)
  • Re: Cannot find a GUI to execute a query
    ... you'll still have SSMSE 2008. ... SSMSE but there are some other features, like SQL Server Profiler, that I ... I guess I can still 'Script Stored Procedure As Execute To' a new window, ... How about using SSMSE instead of SSMS? ...
    (microsoft.public.sqlserver.tools)