Post reply

execution of SSIS package from stored procedure???

  • February 21, 2007 at 11:06 pm

    #117753

    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

  • February 26, 2007 at 8:00 am

    This was removed by the editor as SPAM

  • February 26, 2007 at 8:07 am

    #691771

    Hi,

    In your SP you print @cmd to the console. Have you tried copying and pasting this into a command window?

    Also you could try using DTExecUI.exe to generate the command line arguments which you can then copy into you SP to check they are correct.

    Regards

    Daniel

  • May 18, 2007 at 8:30 am

    #707470

    I am also having same problem, did anyone found the soln. it will be a great help!!

    I copied command line text and executed from cmd sucessfully. but it's failing from SP

  • September 12, 2007 at 10:43 am

    #733205

    Is there anyone who got the solution for the same.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply