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

Charles,

1st thanks for all your advice and patience while helping me work this
problem. Your help is very much appreciated.

With respect to your suggestions ...

I tried changing the mapped drive values to UNC's for both the path to the
delimited text file directory and also the path to the .dtsx files. The
process still fails. Very frustrating.

I also tried adding the UNC path to the <FullPath> value in the .dtproj
..dtsx definitions as well. Still with no success.

It's interesting to note that the job runs fine when executed directly from
within the SMS - Integration Services connection package store using both
the mapped drives and the UNC paths.

With respect to engine version, we're still running the 1st release version.
From your note it looks like I should re-consider installing SP2.
Unfortunately, from what I've read about 'breaking' problems with SP1 & SP2,
I'm very wary of upgrading our instance since our engine is running GREAT
right now except for this particular problem.

The one alternative I've been wondering about is setting the default package
path in the 'Controller' SSIS package that calls all the individual table
[Execute Package] tasks. Unfortunately, I haven't figured out a way to
set a starting path from within an SSIS package script. Is there a way to
set a 'defualt starting path' at the beginning of an SSIS package ?

By the way, changing the file path on 150+ individual packages with the
SSIS designer would be VERY tedious ... is there some place that package
object paths can be updated with a text editor outside of the SSIS designer
? I had hopes when updating the .dtproj <FullPath> value but it setting the
path there doesn't seem to make a difference.

Thanks in advance.

Barry
in Oregon

"Charles Wang[MSFT]" <changliw@xxxxxxxxxxxxxxxxxxxx> wrote in message
news:%23$toY%23SzHHA.5848@xxxxxxxxxxxxxxxxxxxxxxxxx
Hi Barry,
Thanks for your response.

What is the result if you modify the FullPath value?
Also, I find that your SQL Server 2005 version is 9.0.0.1399 which
indicates that your SQL Server 2005 has no service packs installed. I
recommend that you install SQL Server 2005 SP2 first and then recreate
your
SSIS package to see if this issue persists.

You can download the SP2 from:
http://www.microsoft.com/downloads/details.aspx?familyid=d07219b2-1e23-49c8-
8f0c-63fa18f26d3a&displaylang=en
http://support.microsoft.com/kb/933508

Please feel free to let me know if you have any other questions or
concerns.

Best regards,
Charles Wang
Microsoft Online Community Support



Charles,

I don't know if this has any relevance to this issue or not. However ...
this observation may be worth mentioning.

I was fighting with trying to rename one of my SSIS projects and had a
terrible time getting the renaming to 'take', finally ended
up deleting the .database, .dtproj, etc files totally and simpy readded
all the projects to a new correctly named project. Durning this > process
I noticed that the package descriptions within the .dtproj file have a
<FullPath> xml tag. But in my SSIS projects the
<FullPath> value contains only the package name and no path information
at all. Implying that, to run, the directory containing the
package must be selected ... could this be the issue that is causing the
SMS job to fail to find the package ??

Probably a 'red herring' but one never knows ...


<DtsPackage>
<Name>aim_employee_responsible_to_delimited_txt.dtsx</Name>

<FullPath>aim_employee_responsible_to_delimited_txt.dtsx</FullPath>
<References />
</DtsPackage>
<DtsPackage>
<Name>aim_hardware_attributes_to_delimited_txt.dtsx</Name>

<FullPath>aim_hardware_attributes_to_delimited_txt.dtsx</FullPath>
<References />
</DtsPackage>
<DtsPackage>
<Name>aim_inventory_item_to_delimited_txt.dtsx</Name>
<FullPath>aim_inventory_item_to_delimited_txt.dtsx</FullPath>
<References />
</DtsPackage>

Barry
in Oregon



.

Relevant Pages

  • RE: Move SSIS packages to a new SQL 2005 server
    ... domain user account for SQL Server Agent services. ... I recommend that you create a domain user account for your SQL Server Agent ... I think that a better way is to set your package ... You can create a SSIS package configuration file and deploy it together ...
    (microsoft.public.sqlserver.dts)
  • "Object was open" OLEDB error for parallel calls to Look Up transformation Options
    ... I have an SQL Server 2005 SSIS Package which users can initiate using ... I kick the package off from code in a separate thread. ... An OLE DB record is available. ...
    (microsoft.public.sqlserver.dts)
  • Re: SSIS - can SSIS pkg recognize a drive letter within SMS job?
    ... What is the result if you modify the FullPath value? ... indicates that your SQL Server 2005 has no service packs installed. ... SSIS package to see if this issue persists. ... <DtsPackage> ...
    (microsoft.public.sqlserver.dts)
  • RE: Run a ssis package on SQL Server 2005 Error
    ... Failure saving package. ... This is most likely a known issue in SQL Server 2005, ... Set the SSIS Package ProtectionLevel property to ServerStorage ...
    (microsoft.public.sqlserver.dts)
  • RE: Run and Edit DTS in SQL Server 2005
    ... Charles Kangai, MCT, MCDBA ... we've the schedule to migrate all DTS to SSIS but the run DTS in ... Packages folder in Solution Explorer and choose New SSIS Package). ... The SSIS package you just created will then go and execute your DTS ...
    (microsoft.public.sqlserver.dts)