olivier pieri's WebLog

here is a small sample that show how to run an existing package from a .Net program.

This sample show how to replace target and source connection string and give a value to a package variable.

It show too how to register package event.

using Runtime = Microsoft.SqlServer.Dts.Runtime;

I was very suprise to don't find a tool to execute XMLA Script from the command line. Then I have decided to write it and it have been simple.

Let do it !

1) Use the useful namespace

using Microsoft.AnalysisServices.AdomdClient;

using System.Data;

using System.IO;

using System.Xml;

2) Start by connecting to Analysis Services 2005 using Adomd.

strConnectionString = "Data Source=" + ServerID + ";";

objConnection = new AdomdConnection();

Once you are connected to SQL Server 2005 with a SMO connection, you can start manage and create object. Here is an example on how you can create a stored procedure, we imagine that we have the code of a stored procedure in an embeded resources file. (this sample is written in C# and use a beta version of SQL Server 2005 and of the .Net Framework 2.0).

For this sample you need to use the following name space :

using Microsoft.SqlServer.Management.Smo;

using Microsoft.SqlServer.Management.Smo.Agent;

using Microsoft.SqlServer.Management.Common;

And here is the code of the sample :

StoredProcedure mySP;

string myResourceFile;

mySP = new StoredProcedure(sqlserver.database, "createNewYear");

myResourceFile = "MyAssemblieName.StoredProc.sql";

StreamReader sr = new StreamReader(System.Reflection.Assembly.GetExecutingAssembly().GetManifestResourceStream(myResourceFile), System.Text.Encoding.Default);

When you have an SMO connection, it is very easy to make an ADO.Net connection as the connection string is exactly the same. Then you just have to reuse it.

Here is a sample that supposed that the object ServerConn is an SMO ServerConnection.

mySQLConnection = new SqlConnection(ServerConn.ConnectionString);

Once you are connected to SQL Server 2005 with a SMO connection, you can start manage and create object. Here is an example on how you can create a table with a promary key (this sample is written in C# and use a beta version of SQL Server 2005 and of the .Net Framework 2.0).

The Object sqlserver, is the object created after the connection in this post about smo connection

Of course, In order to connect to SQL Server using SMO you have to reference the following namespace (and the corresponding assembly):

using Microsoft.SqlServer.Management.Smo;

using Microsoft.SqlServer.Management.Smo.Agent;

using Microsoft.SqlServer.Management.Common;

And the code to create the table is :

tbl = new Table(sqlserver.database, "MyTable");

col = new Column(tbl, "MyCol1", DataType.Int);

col.Nullable = false;

// Add the primary key index

idx = new Index(tbl, "PK_MyTable");

idx.IndexedColumns.Add(new IndexedColumn(idx, col.Name));

idx.IsClustered = true;

idx.IsUnique = true;

col = new Column(tbl, "myCol2", DataType.SmallInt);

col.Nullable = false;

// Create the table

This sample is written in C# and use SQL Server 2005 beta 2 and a beta version of the .Net Framework 2.0. It show how, with SMO you can Connect to SQL Server 2005

In order to connect to SQL Server using SMO you have to reference the following namespace (and the corresponding assembly):

using Microsoft.SqlServer.Management.Smo;

using Microsoft.SqlServer.Management.Smo.Agent;

using Microsoft.SqlServer.Management.Common;

1) The following code allow you to connect with the intergrated security :

This sample is written in C# and use SQL Server 2005 beta 2 and a beta version of the .Net Framework 2.0. It show how, with ADOMD.Net, you can retreive KPI that are defined in Analysis Services 2005.

1) reference the namespace for ADOMD:

using Microsoft.AnalysisServices.AdomdClient;

2) Build your connection string and connect to Analysis Services

Hi,

I am Olivier PIERI, a Microsoft consultant specialized in database technologies.

The goals of this blog is discuss about database and Data access technologies offered by Microsoft.Net  Framwork such as ADO.Net, ADOMD.Net and SMO.

I will put some sample code about new possibilities offer by the .Net framework 2.0 and SQL Server 2005.

Have fun.

Olivier