SSIS Error Code DTS_E_OLEDB_EXCEL_NOT_SUPPORTED

just published "another ssis package" on one of our servers runnig Windows 2008R2.
Go the following error :

Description: SSIS Error Code DTS_E_OLEDB_EXCEL_NOT_SUPPORTED: The Excel Connection Manager is not supported in the 64-bit version of SSIS, as no OLE DB provider is available.  End Error  Error: 2012-10-18 15:43:10.76     Code: 0xC00291EC     Source: create xls file for  Execute SQL Task     Description: Failed to acquire connection "DestinationConnectionExcel". Connection may not be configured correctly or you may not have the right permissions on this connection.  End Error  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  15:43:08  Finished: 15:43:10  Elapsed:  2.309 seconds.  The package execution failed.  The step failed.

 the fact is using 64bits you cannot use Microsoft.JET.OLEDB.4.0.
To work around this open the job step properties ans choose the execution option tab.

then check the following option below left of the properties window.

here is its , now you can you Jet Oledb for excel without issue !

SQL Template to delete SQL job, maintenance plan and schedule

Problem: 
on our test servers , or servers that used to be managed by third party companies, the sql job list can become ...messy.
I came with a SQL server template to delete the job, the maintenance plan and the schedule if unused.

1. Create the template:

open the "view Template" window:

the template menu appears by default at the right of the SSMS window.

Create a folder of you choice in the Template explorer list , right click and choose "new template"

Name the template as you wish , right click the template and choose "Edit".

2. Now copy  the code that you can download from here

within the code you will notice special strings , these are the template parameters :

3.Save the template
4. Using the template:

Create a new query on the server on which you want to run the template.
Then simply drag the template you just saved on the blank query window
The template code is then copied on the new query window.
Then here is the cool stuff: click  on CTRL+SHIFT+M and you will be prompted to enter the parameters:

The first parameter  is to specify the job name, the second parameter (Delete_Maintenance_plan) should be  set to 1 if you want to delete the associated maintenance plan. Of course this is only a simple script, error check should be added to check if there is actually a maintenance plan etc..

Nonetheless it is still useful, and shows the basics of using templates.

Free sql online formatter tool

Recently found out this TSQL Formatting tool that is worth sharing : Instant SQL Formatter
Really like it as there are many options, can really save some time and improve readability.

enjoy

sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029)

We still have an SQL 2000 production server from a third party supplier. The backups failed for several days and looking on serveral blogs I found that some had disk space issues.

I did not have a disk space issue BUT a consultant from the third party had restricted the rights of the DATA folder to administrators only !

so if you run into this error :

1. check that you have sufficient disk space
2. check the data folder security :)

How to select column names of a table

select COLUMN_NAME  from INFORMATION_SCHEMA.COLUMNS where  TABLE_NAME ='my_table'