Post reply

Microsoft Data Transformation Services (DTS) Package - Not enough storage is available to process this command.

  • September 12, 2008 at 6:25 am

    #71977

    Working on SQL Server 200 SP4.

    I have a DTS package that is executed from a stored procedure. The problem occurs when I get an error on the package and apply a change to fix it. The next time the Stored Procedure calls ANY Package I get the following error:

    Microsoft Data Transformation Services (DTS) Package - Not enough storage is available to process this command.

    The only resolution is to bounce the Server.

  • September 16, 2008 at 7:02 am

    #872335

    Hi,

    When executing the package are you using sp_executePkg or xp_cmdShell 'DTSRun...'?


    Ade

    A Freudian Slip is when you say one thing and mean your mother.
    For detail-enriched answers, ask detail-enriched questions...[/url]

  • September 17, 2008 at 8:17 am

    #873033

    Hi,

    Thanks for replying - thi is the code for executing.

    SET @Package = 'SXE Done File' -- + @stream

    -- Create a DTS Package object

    exec @hr = sp_OACreate 'DTS.Package', @pkg output

    if @hr <> 0

    begin

    exec @hr = [master].[dbo].[sp_OAGetErrorInfo] @pkg, @source out, @description out

    if @hr = 0

    Print 'sp_OACreate Error : ' + @Package + ' ' + @source + ' - ' + @description

    else

    Print 'sp_OAGetErrorInfo Error (Create)'

    end

    set @source = ''

    set @description= ''

    -- load parms

    exec @hr = sp_OAMethod @pkg, 'LoadFromSqlServer', null

    ,@ServerName = '(local)'

    ,@Flags= 256 --Integrated security. Use 0 for SQL Server authentication

    ,@PackageName = @Package

    if @hr <> 0

    begin

    exec @hr = [master].[dbo].[sp_OAGetErrorInfo] @pkg, @source out, @description out

    print 'error loading parms'

    print @Package + ' ' + @source + ' - ' + @description

    end

    exec @hr = sp_OASetProperty @Pkg, 'GlobalVariables("Load_No").value', @LoadNo

    if @hr <> 0

    begin

    print 'error laod no Variable'

    end

    exec @hr = sp_OASetProperty @Pkg, 'GlobalVariables("Table_Name").value', @TableName

    if @hr <> 0

    print 'error Tablename Variable'

    exec @hr = sp_OASetProperty @Pkg, 'GlobalVariables("Table_ID").value', @TableID

    if @hr <> 0

    print 'error TableID Variable'

    exec @hr = sp_OASetProperty @Pkg, 'GlobalVariables("File_Path").value', @ImportFolder

    if @hr <> 0

    print 'error File_Path Variable'

    -- run package

    exec @hr = sp_OAMethod @pkg, 'Execute'

    if @hr <> 0

    print 'error on Execute ' + @Package

  • February 24, 2009 at 8:24 pm

    #949759

    I am having the exact same problem.

    Have you found a solution?

  • February 25, 2009 at 3:32 am

    #949856

    The only resolution suggestions I've found are here:

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

    HTH,


    Ade

    A Freudian Slip is when you say one thing and mean your mother.
    For detail-enriched answers, ask detail-enriched questions...[/url]

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

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