Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2007

    Unanswered: How to populate a table from a sql statement

    In a Data Flow Task, I have an OLE DB source that uses the following SQL Statement;


    --First create a table

    DECLARE @CategoryTable TABLE
    (ColID Int,
    ColCategory varchar(60),
    ColValue varchar(500)

    --and fill it

    INSERT INTO @CategoryTable
    (ColID, ColCategory, ColValue)
    LEFT(RawCollectionData,CHARINDEX(':',RawCollection Data)),
    LTRIM(SUBSTRING(RawCollectionData,CHARINDEX(':',Ra wCollectionData)+1,255))
    FROM Collections_Staging

    --Assign an ID to each block of data for each occurance of 'Reason:'

    DECLARE @ID int
    SET @ID = 1
    UPDATE @CategoryTable
    SET [ColID] = CASE WHEN ColCategory = 'Reason:' THEN @ID - 1 ELSE @ID END,
    @ID = CASE WHEN ColCategory = 'Reason:' THEN @ID + 1 ELSE @ID END

    --Then put the data together

    SELECT --cast to Nvarchar for MSAccess
    CAST(a.ColValue as Nvarchar(30)) AS OrderID,
    COALESCE(CAST(b.ColValue as Nvarchar(30)),'') AS SellerUserID,
    COALESCE(CAST(c.ColValue as Nvarchar(100)),'') AS BusinessName,
    COALESCE(CAST(d.ColValue as Nvarchar(15)),'') AS BankID,
    COALESCE(CAST(e.ColValue as Nvarchar(15)),'') AS AccountID,
    COALESCE(CAST(SUBSTRING(f.ColValue,CHARINDEX('$',f .ColValue)+1,500)AS DECIMAL(18,2)),0) AS CollectionAmount,
    COALESCE(CAST(g.ColValue as Nvarchar(10)),'') AS TransactionType,
    WHEN h.ColValue LIKE '%Matching Disbursement%' THEN NULL
    ELSE CAST(h.ColValue AS SmallDateTime)
    END AS DisbursementDate,
    --COALESCE(h.ColValue,'') AS DisbursementDate,
    WHEN i.ColValue LIKE '%Matching Disbursements%' THEN NULL
    WHEN CAST(LEFT(REVERSE(i.ColValue),4)AS INT) > 1000 THEN CAST(i.ColValue AS SmallDateTime)
    WHEN LEFT(REVERSE(i.ColValue),4) = '1000' THEN NULL
    END AS ReturnDate,
    --COALESCE(i.ColValue,'') AS ReturnDate,
    COALESCE(CAST(j.ColValue as Nvarchar(4)),'') AS Code,
    COALESCE(CAST(k.ColValue as Nvarchar(255)),'') AS CollectionReason

    FROM @CategoryTable a
    LEFT JOIN @CategoryTable b ON b.ColID = a.ColID AND b.ColCategory = 'Seller UserId:'
    LEFT JOIN @CategoryTable c ON c.ColID = a.ColID AND c.ColCategory = 'Business Name:'
    LEFT JOIN @CategoryTable d ON d.ColID = a.ColID AND d.ColCategory = 'Bank ID:'
    LEFT JOIN @CategoryTable e ON e.ColID = a.ColID AND e.ColCategory = 'Account ID:'
    LEFT JOIN @CategoryTable f ON f.ColID = a.ColID AND f.ColCategory = 'Amount:'
    LEFT JOIN @CategoryTable g ON g.ColID = a.ColID AND g.ColCategory = 'Transaction Type:'
    LEFT JOIN @CategoryTable h ON h.ColID = a.ColID AND h.ColCategory = 'Disbursement Date:'
    LEFT JOIN @CategoryTable i ON i.ColID = a.ColID AND i.ColCategory = 'Return Date:'
    LEFT JOIN @CategoryTable j ON j.ColID = a.ColID AND j.ColCategory = 'Code:'
    LEFT JOIN @CategoryTable k ON k.ColID = a.ColID AND k.ColCategory = 'Reason:'

    WHERE a.ColCategory = 'Order ID:'


    This statement parses. I can preview the data. I've tried to set up both an OLE DB destination to a SQL Server table and an MS Access table destination (Jet). In either case, the data will not populate the tables. I set up a Data Viewer, and no data appears in the Viewer. With the Access destination, I have the package set up to run in 32 bit mode.

    If data appears in the preview, then why doesn't the data appear in the data viewer, and why will the data not populate either of the destination tables?

    Thank you for your help!


  2. #2
    Join Date
    Apr 2007
    Sorry for the bad thread title. Anyway, I put the sql statement in a table UDF, and the UDF appears to work fine as the source.


Posting Permissions

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