SSIS: Package using SQL Authentication and DontSaveSensitive as ProtectionLevel

In this post i am trying to look into steps to follow while running SSIS package using SQL Authentication and DontSaveSensitive as ProtectionLevel.

I created a simple SSIS package using a connection manager going to SQL Server using SQL authentication, I have my ProtectionLevel as EncryptSensitiveWithUserKey

clip_image001

clip_image003

My package has an OLEDB Source (using connection manager above) which queries a table in database and dumps few rows in flat file.

clip_image005

clip_image006

With above settings ,when I execute my package, Its works like a charm.

clip_image007

Now, I changed the ProtectionLevel  to DontSaveSensitive

clip_image008

And package failed

clip_image009

With following error

SSIS package “Package.dtsx” starting.

Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning.

Error: 0xC0202009 at Package, Connection manager “runeet2k8.sa”: An OLE DB error has occurred. Error code: 0x80040E4D.

An OLE DB record is available.  Source: “Microsoft SQL Native Client”  Hresult: 0x80040E4D  Description: “Login failed for user ‘sa’.”.

Error: 0xC020801C at Data Flow Task, OLE DB Source [1]: The AcquireConnection method call to the connection manager “runeet2k8.sa” failed with error code 0xC0202009.

Error: 0xC0047017 at Data Flow Task, DTS.Pipeline: component “OLE DB Source” (1) failed validation and returned error code 0xC020801C.

Error: 0xC004700C at Data Flow Task, DTS.Pipeline: One or more component failed validation.

Error: 0xC0024107 at Data Flow Task: There were errors during task validation.

SSIS package “Package.dtsx” finished: Failure.

What we see above is expected as we have sensitive information in our package (password for SQL account) and have chosen DontSaveSensitive as protection level. The affect is Password being cleared from our connection manager hence login failure.

For this situation, We would have to save this password somewhere outside the package. I chose to save it in configuration file as shown below. I used XML Configuration file

clip_image011

clip_image012

I had to chose correct connection manager, Its called runeet2k8.sa in my case

clip_image013

This is the XML configuration file that BIDS wrote for me

<?xml version=”1.0″?>

<DTSConfiguration>

      <DTSConfigurationHeading>

            <DTSConfigurationFileInfo GeneratedBy=”Domain\UserName” GeneratedFromPackageName=”Package” GeneratedFromPackageID=”{77FB98FB-E1AF-48D9-8A43-9FD6B1790837}” GeneratedDate=”22-12-2009 16:12:59″/>

      </DTSConfigurationHeading>

      <Configuration ConfiguredType=”Property” Path=”\Package.Connections[runeet2k8.sa].Properties[Password]” ValueType=”String”>

                  <ConfiguredValue></ConfiguredValue>

      </Configuration>

</DTSConfiguration>

 

I have to go in and type password value.

 

<?xml version=”1.0″?>

<DTSConfiguration>

      <DTSConfigurationHeading>

            <DTSConfigurationFileInfo GeneratedBy=”FAREAST\runeetv” GeneratedFromPackageName=”Package” GeneratedFromPackageID=”{77FB98FB-E1AF-48D9-8A43-9FD6B1790837}” GeneratedDate=”22-12-2009 16:12:59″/>

      </DTSConfigurationHeading>

      <Configuration ConfiguredType=”Property” Path=”\Package.Connections[runeet2k8.sa].Properties[Password]” ValueType=”String”>

  <ConfiguredValue>Password</ConfiguredValue>

</Configuration>

</DTSConfiguration>

Now i have a package with ProtectionLevel as DontSaveSensitive and password value for connection manager saved in a configuration file outside the package. Package executed absolutely fine after this.

clip_image014

I went ahead and set this package as Integration Services Job under SQL Agent.

clip_image015

Executed the job and it ran successfully.

clip_image016

One important thing to note is that if you are moving your package from your development machine to say test server for execution. You would have to move your configuration file as well and put it at same path on target machine where its placed on your development box.

For example, on my box, the config file PackageConfig.dtsconfig  is placed at D:\Cases\Integration Services Project1\PackageConfig.dtsconfig and if I am moving my package from my development machine to somewhere else, I would have to move my config file as well and place it at location D:\Cases\Integration Services Project1\ on that machine.

If you don’t have dtsconfig at expected location, Job would fail.

