Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2005

    Unanswered: problem running a BCP SP in SSIS

    Hi all,

    i have a script to load and trandform data and then using a BCP command to export it out of SQL to a CSV file.

    I am using SQL 2005 SP2

    If i execute the command through a SQL query window it works great - i get my outputted CSV file, but if i execute it using a DTSX package, then it doesnt work!!!!!

    the code is below that i am running. Any assistance would be appreciated!

    Use database1
    Create PROC testbut
    Set NoCount ON
    DECLARE @ADPDiv nvarchar(10)
    DECLARE @ADPMth nvarchar(10)
    DECLARE @ADPYear nvarchar(10)
    DECLARE @DIVMY nvarchar(100)
    DECLARE @BCPCMD nvarchar(4000)
    DECLARE @BCPCMD1 nvarchar(4000)
    DECLARE @BCPCMD2 nvarchar(4000)
    SET @ADPDIV = 'DIV45' 
    SET @ADPmth = 'JUL'
    SET @ADPyear = '2011'
    SELECT @DIVMY = '"E:\Dataload\'+@ADPDIV+@ADPmth+@ADPyear+'.csv"'
    SELECT @BCPCMD1 = 'BCP "exec frmpc.dbo.testbut" QUERYOUT '
    SELECT @BCPCMD2 = ' -c -Uuser -Password -t,'
    --setup temp table
    DECLARE @Dataload Table (
     [AC_Code] [nvarchar](50) NULL, 
    [Value] [money] NULL, 
    [Month] [nvarchar](50) NULL,
    [Year] [nvarchar](50) NULL
    -- Inserting data from the adptemp table
    INSERT INTO @dataload ([AC_CODE],[Value],[month],[year],[ADP_Desc])
    SELECT [AC_CODE],[Value],[month],[year],[ADP_Desc]
    FROM Dataload2
    --transform the data
    --some stuff in here
    --script for selecting end results
    Select unit_id,Dept_id,Franchise_id,Line_id,month_id,Year_id, [Value] from @dataload
    --test results
    --PRINT @divmy
    --BCP to export the data
    EXEC master..xp_cmdshell @BCPCMD
    EXEC testbut
    DROP PROC testbut
    Last edited by chappo; 09-10-09 at 13:27.

  2. #2
    Join Date
    Nov 2004
    on the wrong server
    Provided Answers: 6
    smells like a permissions issue. got an error?
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Feb 2005
    Quote Originally Posted by Thrasymachus
    smells like a permissions issue. got an error?
    i think your right - but i get no errors! - ive checked that the sp xp_cmdshell is enabled ...

    ive ran the SQL profiler, and i get a different result set when i run it through query (it shows executing the BCP at the bottom and the the sql statements) - whereas when running through ssis, it simply shows the bcp command (does this make sense>??)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts