SSIS 2005 - Read data from an excel 2007 file and format it

In order to read data from an Excel 2007 file, create an OLE DB connection and add file path and the file name as the server name.


Read data from [CountryOverview] sheet.
If you select "Table name or View name" as the data access mode, you could select the sheet name which you wish to read from the drop down menu. Instead of that, as you can see in the image below, a SQL query can be writted to read data from excel sheet.

SQL Server Agent Jobs to Backup / Restore SSAS Cube

This post demostrates a few scripts which can be used within SQL Server Agent Jobs.

XMLA Script to backup a cube
<Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
  <Object>
    <DatabaseID>Company UAT Cube</DatabaseID>
  </Object>
  <File>Company UAT Cube.abf</File>
  <AllowOverwrite>true</AllowOverwrite>
  <Password>AABB01</Password>
</Backup>

XMLA Script to restore a .ABF file

<Restore xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
  <File>C:\Data\SQL Server Data\MSSQL.2\OLAP\Backup\Company Test Cube.abf</File>
  <DatabaseName>Company Test Cube</DatabaseName>
  <AllowOverwrite>true</AllowOverwrite>
  <Password>AABB01</Password>
</Restore>
SSAJ - operating system command to copy a file
copy "D:\FTP Root\Test Cube.abf" "D:\Microsoft SQL Server 2005\Analysis Services\MSSQL.2\OLAP\Backup\Test Cube.abf" /Y

SSAJ - operating system command to run a batch file
cmd.exe /c "C:\PharmaAnalytics\Daiichi Sankyo Europe Instance\System\Scripts\CopyTestCubeToSrerver1ABFBackups.bat"

Batch script to FTP an ABF file
@echo off
echo user USERNAME PASSWORD>ftp.ftp
echo put "C:\Data\SQL Server Data\MSSQL.2\OLAP\Backup\Cube Test.abf">>ftp.ftp
echo bye>>ftp.ftp
ftp -n -s:ftp.ftp 94.1.1.2
del /q ftp.ftp