If you done want to create same complex folder path for dtsconfig on target machine as its on your dev box (eg: D:\Cases\Integration Services Project1\) You could place this dtsconfig file anywhere you want and give the pointer of this path from Configuration tab  under you SSIS job step.

clip_image001[4]

SQL Query: How to retrieve the Table columns from a stored procedure

SELECT
name AS ColumnName
,system_type_name AS DataType
,CASE
WHEN is_nullable=0 THEN ‘NOT NULL’
WHEN is_nullable=1 THEN ‘NULL’
END AS IS_NULLABLE
,column_ordinal AS OrdinalPosition
,(‘Col_’ + CAST(column_ordinal AS varchar(4))) AS Alias
FROM sys.dm_exec_describe_first_result_set
(N'[dbo.Registration]’, null, 0) ;

 

SQL SERVER – How to retrieve the metadata of a stored procedure

In my earlier articles, I wrote about sys.dm_exec_describe_first_result_set
(A new dynamic management view shipped with SQL Server 2012), it is very handy when you need to know the metadata of the first possible result set of any Transact SQL. Today, I came across an issue when I tried to get the metadata of a stored procedure in earlier version of SQL Server and wanted to use the list of columns for further manipulations. However, I could not find a proper solution like sys.dm_exec_describe_first_result_set in the earlier version of SQL Server. So I started developing the solution.

Given below are the solution.

SOLUTION 1 : For SQL Server 2012 and above using sys.dm_exec_describe_first_result_set

1
2
3
4
5
USE AdventureWorks2012;
GO
SELECT FROM sys.dm_exec_describe_first_result_set
(N'[dbo].[uspGetManagerEmployees] 16', null, 0) ;
 --OUTPUT

metadata of stored procedure.1.1

SOLUTION 2 : For SQL Server 2005 and above using OPENROWSET

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
USE AdventureWorks2012
GO
--DROP TABLE #temp1
GO
SELECT * INTO  #temp1
FROM OPENROWSET('SQLNCLI', 'Server=(local);uid=sa;pwd=test',
'EXEC AdventureWorks2012.[dbo].[uspGetManagerEmployees] 16');
GO
SELECT * FROM tempdb.sys.columns
WHERE object_id=object_id('tempdb..#temp1')
GO
--View the column name / metadata of the
--above created temporary table.
--OUTPUT

metadata of stored procedure.1.2

Regex: query examples and website links

source link:

  1. debuggex.com
  2. regex101.com

OS_Rec_April 2017_EMMain_06Q_26052017082756_a8abaa7c46_20170530_174428_FFP Feb 2018

Regular expression to find month names:

(?<Month>(?:Jan(?:uary))|(?:Feb(?:ruary))|(?:Mar(?:ch))|(?:Apr(?:il))|(?:Apr(?:il)))|(?:May(?:))|(?:Jun(?:e))|(?:Jul(?:y))|(?:Aug(?:ust))|(?:Sep(?:tember))|(?:Oct(?:ober))|(?:Nov(?:ember))|(?:Dec(?:ember))

 

(\b\d{1,2}\D{0,3})?\b(?:Jan(?:uary)?|Feb(?:ruary)?|Mar(?:ch)?|Apr(?:il)?|May|Jun(?:e)?|Jul(?:y)?|Aug(?:ust)?|Sep(?:tember)?|Oct(?:ober)?|(Nov|Dec)(?:ember)?)\D?(\d{1,2}\D?)?\D?((19[7-9]\d|20\d{2})|\d{2})

 

Regular expression tofind Month Year:

((?:(?<Month>(?:Jan(?:uary))|(?:Feb(?:ruary))|(?:Mar(?:ch))|(?:Apr(?:il))|(?:Apr(?:il)))|(?:May(?:))|(?:Jun(?:e))|(?:Jul(?:y))|(?:Aug(?:ust))|(?:Sep(?:tember))|(?:Oct(?:ober))|(?:Nov(?:ember))|(?:Dec(?:ember))) (?<Year>([0-9]{4})))

 

 

 

RecOrPostRec:

(?:[_])(?<RecOrPostRec>(?:(Post)?(?:Rec)))(?:[_])

 

CCGCode:

(?:[_])(?<CCGCode>[0-9][0-9a-zA-Z]{2})(?:[_])

 

FileType (EMMain/EPMain):

(?:[_])(?<FileType>[a-zA-Z]{6,15})(?:[_])(?:(?<CCGCode>[0-9][0-9a-zA-Z]{2})(?:[_]))

 

Full regex:

 

(?:[_])(?<RecOrPostRec>(?:(Post)?(?:Rec)))(?:[_])((?:(?<Month>(?:Jan(?:uary))|(?:Feb(?:ruary))|(?:Mar(?:ch))|(?:Apr(?:il))|(?:Apr(?:il)))|(?:May(?:))|(?:Jun(?:e))|(?:Jul(?:y))|(?:Aug(?:ust))|(?:Sep(?:tember))|(?:Oct(?:ober))|(?:Nov(?:ember))|(?:Dec(?:ember))) (?<Year>([0-9]{4})))(?:[_])(?<FileType>[a-zA-Z]{6,15})(?:[_])(?:(?<CCGCode>[0-9][0-9a-zA-Z]{2})(?:[_]))

Save:

(?<RecOrPostRec>_Rec_)|(?<Month>(?:Jan(?:uary))|(?:Feb(?:ruary))|(?:Mar(?:ch))|(?:Apr(?:il))|(?:Apr(?:il)))|(?:May(?:))|(?:Jun(?:e))|(?:Jul(?:y))|(?:Aug(?:ust))|(?:Sep(?:tember))|(?:Oct(?:ober))|(?:Nov(?:ember))|(?:Dec(?:ember))|(?<Year>[0-9]{4}_)|(?<FileType>_EMMain_)|(?<CCGCode>_[0-9][0-9a-zA-Z]{2}_)

 

 

 

 

SSIS C#: Read two variables, join them in a two dimensional array, assign to an object variable

SSIS C#: Read two variables, join them in a two-dimensional array, assign to an object variable.

–write DataTable to an object variable

Part 1 plan:

  1. I will receive value in project parameter. For example: SSIS Project variable CCGCode=0A;1D;2J, SSIS Project variable PCTCode=0A;1D;2J
  2. Read the multiple values from the project parameter separated by semicolon;
  3. Split the values by semicolon then insert into a DataTable
  4. Assign the DataTable to an Object Variable

Part 2 plan:

  1. Foreach ADO Enumerator
    1. Collection: Read from the Object Variable
    2. Map the values to a string variable
  2. Use the values to in the task

public void Main()
{

DataTable dt = new DataTable();
OleDbDataAdapter da = new OleDbDataAdapter();
dt.Columns.Add(“CCGCode”, typeof(string));
dt.Columns.Add(“PCTCode”, typeof(string));

string[] ccgCodes, pctCodes;
ccgCodes = Dts.Variables[“$Project::CCGCode”].Value.ToString().Split(‘;’);
pctCodes = Dts.Variables[“$Project::PCTCode”].Value.ToString().Split(‘;’);
for (int i = 0; i < ccgCodes.Length; i++)
{
DataRow dr = dt.NewRow();

//without using the string.IsNullOrEmpty() check it fails, even the input does not have any null values.
dr[“CCGCode”] = string.IsNullOrEmpty(ccgCodes[i])? “NUL” : ccgCodes[i];
dr[“PCTCode”] = string.IsNullOrEmpty(pctCodes[i]) ? “NUL” : pctCodes[i];
dt.Rows.Add(dr);
//MessageBox.Show(dr[“CCGCode”].ToString());
//MessageBox.Show(dr[“PCTCode”].ToString());
//codes.Add(new CCGPCTCodes(ccgCode[i], pctCode[i]));
}

try
{
Dts.Variables[“User::objCodes”].Value = dt;
}
catch (Exception e)
{
Dts.Events.FireError(0, null, e.Message, null, 0);
}
}

C#: Generate Scripts for database for SQL Server

— Generate Scripts for database objects with SMO for SQL Server

Generate Scripts for database objects with SMO for SQL Server

https://www.mssqltips.com/sqlservertip/1826/getting-started-with-sql-server-management-objects-smo/

 

/*working code*/

public void Main()public void Main()

{

ETLControlDetails etl =GetETLControlDetails();

GenerateDropTableScript(etl);

GenerateCreateTableScript(etl);

MessageBox.Show(sb.ToString());

}

/*working code*/

StringBuilder sb = new StringBuilder();

#region public struct ETLControlDetails
public struct ETLControlDetails
{
public string Server { get; set; }
public string SourceServer { get; set; }
public string SourceInstance { get; set; }
public string SourceDatabase { get; set; }
public string SourceSchema { get; set; }
public string SourceTable { get; set; }
}
#endregion

 

#region public ETLControlDetails GetETLControlDetails()
public ETLControlDetails GetETLControlDetails()
{

ETLControlDetails etl = new ETLControlDetails();
etl.Server = Dts.Variables[“User::SourceServer”].Value.ToString() + “\\” + Dts.Variables[“User::SourceInstance”].Value.ToString();
etl.SourceServer = Dts.Variables[“User::SourceServer”].Value.ToString();
etl.SourceInstance = Dts.Variables[“User::SourceInstance”].Value.ToString();
etl.SourceDatabase = Dts.Variables[“User::SourceDatabase”].Value.ToString();
etl.SourceSchema = Dts.Variables[“User::SourceSchema”].Value.ToString();
etl.SourceTable = Dts.Variables[“User::SourceTable”].Value.ToString();
return etl;
}
#endregion

#region public void ConnectToServer(string server)
public Server ConnectToServer(string server)
{
Server myServer = new Server(server);
try
{
/*Using windows authentication*/
myServer.ConnectionContext.LoginSecure = true;
myServer.ConnectionContext.Connect();
////
//Do your work
////
if (myServer.ConnectionContext.IsOpen)
myServer.ConnectionContext.Disconnect();
///*Using SQL Server authentication*/
//myServer.ConnectionContext.LoginSecure = false;
//myServer.ConnectionContext.Login = “SQLLogin”;
//myServer.ConnectionContext.Password = “entry@2008”;
LogInformation(“ConnectToServer”, String.Format(“Connected to the server {0}”, server));
}
catch (Exception e)
{
LogError(“ConnectToServer – Failed connecting to the server”, e);
}
return myServer;
}
#endregion

 

#region public void GenerateCreateTableScript(ETLControlDetails etl)
public void GenerateCreateTableScript(ETLControlDetails etl)
{
/*generate create table script for a individual table */
Server server = new Server(etl.Server);
Database db = server.Databases[etl.SourceDatabase.ToString()];
List<Urn> list = new List<Urn>();
DataTable dataTable = db.EnumObjects(DatabaseObjectTypes.Table);

foreach (DataRow row in dataTable.Rows)
{
list.Add(new Urn((string)row[“Urn”]));
}
Scripter scripter = new Scripter();
scripter.Server = server;
scripter.Options.IncludeHeaders = true;
scripter.Options.IncludeIfNotExists = true;
scripter.Options.SchemaQualify = true;
scripter.Options.SchemaQualifyForeignKeysReferences = true;
scripter.Options.NoCollation = true;
scripter.Options.DriAllConstraints = true;
scripter.Options.DriAll = true;
scripter.Options.DriAllKeys = true;
scripter.Options.DriIndexes = true;
scripter.Options.ClusteredIndexes = true;
scripter.Options.NonClusteredIndexes = true;
scripter.Options.ToFileOnly = true;
//scripter.Options.FileName = @”C:\Download\script_custom.sql”;
/*get the all the tables for a database*/
//scripter.Script(list.ToArray());

/*get a particular table script only*/
Table myTable = db.Tables[etl.SourceTable, etl.SourceSchema];
//scripter.Script(new Urn[] { myTable.Urn});

StringCollection sc = scripter.Script(new Urn[] { myTable.Urn });
foreach (string script in sc)
{
sb.AppendLine();
sb.AppendLine(“–create table”);
sb.Append(script + “;”);
}
}
#endregion

 

#region public void GenerateCreateTableScript_V2(ETLControlDetails etl)
public void GenerateCreateTableScript_V2(ETLControlDetails etl)
{
//=================================================
Server myServer = ConnectToServer(etl.Server);

Scripter scripter = new Scripter(myServer);
Database db = myServer.Databases[etl.SourceDatabase.ToString()];

Table myTable = db.Tables[etl.SourceTable, etl.SourceSchema];
/* With ScriptingOptions you can specify different scripting
* options, for example to include IF NOT EXISTS, DROP
* statements, output location etc*/
ScriptingOptions scriptOptions = new ScriptingOptions();
scriptOptions.IncludeHeaders = true;
scriptOptions.ScriptDrops = true;
scriptOptions.IncludeIfNotExists = true;
scriptOptions.WithDependencies = true;
scriptOptions.DriAll = true;
/* You can optionally choose each DRI object separately as given below */
scriptOptions.DriAllConstraints = true;
scriptOptions.DriAllKeys = true;
scriptOptions.DriChecks = true;
scriptOptions.DriClustered = true;
scriptOptions.DriDefaults = true;
scriptOptions.DriForeignKeys = true;
scriptOptions.DriIndexes = true;
scriptOptions.DriNonClustered = true;
scriptOptions.DriPrimaryKey = true;
scriptOptions.DriUniqueKeys = true;
scriptOptions.FileName = @”C:\Download\script_custom.sql”;

/* Generating IF EXISTS and DROP command for tables */
StringCollection tableScripts = myTable.Script(scriptOptions);
foreach (string script in tableScripts)
{
sb.AppendLine();
sb.AppendLine(“–create table”);
sb.Append(script + “;”);
//MessageBox.Show(script);
}
/* Generating CREATE TABLE command */
tableScripts = myTable.Script();
foreach (string script in tableScripts)
{
sb.AppendLine();
sb.AppendLine(“–create table”);
sb.Append(script + “;”);
//MessageBox.Show(script);
}
}
#endregion

 

#region public void GenerateDropTableScript(ETLControlDetails etl)
public void GenerateDropTableScript(ETLControlDetails etl)
{
/*generate drop table script for a individual table*/
Server server = new Server(etl.Server);
Database db = server.Databases[etl.SourceDatabase.ToString()];
List<Urn> list = new List<Urn>();
DataTable dataTable = db.EnumObjects(DatabaseObjectTypes.Table);

foreach (DataRow row in dataTable.Rows)
{
list.Add(new Urn((string)row[“Urn”]));
}
Scripter scripter = new Scripter();
scripter.Server = server;
scripter.Options.IncludeHeaders = true;
scripter.Options.IncludeIfNotExists = true;
scripter.Options.ScriptDrops = true;
scripter.Options.SchemaQualify = true;
//scripter.Options.FileName = @”C:\Download\script_custom.sql”;
/*get the all the tables for a database*/
//scripter.Script(list.ToArray());

/*get a particular table script only*/
Table myTable = db.Tables[etl.SourceTable, etl.SourceSchema];
//scripter.Script(new Urn[] { myTable.Urn });

StringCollection sc = scripter.Script(new Urn[] { myTable.Urn });
foreach (string script in sc)
{
sb.AppendLine();
sb.AppendLine(“–script option”);
sb.Append(script + “;”);
}

}
#endregion

 

Source from online:

Problem
In my last article, Getting started with SQL Server Management Objects (SMO), I discussed what SMO is, how you can start working with SMO, how to connect to SQL server, how to enumerate through the different SQL objects, create a database, create a table etc, all programmatically.

In this tip I would like to take you on an SMO ride to generate SQL object scripts programmatically. Though you can do this through SQL Server Management Studio (SSMS) there might be times (more details on usage scenarios given below) when you would need to create SQL scripts automatically.

Solution
As I discussed in my last tip, SQL Server objects are represented as object hierarchies inside SMO, for example a Server object is a collection of Database objects. A Database object is a collection of a Table (though there are couple of other collection inside the Database object as well such as Stored Procedure, Views, User-defined Functions etc).  A Table  is a collection of a Column and so on.

Every object in this hierarchy has a method called a script, which returns a string collection of scripts. Apart from that, SMO provides a utility class, Scripter, which generates the script in a more efficient way. For example, the Scripter class can discover the relationships between objects and can provide scripts for dependencies as well and it can respond to Progress and Error events.

Usage Scenario

As I said, SQL Server Management Studio (SSMS) provides a wizard type interface to script out all or selected objects, but there might be some scenarios, some of them are discussed below, where you would consider the use of SMO instead of SSMS.

  • You want to create a tool which will automatically set up (or sync) an environment for dev or test which resembles the production environment.
  • You have a data warehouse database, in this database data is not that important (or even the data size is so big, taking a backup of the entire database would not be feasible) as data can again be pulled from the source systems but you want to make sure the schema objects are scripted and backed up automatically so that you can re-create the database in case of a disaster.
  • Programmatically you want to control the backup and restore process of database administration (I will cover this in more detail in another tip “Backup and Restore Programmatically with SMO”).
  • Programmatically you want to transfer a database schema and data to another instance of SQL Server. (I will cover this in more detail in another tip “Transferring schema objects and data programmatically with SMO”).

Example

Before you start writing your code using SMO, you need to take reference of several assemblies which contain different namespaces to work with SMO. For more details on what these assemblies are and how to reference them in your code, refer to my tip Getting started with SQL Server Management Objects (SMO).

C# Code Block 1 – Here I am using the Scripter utility class to generate the script for two selected databases. Two Databaseobjects are created first; one of them refers to the AdventureWorks database and another one refers to AdventureWorksDW.  The script method of the scripter object is called which takes database object URN (Unique Resource Name) as input and returns a string collection of scripts. URN is a new concept in SMO (this was not available in SQL-DMO) which provides similar notation like XPath to denote object hierarchy.

C# Code Block 1 – Generating Database Script

Scripter scripter = new Scripter(myServer);
Database myAdventureWorks = myServer.Databases[“AdventureWorks”];
Database myAdventureWorksDW = myServer.Databases[“AdventureWorksDW”];
Urn[] DatabaseURNs = new Urn[] { myAdventureWorks.Urn, myAdventureWorksDW.Urn };
StringCollection scriptCollection = scripter.Script(DatabaseURNs);
foreach (string script in scriptCollection)
Console.WriteLine(script);

C# Code Block 2 – In this code block I am generating CREATE TABLE scripts for all the tables in the AdventureWorks database. As said before, a database is a collection of tables, so I am enumerating through the table collection of the database to generate a script for each table. Along with that I am also using the ScriptOptions class to specify the different scripting options, for example in this code I am scripting IF NOT EXISTS and DROP TABLE scripts as well.

C# Code Block 2 – Generating scripts for table collection

Scripter scripter = new Scripter(myServer);

Database myAdventureWorks = myServer.Databases[“AdventureWorks”];
/* With ScriptingOptions you can specify different scripting
* options, for example to include IF NOT EXISTS, DROP
* statements, output location etc*/
scriptOptions = new ScriptingOptions();
scriptOptions.ScriptDrops = true;
scriptOptions.IncludeIfNotExists = true;
foreach (Table myTable in myAdventureWorks.Tables)
{
/* Generating IF EXISTS and DROP command for tables */
StringCollection tableScripts = myTable.Script(scriptOptions);
foreach (string script in tableScripts)
Console.WriteLine(script);

/* Generating CREATE TABLE command */
tableScripts = myTable.Script();
foreach (string script in tableScripts)
Console.WriteLine(script);
}

C# Code Block 3 – This code block further extends the use of the Scripter and ScriptOptions classes to generate a script for a table (HumanResources.EmployeeAddress) along with all the other objects on which this table depends on. The ScriptOptionsclass also provides several properties for DRI (Declarative Referential Integrity) objects. You can either select individual DRI objects or all to script out along with the main object script.

C# Code Block 3
Generating table script with all dependencies and DRI objects

Scripter scripter = new Scripter(myServer);
Database myAdventureWorks = myServer.Databases[“AdventureWorks”];
Table myTable = myAdventureWorks.Tables[“EmployeeAddress”, “HumanResources”];/* Generate Scripts of table along with for all
* objects on which this table depends on */
ScriptingOptions scriptOptionsForDependendencies = new ScriptingOptions();
scriptOptionsForDependendencies.WithDependencies = true;
/* DriAll will include all DRI objects in the generated script. */
scriptOptionsForDependendencies.DriAll = true;
/* You can optionally choose each DRI object separately as given below */
//scriptOptionsForDependendencies.DriAllConstraints = true;
//scriptOptionsForDependendencies.DriAllKeys = true;
//scriptOptionsForDependendencies.DriChecks = true;
//scriptOptionsForDependendencies.DriClustered = true;
//scriptOptionsForDependendencies.DriDefaults = true;
//scriptOptionsForDependendencies.DriForeignKeys = true;
//scriptOptionsForDependendencies.DriIndexes = true;
//scriptOptionsForDependendencies.DriNonClustered = true;
//scriptOptionsForDependendencies.DriPrimaryKey = true;
//scriptOptionsForDependendencies.DriUniqueKeys = true;/* If you can use FileName to output generated script in a file
* Note : You need to have access on the specified location*/
scriptOptionsForDependendencies.FileName = @”D:\TableScriptWithDependencies.sql”;
StringCollection tableScripts = myTable.Script(scriptOptionsForDependendencies);
foreach (string script in tableScripts)
Console.WriteLine(script);

C# Code Block 4 – This code block provides CREATE TABLE scripts for all the AdventureWorks’ tables along with all the indexes of each table. Here I am using IndexCollection class to enumerate through index collection of the table to generate CREATE INDEX scripts for all the indexes on the given table.

C# Code Block 4
Generating scripts for table collection along with all the indexes on the table

Scripter scripter = new Scripter(myServer);
Database myAdventureWorks = myServer.Databases[“AdventureWorks”];
/* With ScriptingOptions you can specify different scripting
* options, for example to include IF NOT EXISTS, DROP
* statements, output location etc*/
ScriptingOptions scriptOptions = new ScriptingOptions();
scriptOptions.ScriptDrops = true;
scriptOptions.IncludeIfNotExists = true;foreach (Table myTable in myAdventureWorks.Tables)
{
/* Generating IF EXISTS and DROP command for tables */
StringCollection tableScripts = myTable.Script(scriptOptions);
foreach (string script in tableScripts)
Console.WriteLine(script);/* Generating CREATE TABLE command */
tableScripts = myTable.Script();
foreach (string script in tableScripts)
Console.WriteLine(script);IndexCollection indexCol = myTable.Indexes;
foreach (Index myIndex in myTable.Indexes)
{
/* Generating IF EXISTS and DROP command for table indexes */
StringCollection indexScripts = myIndex.Script(scriptOptions);
foreach (string script in indexScripts)
Console.WriteLine(script);

/* Generating CREATE INDEX command for table indexes */
indexScripts = myIndex.Script();
foreach (string script in indexScripts)
Console.WriteLine(script);
}
}

C# Code Block 5 – In my last tip on SMO, I showed you how you can use SMO to create a database and a table on the server programmatically. In this code block, I am using the same code, but this time before creating the objects on the server I am generating database and table scripts. This means it is not required to create objects on the server in order to generate scripts, if you have objects in memory you can generate scripts for these objects as well even without creating on the server.

 

C# Code Block 5
Generating Script for objects in memory even before creating on the server

/* Create database called, “MyNewDatabase” */
Database myDatabase = new Database(myServer, “MyNewDatabase”);/* Output the database script on the console */
StringCollection DBScripts = myDatabase.Script();
foreach (string script in DBScripts)
Console.WriteLine(script);/* Create a table instance */
Table myEmpTable = new Table(myDatabase, “MyEmpTable”);
/* Add [EmpID] column to created table instance */
Column empID = new Column(myEmpTable, “EmpID”, DataType.Int);
empID.Identity = true;
myEmpTable.Columns.Add(empID);
/* Add another column [EmpName] to created table instance */
Column empName = new Column(myEmpTable, “EmpName”, DataType.VarChar(200));
empName.Nullable = true;
myEmpTable.Columns.Add(empName);
/* Add third column [DOJ] to created table instance with default constraint */
Column DOJ = new Column(myEmpTable, “DOJ”, DataType.DateTime);
DOJ.AddDefaultConstraint(); // you can specify constraint name here as well
DOJ.DefaultConstraint.Text = “GETDATE()”;
myEmpTable.Columns.Add(DOJ);
/* Add primary key index to the table */
Index primaryKeyIndex = new Index(myEmpTable, “PK_MyEmpTable”);
primaryKeyIndex.IndexKeyType = IndexKeyType.DriPrimaryKey;
primaryKeyIndex.IndexedColumns.Add(new IndexedColumn(primaryKeyIndex, “EmpID”));
myEmpTable.Indexes.Add(primaryKeyIndex);/* Output the table script on the console */
StringCollection TableScripts = myEmpTable.Script();
foreach (string script in TableScripts)
Console.WriteLine(script);

/* If you want to create objects on the server you need call
* create method or else objects will not be created on the server */
myDatabase.Create();
myEmpTable.Create();

Output:

The complete code listing (created on SQL Server 2008 and Visual Studio 2008, though there is not much difference if you are using it on SQL Server 2005 and Visual Studio 2005) can be found in the below text box.