Importing data from MS Access using DTS 2005

Guys,

I am new to DTS 2005; having trouble on how to connect to MS Access to pull data? what kind of connection manager should I use (OLE?) and what specific Data Flow Source type? Please respond.

Thanks

You will need to set your connection manager to use OLE DB and the Microsoft Jet 4.0 OLE Provider. This will connect you to any version of Access above version 4 from memory.

Once you have added the dataflow task, under the dataflow tab add you OLD db source, and then your destination. If you do need to do any transforms you would need to add them from the tool box under the data flow tab.

|||

Glenn,

Thanks; still having some issues; am trying to connect from access and dump to an excel spreadsheet.

I used the OLE DB to connect; and in my data flow task, I created a connection manager for Excel (specifing file path where the spreadsheet was located). I then identified an Excel Spreadsheet as my data flow destination point. When I executed, I got the following errors:

Error: 0xC0202009 at Pull From Access, Excel Destination [357]: An OLE DB error has occurred. Error code: 0x80040E21.
Error: 0xC0202025 at Pull From Access, Excel Destination [357]: Cannot create an OLE DB accessor. Verify that the column metadata is valid.
Error: 0xC004701A at Pull From Access, DTS.Pipeline: component "Excel Destination" (357) failed the pre-execute phase and returned error code 0xC0202025.

Anything I am doing wrong?

|||

Can anyone throw some light on this issue. Im facing almost a similar problem. I have an OLEDB source which connects to SQL server pulls some records out of a table and i want them to be exported to a Excel File which i have already created. So i added a New connection using the Connection Manager for Excel Files and connected to the already existing destination in which i have defined some column names. When i maped the columns it initially gave me some conversion errors for teh varchar fields, then finally i converted all the varchar fields to "Unicode text stream [DT_NTEXT]", now there were no conversion errors. But when i executed the package i got the following errors:

[Excel Destination [185]] Error: An OLE DB error has occurred. Error code: 0x80040E21.

[Excel Destination [185]] Error: Cannot create an OLE DB accessor. Verify that the column metadata is valid.

[DTS.Pipeline] Error: component "Excel Destination" (185) failed the pre-execute phase and returned error code 0xC0202025.

Any help is appreciated. Thanks a lot in advance.

|||

I got most of the things done (I created an Excel File and each time im creating sheets i.e., Creating and dropping tables which deltes the data and gives a fresh sheet to insert the data) but in my package im creating an Excel sheet/TAble using the Execute SQL statement

"CREATE TABLE `CUSTOMER_ORDER_ITEM` (`TransferDate` DateTime,
`ErrCode` LongText,
`ErrDesc` LongText,
`ErrData` LongText,
`ErrorStatus` Short
)
GO
"

before that im dropping the sheet/table using the below Execute SQL statement

"DROP TABLE `CUSTOMER_ORDER_ITEM` "

So it is throwing an error for the first time when the package is running. I need to know whether there is a way to check that the Sheet/Table exists before deleting the Sheet/Table.

Thanks in advance. Any other work around is also appreciated

|||

You can use GetOleDbSchemaTable to check whether the sheet exists.

http://support.microsoft.com/kb/309488