Answer My Searches

So I set up a SQL Agent Job to run my SSIS package following these steps.

Here’s the crazy thing. The package would run perfectly when I ran it directly or via a batch file, but when I ran it through the SQL Agent Job it would die and I would see a message like this in the log file I set up:

DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Connection 1" failed with error code 0xC0209303. There may be error messages posted before this with more information on why the AcquireConnection method call failed.


I tried all of the work-arounds mentioned on this page. However none of them helped.

The answer it turns out is that 64 bit SQL Server Integration Services can’t handle Excel (xls) files! The trick is to use the 32 bit runtime of dtexec. To make the SQL Agent Job do this, go to the execution options tab of the job step and select “use 32 bit runtime”.

The newer versions of Office seem to have broken from the standard help/about layout that all other programs in the universe use. Here’s how you get to the new about menu.

For Excel 2007:

Click on the Office-logo at the top-left, then click on the “Excel options” button, then choose “Resources” in the left menu and then click on the “About” button.

I assume other Office products will work the same way. Post a comment if you find they don’t.

Source

Have you ever wanted to sort or filter by the background color of cells in Excel? There are no functions built into Excel to do such a thing, but you can still do it with this simple user defined function:

Function Color(MyRef As Range) As Long
Color = MyRef.Interior.ColorIndex
End Function


I found installing it a bit convoluted (at least in Excel 2003). First you want to make sure you get it into your personal workbook so it will always be available to you. But when I went to the Visual Basic editor in Excel, it wouldn’t show me my personal workbook.

What I did was record a simple macro in Excel, being sure to select to store it in the “personal macro workbook” option. Do something simple like type in a cell, and hit stop recording. Then go into the Visual Basic editor again, and you can see your personal workbook (PERSONAL.XLS). Place the function above in the same module as the macro you just recorded. You can delete the macro you recorded now.

To use the function you have to call it in a funny way. Thus to get the color of cell A3 you would type in a cell:

=PERSONAL.XLS!Color(A3)

And now you can get numeric values for the background colors in your cells! Yay!

It turns out that this nifty pyExcelerator program will let you write an Excel formatted document directly from Python.

While you could always use COM automation with the Python Win32 extensions to make Excel documents, I think this library is the way to go. It lets you write an Excel file without even having to have Excel installed, and you don’t even need to be running on Windows. That’s pretty neat. It sounds like an easy way to offer an Excel output format option from your application or website, without requiring any messing with Windows or Excel.

Sadly there wasn’t any friendly documentation for this project online, so here I offer my first code snippet using this package. I mostly figured it out from the example files included with the package. It writes a few rows of data and styles the data depending on content.
(more…)

Here’s the syntax to write to a CSV file:

import csv
w=csv.writer(file(r'C:\greg.csv','wb'),dialect='excel')
some_values=[[1,2,3],['A','B','C'],[4,'"5"','ab,c']]
w.writerows(some_values)

Make sure you use ‘wb’ when you open the file. Otherwise I’ve found that it will put a blank line between each row at least when Excel opens it.

Here’s the syntax for reading a csv file (from the documentation):

import csv
reader = csv.reader(open("some.csv", "rb"))
for row in reader:
    print row[0], row[-1]

And here’s the link to the full documentation:
http://docs.python.org/lib/module-csv.html
And this takes you straight to the examples in the documentation.

Update:
Another useful writeup of the CSV module.

[tags]Python, csv, csv file, comma delimited, comma seperated values, comma seperated, Python documentation[/tags]