Ycsoftware.net

I am writing this post for all the Microsoft pundits out there whose answers to a question such as “How do i export data to an excel spreadsheet using ssis?” is the following “it is very easy, just drop the control to your working area and run the package”. Guess what it is not always that straightforward and everything changes as soon as you are trying to export a text column from your database. You ended up finding hacks since the tool (SSIS) does not do what it is supposed to do well: “Export my damn data to excel no matter what it is.” Also, the last time i checked “text” what a common data type in databases, Why would I even need to convert text? why doesn’t the tool handle that?

[Excel Destination [299]] Error: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E21.
[Excel Destination [299]] Error: Cannot create an OLE DB accessor. Verify that the column metadata is valid.
[DTS.Pipeline] Error: component "Excel Destination" (299) failed the pre-execute phase and returned error code 0xC0202025.

The error above does not tell us s??t. After dealing with the error above for almost half a day, i finally found a solution (Honestly i would not call it a solution but a hack). Let’s rewind and see how i got the error message in the first place.

I am trying to just export SQL data to an excel spreadsheet using SSIS (sql server 2005, BIDS – business intellingence development studio). My data contains a text field and i could not for sake of it export that text field to the excel spreadsheet. I have used everything (database conversion , derived column ….) without a success. Then i recalled reading something about the limitations of the Jet Engine which is the Microsoft Technology used to communicate with these excel spreadsheets.

The problem is that the excel destination task is reading the excel sheet and decide what the type of the fields should be (based on the field's content). This is stupid because why is it that the task is checking my excel sheet when the later does not have any data yet. For example if you have a column on the excel sheet that has a number, the excel destination task may expect you to send an integer to this column and so on

So if you want to send a text field from your database to an excel spreadsheet just follow the following procedure:

On your Excel spreadsheet template find the column that will contain the text data, go ahead and enter a very large text to it. YOU WANT THE JET ENGINE TO SET THE TYPE OF THIS COLUMN TO "TEXT".

-You are tricking the jet engine to think that it must assign a text type to this field. Personally i do not like workarounds but this hack has worked for me.

now run your DTS package, you should not have any issue.

Here is a sample excel sheet to give you a better idea:
http://ycsoftware.net/custom/dts_ole_sample_file.xls

“The RsyncServer service on Local Computer started and then stopped. Some services stop automatically if they have no work to do, for example, the Performance Logs and Alerts service.”

If you are getting this error, you are not alone. When I checked the event viewer i see something like this:

Description:
The description for Event ID ( 0 ) in Source ( RsyncServer ) cannot be found. The local computer may not have the necessary registry information or message DLL files to display messages from a remote computer. You may be able to use the /AUXSOURCE= flag to retrieve this description; see Help and Support for details. The following information is part of the event: RsyncServer: PID 4536: `RsyncServer’ service stopped, exit status: 10.

What the heck is this? How am i supposed to fix this issue with the above error message? the only thing relevant from this error message could be “exit status : 10” but when I googled it, i could not find anything. After some more research some guy mentioned that to check the application’s log file (C:\Program Files\ICW\rsynd.log), which i did and i found this:

2011/05/21 14:29:34 [6012] rsyncd version 3.0.8 starting, listening on port 873
2011/05/21 14:29:34 [6012] bind() failed: Address already in use (address-family 2)
2011/05/21 14:29:34 [6012] unable to bind any inbound sockets on port 873
2011/05/21 14:29:34 [6012] rsync error: error in socket IO (code 10) at socket.c(544) [Receiver=3.0.8]

Now we talking. All that time , I had a program that it already using port 873 so I downloaded currports (http://www.nirsoft.net/utils/cports.html) and find that line :
rsync.exe 1480 TCP 873 0.0.0.0 0.0.0.0 Listening C:\Program Files\Delta Copy\rsync.exe

Just per curiosity i tried to check the content of the folder C:\Program Files\Delta Copy\ then i found this:

The stupid directory is empty (B??? Sh??). How the heck is the service is still running?

I eventually killed the process using currports (right click) mentioned above and i was able to start the rsyncserver successfully.

The moral of the story is

If you are getting this error when trying to start the rsyncserver, there is already a service using port 873.

You can change the port that rsyncserver is listening to using the following instructions :
http://www.itefix.no/i2/node/11315
or you can identify the program that is using the port and take the appropriate action.

CTRL+S and Putty

Html Tables and CSS

An OLE DB error has occurred. Error code: 0x80040E21. Error: Cannot create an OLE DB accessor SSIS

Cannot connect to Database server – Cannot start SSH tunnel manager (mysql workbench 5.2.33 or earlier)

The RsyncServer service on Local Computer started and then stopped.