SSAS XMLA Cube Script Backup

Most of time its taking long time to generate XMLA script for cube.But consider a scenario you have 10 cubes and you want to back of all the cube.XMLA script that what should be the solution. As a developer its hard to wait for generate script one after another in ssms. Below is my finding which will generate all the cube script with specify folder  and it will not make any load on your work not even SSMS will have any impact while you are doing your development.

Run this script in poweshell window and press enter. all cube script will generate in quick.

Step 1: Open powershell

Step 2: Use below code with server and folder location

$serverName = "Servername "
$outputFolder = "Folder Path"

## load the AMO and XML assemblies into the current runspace
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices") > $null
[System.Reflection.Assembly]::LoadWithPartialName("System.Xml") > $null
$dateStamp = (get-Date).ToString("yyyyMMdd")

## connect to the server
$svr = new-Object Microsoft.AnalysisServices.Server
$svr.Connect($serverName)
foreach ($db in $svr.Databases)
{
    write-Host "Scripting: " $db.Name
    $xw = new-object System.Xml.XmlTextWriter("$($outputFolder)DBScript_$($db.Name)_$($dateStamp).xmla", [System.Text.Encoding]::UTF8)
    $xw.Formatting = [System.Xml.Formatting]::Indented
    [Microsoft.AnalysisServices.Scripter]::WriteCreate($xw,$svr,$db,$true,$true)
    $xw.Close()
}
$svr.Disconnect()

Thanks,
Enjoy!! 

Lets Play with XML Data....(XML data import to Sql Server Table )

 Lets Today we will take a trip on xml data .I faced many of insurance company having data from different source system . Even still people are using foxpro database ( the first database for Microsoft) , for these kind of source or for some other you can install a driver to pull data from different source system . Point is coming  you are working in a stock market company and there is a API (Application Program Interface )which is having all update stock market data in web . I remember I used to have a scenario where data was coming from web even data is updating in web and we do not have any control to  work on it . then I came with a solution for below one which is dynamically retrieve data from web using a stored procedure and insert to a sql table .There is so many ways . Its always depend on your node of XML how  the node is define .

Lets  take a below example 

Here i have my Yahoo  Finance value site . Where data is always updating under time .Basically its Stock market site and its a free API.any body can use these data .

Link :  http://finance.yahoo.com/webservice/v1/symbols/allcurrencies/quote

so this link having data in the XML format .

lets check with the stored procedure 

declare @doccount   INT;

declare @XMLString     XML;

declare @SourceURL         NVARCHAR(255);

declare @FILE_Path        NVARCHAR(255);

declare @Command         NVARCHAR(255);

declare @SQL_Script         NVARCHAR(255);

declare @DestinationTable        TABLE(DATA XML);

SET @SourceURL  = 'http://finance.yahoo.com/webservice/v1/symbols/allcurrencies/quote';---Url

SET @FILE_Path = 'C:\Users\sitakanta\Downloads\New folder\quotes.xml';---File Patha Define 

SET @Command = '

C:\Windows\System32\WindowsPowerShell\v1.0\powershell (new-object System.Net.WebClient).DownloadFile( ''' + 

@SourceURL + ''',''' + @FILE_Path + '''  )'

---Downloaded

--print  @Command

EXEC master.dbo.xp_cmdshell @Command, no_output -----Query to download

-- Loading the Downloaded File into the XML variable

