Connection manager for Access in SSIS

Question

Connection manager for Access in SSIS

namrata.dhanawade-1143388

Ten Centuries

Points: 1269
More actions
December 17, 2009 at 6:44 am

#136014

Hi
I want to export some dta from SQL to a Tbale in the Access database.
I am not sure what connection manager should be used.
Also, what syntax should be used to delete current records in that table before I create a dataflow task to export it?

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

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

namrata.dhanawade-1143388 Ten Centuries Points: 1269 More actions · Answer 1

OK. I know about the connection manager now.

However in a Execute SQL Task; I want to delete rows from a table in the Access database for which the Jet ole db connection has been created.

If I write -

Delete from tablename

it gives me an error when I try to parse the query - Object reference not set to an instance of an object.

Where am I going wrong?

COZYROC One Orange Chip Points: 28499 More actions · Answer 2

namrata.dhanawade-1143388 (12/17/2009)
Hi
I want to export some dta from SQL to a Tbale in the Access database.
I am not sure what connection manager should be used.
Also, what syntax should be used to delete current records in that table before I create a dataflow task to export it?

You can use OLEDB Connection / Destination (Use Microsoft Jet 4.0 OLE DB Provider). Keep in mind this driver is only available for 32bit mode. So if you are executing on 64bit machine, make sure your package is setup to execute in 32bit. If you can use third-party components, check the commercial CozyRoc ODBC Destination component. It does provider 5x faster performance compared to the regular OLEDB Destination.

For deleting the records, you can use regular SQL syntax.

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

COZYROC One Orange Chip Points: 28499 More actions · Answer 3

namrata.dhanawade-1143388 (12/17/2009)
OK. I know about the connection manager now.
However in a Execute SQL Task; I want to delete rows from a table in the Access database for which the Jet ole db connection has been created.
If I write -
Delete from tablename
it gives me an error when I try to parse the query - Object reference not set to an instance of an object.
Where am I going wrong?

Do not try to parse. I think there is a bug in the user interface. Make a test, executing your package and see how it goes.

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

namrata.dhanawade-1143388 Ten Centuries Points: 1269 More actions · Answer 4

OK.

Now it says -

Cannot convert between unicode and non-unicode string data types

COZYROC One Orange Chip Points: 28499 More actions · Answer 5

namrata.dhanawade-1143388 (12/21/2009)
OK.
Now it says -
Cannot convert between unicode and non-unicode string data types

Use derived column transformation to convert the failing column.

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/