Importing MySQL BLOB to MSSQL

Hi all!
I'm trying importing a MySQL database to my MSSQL server. I've donwloaded the MySQL ODBC drivers and I can connect succesfully throught a DSN to my MySQL server. Everything works fine EXEPT my BLOB field. I'm using a BLOB field for storing a binary file.

When I choose preview while importing my bob field is empty.

I'm desperate!

:eek:Viewing a BLOB is pretty much a waste of time. Use DTS or a linked server to move it, and life should be good.

-PatP

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

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

Importing Access Reports

Hi,
I'm trying to import some access reports into Report Services.
The reports are stored in a .adp file and connect to a MS SQL server database.
I'm using the import from Reporting Services in VS.NET, but am getting the
following error on 90% of the reports.
An error occurred while the report <<report name>> was being imported: The
given path's format is not supported.
Out of about 20 reports, it only successfully imports 4. All of the reports
work in the adp file fine. Have anyone seen this error before, and possibly
provide a fix or workaround to import the reports?
Thanks in advance.Anthony, hello. I'm getting this same error. Did you ever figure this out?
I would love to figure this out.
Thanks!
Shane Eckel
shane.eckel@.seattlesoftware.com
--
Thank You!
"Anthony" wrote:
> Hi,
> I'm trying to import some access reports into Report Services.
> The reports are stored in a .adp file and connect to a MS SQL server database.
> I'm using the import from Reporting Services in VS.NET, but am getting the
> following error on 90% of the reports.
> An error occurred while the report <<report name>> was being imported: The
> given path's format is not supported.
> Out of about 20 reports, it only successfully imports 4. All of the reports
> work in the adp file fine. Have anyone seen this error before, and possibly
> provide a fix or workaround to import the reports?
> Thanks in advance.
>