Sql2005 SSIS pack will not run from XML, Strored Proc

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

briang - 25 Jul 2008 23:46 GMT

I believe this to be a File System security issue but I can not get beyond it.

In my test I am using: XML, to call a stored proc which executes two .dtsx
packages. One DTSX package "CreateExcel.dtsx" creates an Excel File from a
Sql Table. The other dtsx package "RenameFile.dtsx" renames the Excel file.

This is my XML test url
http://10.1.202.10/br_app_db/templates/br_export_report_test.xml?Report_Number=PLA
This executes stored proc "sp_br_export_report"

When I execute the Stored Proc "sp_br_export_report" from a query window in
SS Management Studio, both DTSX packages run fine and all is well.

When I call "sp_br_export_report" from the XML, only CreateExcel.dtsx works
correctly. The result Excel file is never renamed.

I don't know how to get any debug messages when uing the XML call so I am
blindly trying many things.

This is a second attempt to "rename a file" . I had the same problem using a
xp_cmdshell. I setup the proxy account but still no luck.
@winCmd = 'rename file.xls newname.xls'
EXEC @shell_result = master..xp_cmdShell @winCmd

I gave up on xp_cmdshell and started the RenameFile.dtsx because the first
DTSX pacakge was working well. Now I am stuck again.

Any suggestions?

Signature

Peace,
Brian

Reply to this Message

Todd C - 28 Jul 2008 15:16 GMT

If you can CREATE the file, then you shoule be able to RE-NAME it.

Have you tried putting a FileSystem Task in the first DTSX package to do the
re-naming?

Signature

Todd C

> I believe this to be a File System security issue but I can not get beyond it.
>
[quoted text clipped - 24 lines]
>
> Any suggestions?

Reply to this Message

briang - 28 Jul 2008 20:06 GMT

I tried that but I will try again. I have gone thru so many reps, that I
have lost track.

Signature

Peace,
Brian

> If you can CREATE the file, then you shoule be able to RE-NAME it.
>
[quoted text clipped - 29 lines]
> >
> > Any suggestions?

Reply to this Message

briang - 29 Jul 2008 19:11 GMT

Todd:
I went back to add the rename task in the package. I realized that how I
would use the Rename package would be to add it as an archive step in a
Stored Procedure or another package. It needs to be more modular rather
than including it over and over in every package I write. I also noticed that
Variables do not copy between packages which make inclusion in each
individual package a more time consuming task. In summary, I can include it
in package as a trouble shooting step, but I really need to get it working as
it own package.
Any ideas?

Signature

Peace,
Brian

> > If you can CREATE the file, then you shoule be able to RE-NAME it.
> >
[quoted text clipped - 29 lines]
> > >
> > > Any suggestions?

Reply to this Message