SET @SQL_Script = 'SELECT BulkColumn FROM OPENROWSET( BULK ''' + @FILE_Path + ''', SINGLE_BLOB ) AS a'

INSERT @DestinationTable EXEC(@SQL_Script);

SELECT @XMLString = data from @DestinationTable 

EXEC sp_xml_preparedocument @doccount OUTPUT, @XMLString;

-----Output Data

SELECT * FROM OPENXML(@doccount, N'//list/resources/resource')  

WITH ( name    VARCHAR(10) 'field[@name="name"]',

price   VARCHAR(10) 'field[@name="price"]',

symbol  VARCHAR(10) 'field[@name="symbol"]',

ts      VARCHAR(10) 'field[@name="ts"]',

type    VARCHAR(10) 'field[@name="type"]',

volume  VARCHAR(10) 'field[@name="volume"]');

EXEC sp_xml_removedocument @doccount;

And here is the output :

Let me know for any info.

Regards,

Sita

SSIS Tune Up

SSIS : Tuning Buffer Size

Tuning the Buffer Size for the data flow task in SSIS

The buffer size used is the smaller of 2 figures.

1) defaultbuffersize
2) defaultmaxbufferrows * row width

defaultbuffersize has a default of 10 MB (10485760 B)

You can change this to amaximum of 100 MB (104857600 B), which I have done and is the figure I have used here.

Calculating Row Width

I'm using a simple example of a single target table here.

The row width calculation gets messy if you try it from the table definition i.e. adding the sizes of columns (and remembering 2 bytes per character for unicode columns etc)

I chose to use the table size data and work out the average -

sp_spaceused 'dbo.bigtable'

name	rows	reserved	data	index_size	unused
bigtable	144451461	63064384 KB	44740776 KB	18317304 KB	6304 KB

So, data size in KB 44740776

data in Bytes = 44740776 * 1024 = 45814554624 B

Divide by row count ,
= 45814554624 / 144451461

317.16 Bytes/row

Row Width = 317 Bytes.

From the row width figure and the desire to put as much in the buffer as possible (to match the 100MB defaultbuffersize) we can calculate defaultmaxbufferrows.

Calculating DefaultMaxBufferRows

100MB buffer = 104857600 bytes
Row Width = 317 Bytes.

defaultmaxbufferrows = BufferSize / Row Width

= 104857600 / 317

= 330781 rows

So to ensure maximum buffer usage here we set -

defaultbuffersize = 104857600
defaultmaxbufferrows = 330781 (though I'd most likely round down to a the nearest hundred).

Read more: http://sqlsolace.blogspot.com/2010/06/ssis-tuning-buffer-size.html#ixzz2zGSzvcvI

How to run SSIS package using C# code

Hello friends,

Here again i am going to show you how you can call an ssis package using C# code. as we know we have execute ssis package component to call other package but if you are going to call a set of ssis package and all those package location store in a database , in that case execute package will open different dataflow window to call one after another ssis package. It’s always not good. So we can create a dynamic string with a variable and we can use that variable inside a C# to call number of packages

.
Here is the code (a packge name as calculated columns .dtsx)


using System;

using Microsoft.SqlServer.Dts.Runtime;

namespace RunFromClientAppCS

{

class Program

{

static void Main(string[] args)

{

string pkgLocation;

Package pkg;

Application app;

DTSExecResult pkgResults;

pkgLocation =

@"C:\Program Files\Microsoft SQL Server\100\Samples\Integration Services" +

@"\Package Samples\CalculatedColumns Sample\CalculatedColumns\CalculatedColumns.dtsx";

app = new Application();

pkg = app.LoadPackage(pkgLocation, null);

pkgResults = pkg.Execute();

Console.WriteLine(pkgResults.ToString());

Console.ReadKey();

}

}


enjoy !!

Different ways to Execute SSIS Package

Different ways to execute SSIS Package

When we develop a package, we execute it through Business Intelligence Development Studio (BIDS), for testing the package functionality. Once the package is developed and signaled green for a proper intended behavior we deploy it and then schedule it and we are done as a developer. But there are some other ways also apart from BIDS and SQL Agent Job, that can be used to execute a package.
Let's see how many different ways we can execute an SSIS package in:?
1. Using Dtexec utility from command prompt or DOS prompt
2. Using Dtexec utility from powershell or DOS prompt
3. Using DtexecUI also known as Execute Package Utility
4. Using code (C# or VB.Net)
5. Using a batch file and schedult it through Task scheduler
6. Using sp_start_job procedure to execute a sql agent job
7. Using xp_cmdshell to execute a package deployed in MSDB database or from File System
8. Using BIDS
9. Using SQL Agent job

How Checkpoints Work

Uses of strip line in SSRS to indicate today's date

hi to all,
 here i am posting a report which will indicate today's date by aline . this kind of reports is implementing in those data which will have a dead line target value .
 so i wrote a simple query which will show u 5 days befor and 5 days after of current date
 the query is :

DECLARE
 @table TABLE (dates datetime) DECLARE @a int
SELECT
 @a = - 5 WHILE (@a != 6) BEGIN DECLARE @dat datetime
 SELECT
 @dat =(SELECTDATEADD(DAY, @a, GETDATE()))
 INSERT
 INTO @table VALUES (@dat)
 SELECT
 @a = @a + 1 END
 SELECT
 CONVERT(VARCHAR(8), dates, 3) AS [DD/MM/YY]
 FROM
 @table
then i took a bar chart. i put the value in x axis. in the properties window u wil get strip line there i configure as i mentioned in the figure

 afetr that report look like below.

.. please try it its a very simple interview queston also ,

thanks& regards
sitakanta