execution of SSIS package from stored procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

execution of SSIS package from stored procedure

Author

Topic

papillon2811
Starting Member

18 Posts

Posted - 2007-02-22 : 00:56:15

Hello friends!
I have one query regarding execution of SSIS package through Stored Procedure.

I have created SSIS package which extract data from database and put that into various text files.Here I am using two global variables one is for Department ID and another is path where I wanna to place my text files as per departments.When I ran it through command prompt it works fine but now I want that dtsx package to run from stored procedure with same input parameters

when i searched on line i got this solution

Declare @FilePath varchar(2000)

Declare @Filename varchar(1000)

Declare @cmd varchar(2000)

set @FilePath = 'C:\setup\SSIS Packages\SSIS Package File Extract Data\SSIS Package File Extract Data\bin\'

set @Filename = 'DataExtract.dtsx'

select @cmd = 'DTExec /F "' + @FilePath + @Filename + '"'

print @cmd

exec master..xp_cmdshell @cmd

but when i execute it i got error like

Source: {8A27E8DF-051B-4F6B-9538-85BED1F161D8}
Description: Unable to load the package as XML because of package does not have a valid XML format. A specific XML parser error will be posted.
End Error
Error: 2007-02-22 11:31:37.32
Code: 0xC0011002
Source: {8A27E8DF-051B-4F6B-9538-85BED1F161D8}
Description: Failed to open package file "C:\setup\SSIS Packages\SSIS Package File Extract Data\SSIS Package File Extract Data\bin\DataExtract.dtsx" due to error 0x80070003 "The system cannot find the path specified.". This happens when loadin
g a package and the file cannot be opened or loaded correctly into the XML document. This can be the result of either providing an incorrect file name was specified when calling LoadPackage or the XML file was specified and has an incorrect format.
End Error
Could not load package "C:\setup\SSIS Packages\SSIS Package File Extract Data\SSIS Package File Extract Data\bin\DataExtract.dtsx" because of error 0xC0011002.
Description: Failed to open package file "C:\setup\SSIS Packages\SSIS Package File Extract Data\SSIS Package File Extract Data\bin\DataExtract.dtsx" due to error 0x80070003 "The system cannot find the path specified.". This happens when loading a
package and the file cannot be opened or loaded correctly into the XML document. This can be the result of either providing an incorrect file name was specified when calling LoadPackage or the XML file was specified and has an incorrect format.

And also I am not understand where i should pass my two input parameters which I used in SSIS package variables???????

Please help me out

Thanks

nr
SQLTeam MVY

12543 Posts

Posted - 2007-02-22 : 03:59:15

The path will be relativeto the server i.e. c: will be the disk on the server not the disk on your workstation.
Try it first with the file in the root of the drive and execute a dir command to make sure it can access it, if you still have trouble do a type command to make sure it looks like xml and can be opened.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

papillon2811
Starting Member

18 Posts

Posted - 2007-02-22 : 05:22:17

yes :) :)

It was like that only

thanks nr..but if I want to generate these text files to my work station then shall i use my IP Address\C:\temp ????????

Thanks

nr
SQLTeam MVY

12543 Posts

Posted - 2007-02-22 : 21:08:52

Depends on how your network is set up.
I would create a share on your pc (share a folder) and give everyone full control on it.
Then access it by \\<machinename>\<sharename>
If that works you can try restricting the permissions.

For what you were suggesting it would have to be the admin share
<ip address>\c$\temp
But it's not a good idea to use that even if you have permission.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

papillon2811
Starting Member

18 Posts

Posted - 2007-02-23 : 06:35:31

hi

Good work :)

Great Weekend :) :)

cse_supriya
Starting Member

1 Post

Posted - 2009-12-23 : 03:50:48

Hi

This discussion proved helpful to me..Thanx a lot..
:)
Gud work...

-Supriya

Susheel1974
Starting Member

9 Posts

Posted - 2010-01-07 : 04:50:20

Hi nr,
I am facing the same problem. Can you help me also. Also I didnt understand what do you mean by "if you still have trouble do a type command to make sure it looks like xml" Please explain this.

Thanks in advance.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-07 : 04:58:10

see if this helps you

http://www.codeproject.com/KB/database/Call_SSIS_from_SP.aspx

Susheel1974
Starting Member

9 Posts

Posted - 2010-01-07 : 05:02:14

Hi nr I figured out what you meant.
Your suggestions helped me a lot. Thank you very much

Susheel1974
Starting Member

9 Posts

Posted - 2010-01-07 : 05:08:23

One more help guys. IF i install it on some other server say 154.44.44.44 in a shared folder say SSISPackages. Then can I access it using the
dtexec /file "154.44.44.44\SSISPackages\MYPackage\Package.dtsx"

Thank you

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-07 : 05:29:27

quote:
Originally posted by Susheel1974

One more help guys. IF i install it on some other server say 154.44.44.44 in a shared folder say SSISPackages. Then can I access it using the
dtexec /file "154.44.44.44\SSISPackages\MYPackage\Package.dtsx"

Thank you

you need to use unc path then
//154.44.44.44/..

Susheel1974
Starting Member

9 Posts

Posted - 2010-01-07 : 06:13:44

Hi visakh16. Thank you for your suggestions. However I dont seem to get it. Is it possible for you to give me the complete syntax.

Thanks a lot for your help

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-07 : 06:35:51

something like

psexec \\154.44.44.44 -c DTExec /File <full package path>

Susheel1974
Starting Member

9 Posts

Posted - 2010-01-07 : 23:54:45

Hi visakh16, I tried it but I get this error "'psexec' is not recognized as an internal or external command"
Can you help me please?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-08 : 02:33:46

so you dont have this in your machine?

http://technet.microsoft.com/en-us/sysinternals/bb897553.aspx

Susheel1974
Starting Member

9 Posts

Posted - 2010-01-08 : 03:13:43

Hello visakh16, I am very thank ful to your prompt response. However I have a constraint here and that is I cannot install anything new on my server. Hence psexec cannot be installed there. Is there any other way that I can do it?

Thanks a lot,
Susheel

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-08 : 03:19:14

then try this

http://msdn.microsoft.com/en-us/library/ms403355.aspx

Susheel1974
Starting Member

9 Posts

Posted - 2010-01-08 : 03:50:59

Unfortunately that doesnt server my purpose. I can't do extra coding like webservice or any C# coding. I m just suppose to call the ssis package from the SP. I am very sorry for the trouble. I need to resolve it and need help.

Thanks,
Susheel

Susheel1974
Starting Member

9 Posts

Posted - 2010-01-11 : 00:20:05

Hi visakh16, when I try to run "psexec \\154.44.44.44 -c DTExec /File <full package path>" i get invalid option error for the ip that is being passed i.e \\154.44.44.44. Any idea why it happens.

Thanks for your help

Susheel1974
Starting Member

9 Posts

Posted - 2010-01-11 : 04:36:45

Hello All,
Please help me with the following.
1. I retrieve records from a table using Execute sql as a Full Resultset object.
2. Now want to insert all the records from that Resultset object to another table.
Can please anyone tell me how can i do this as my day is stopped with this little task.

Thank you in advance,
Susheel

itaigitt
Starting Member

1 Post

Posted - 2011-02-08 : 02:26:17

Hi.
Can I copy the package files to the server in order to run it without this error? If yes - which files should I copy?
Thanks.

itaigitt
http://copypastenet.blogspot.com/

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources