Data Not Copying to MS Access Table

Hello,

I have a Data Flow Source that uses a SQL Command to pull data. In the SQL statement, I used CAST to change all varchar types to Nvarvchar to suit MS Access. I can preview the data from the source. In testing, the SQL statement only pulls about ten records.

I have a Microsoft 2000 Access database table as a destination. Data in each column in the table is required, and all columns have defaults.

I also have a grid data viewer set up. I have the DefaultBufferMaxRows set to 2 so that I can see data going across. When I execute this dataflow, no data is transfered to the Access database table. No data shows up in the dataviewer. There are no errors. The 'Execution Results' tab does not show errors, but indicates that zero rows were transfered. There are no warnings.

How do I begin to isolate the problem? The following is the SQL Statement in the Data Flow Source. Thank you for your help! - cdun2

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

--and fill it

INSERT INTO @.CategoryTable
(ColID, ColCategory, ColValue)
SELECT
0,
LEFT(RawCollectionData,CHARINDEX(':',RawCollectionData)),
LTRIM(SUBSTRING(RawCollectionData,CHARINDEX(':',RawCollectionData)+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
a.ColID,
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,
CASE
WHEN h.ColValue LIKE '%Matching Disbursement%' THEN NULL
ELSE CAST(h.ColValue AS SmallDateTime)
END AS DisbursementDate,
--COALESCE(h.ColValue,'') AS DisbursementDate,
CASE
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:'

Are you doing this on an x64 machine. Is it possible that your package is executed in 64-bit mode? There is no 64-bit version of the JET provider.

There have been many posts about this before. Seaarch this forum to get instructions for making sure the packages are executed in 32-bit mode.

Thanks,

Bob

|||

Hi Cdun,

When you preview the Source, do you find any rows existing for the query? If yes, When you execute the dataflow task can you see any rows in Data grid viewer?

In fact if there are rows, it must be transfered to the destination table.

Thanks

Subhash Subramanyam

|||

Subhash Subramanyam wrote:

When you execute the dataflow task can you see any rows in Data grid viewer?

There are rows in the preview, but no rows in the data grid viewer. I'll check into the 64 bit setting.

|||

Bob Bojanic - MSFT wrote:

Seaarch this forum to get instructions for making sure the packages are executed in 32-bit mode.

Thanks,

Bob

I went to the package properties, debugging, and set Run64BitRuntime to False. I still get the same result The data will preview, but will not transfer into the Access table.

In the Data Flow Task, I'm using an OLEDB source to execute the sql statement. Should I be using an Execute SQL Task instead? The sql statement also uses a TABLE variable. Could this be a problem?

|||There must be something wrong with the way I'm trying to deliver the data to the Access table, because I can't get data to a sql server table destination either.|||

Never mind on this. I resorted to creating a table UDF as the source, and it looks like that will work.

cdun2