SQL Script to retrieve metadata of a Sql Statement and/or Table of SQL Server

This script will help us to retrieve metadata/column information from a SQL Statement and/or a Table. Download this script, RETRIVE_METADATA_SCRIPT.sql.

This script will provide the below information .. 

1. TABLE_OWNER
2. ORDINAL_POSITION
3. COLUMN_NAME
4. TYPE_NAME
5. LENGTH
6. PRECISION
7. IS_NULLABLE

See the below table to understand parameters …

Parameter Name	Type	IN/OUT	Description
@SQLStm	VARCHAR(MAX)	INPUT	Put fully qualified table name, i.e. [dbo].[TEST], luckily you can use DB name with table name, i.e. [TEST_DB].[dbo].[TEST]. OR you can pass a SQL statement to check duplicate output, i.e. "select * from [dbo].[TEST]"
@isTable	BIT	INPUT	Mention @SQLStm contains a Table name or SQL stm. TRUE for table and FALSE for SQL Stm.

see also -- SQL script to check duplicate record existence of a table/SQL statement of SQL Server

                 -- SQL script to dynamically UnPivot any Pivot table of SQL Server with example

Order Variables and Categories of a SSIS Custom Task/UI

Actually there is no any built-in property or member to order variables and/or categories. By default system order variables as their position and for categories use alphabetical order. So we have to use tab (\t) technique to let system order as its way but in our order. Rules is, more tab means order position first. System will not show tabs (\t), but texts.

See the below example to order categories but variables will be ordered as their position.

According to below example system will show as following order …

·         Excel Details

Ø  FilePath

Ø  FileName

·         Excel Sheet Details

Ø  SheetNames

[Category("\tExcel Sheet Details")]   // order position low

        public string SheetNames

        {

            get

            {

                return this.loadableSheetNames;

            }

            set

            {

                this.loadableSheetNames = value.Trim();

            }

        }

      

 [Category("\t\tExcel Details")]    // order position high

        public string FilePath    // order position 1^st

        {

            get

            {

                return this.path;

            }

            set

            {

                this.path = value.Trim();

            }

        }

       

[Category("\t\tExcel Details")]    // order position high

        public string FileName   // order position 2^nd

        {

            get

            {

                return this.fileName;

            }

            set

            {

                this.fileName = value.Trim();

            }

        }

See also -- Execute SSIS package from SSIS Script/ C#

Execute SSIS package from SSIS Script/ C#

Execute SSIS package from C# / SSIS script with configuration file and/or preset variable value and get all types of messages/events from that package like information, error, warning etc.

For this execution you have to add below references and use below packages…

  Ø  References (Assembly location “C:\Program Files (x86)\Microsoft SQL Server\100\SDK\Assemblies\”)

ü  Microsoft.SqlServer.ScriptTask

ü  Microsoft.SqlServer.ManagedDTS

  Ø  Packages

ü  Microsoft.SqlServer.Dts.Runtime

ü  Microsoft.SqlServer.Dts.Tasks.ScriptTask

First of all you have to load a package by using Application object and then set configuration file and/or variables value by using Package. Then add a listener to get messages/events from loaded package.

Example declaration and calling … Download code only (CallingCode.c#)

run.Package pkg;

run.Application app;

pkg = app.LoadPackage(pkgPath + @"\TestCall.dtsx", null);

run.Variables pkgVars = pkg.Variables;

pkgResults = pkg.Execute(null, pkgVars, eventListener, null, null);

Download ExecuteSSISPkgC#.zip for example. This zip contains following three (3) files...

File Name	Purpose	Comment
CallingPkg.dtsx	This package will call the target TestCall.dtsx package	Add congif file (XML), set variables, retrieve event messages to/from TestCall.dtsx
TestCall.dtsx	This package will be executed by the package CallingPkg.dtsx	Will be executed
TestConfig.dtsConfig	Use to set variable “ValueOfConfig”	Used by “TestCall.dtsx”. set by “CallingPkg.dtsx”

see also -- SQL script to check duplicate record existence of a table/SQL statement of SQL Server

SQL script to check duplicate record existence of a table/SQL statement of SQL Server

This script is a stored procedure to check duplicate record of a table or output of a SQL statement by considering all columns. Download the script file Duplicate Record Check.sql. Run it on your database. A procedure named "[DUPLICATE_RECORD_CHECK]” will be available under the default schema, i.e [dbo].

This procedure will return TRUE as output if duplicate record found, otherwise return FALSE.

Let’s see the parameters to use it…

Parameter Name	Type	IN/OUT	Description
@SQLStm	VARCHAR(MAX)	INPUT	Put fully qualified table name, i.e. [dbo].[TEST], luckily you can use DB name with table name, i.e. [TEST_DB].[dbo].[TEST]. OR you can pass a SQL statement to check duplicate output, i.e. "select * from [dbo].[TEST]"
@isTable	BIT	INPUT	Mention @SQLStm contains a Table name or SQL stm. TRUE for table and FALSE for SQL Stm.
@duplicateFound	BIT	OUTPUT	Find the output as TRUE/FALSE. If duplicate found, returns TRUE.

An example to call this procedure …

with a Table Name	BEGIN             DECLARE @duplicateFound BIT             EXEC [dbo].[DUPLICATE_RECORD_CHECK] @SQLStm = N'[dbo].[TEST]'                         , @isTable = 1                         , @duplicateFound = @duplicateFound OUTPUT             SELECT @duplicateFound AS N'@duplicateFound' END
with a SQL Statement	BEGIN             DECLARE @duplicateFound BIT             EXEC [dbo].[DUPLICATE_RECORD_CHECK] @SQLStm = N'select 1 as test_column'                         , @isTable = 0                         , @duplicateFound = @duplicateFound OUTPUT             SELECT @duplicateFound AS N'@duplicateFound' END

see also -- SQL script to dynamically UnPivot any Pivot table of SQL Server with example

Send mail as html format with attachment and embedded picture/video using script task of SSIS package/C#.NET

Create a script task and try below code to configure html mail with a file as attachment and an embedded image/video then send it. No required SMTP SSIS connection.

Below is very much completed example, just run it with following resources. No additional references are required.

Attachment           : C:\\test.txt

Embedded Image  : C:\\gmail.jpg

public void Main()

{

                String MailCC = "MailCC@gmail.com";

                String BCCAddress = "BCCAddress@gmail.com";

                String MailFrom = "MailFrom@gmail.com";

                String MailTo = "MailTo@gmail.com";

                String SMTPServer = "smtp.gmail.com";

                String EmailMsgBody = "<html> <body> <h3>test mail <i>html</i></h3> <br> <img src=\"cid:gmail\" alt=\"gmail\" v:shapes=\"Picture_x0020_1\"> </body></html>";

                String EmailSubject = "Test Subject HTML";

                MailMessage EmailCountMsg = new MailMessage(MailFrom, MailTo.Replace(";", ","), EmailSubject, EmailMsgBody);

               

                if (MailCC != "")

                                EmailCountMsg.CC.Add(MailCC.Replace(";", ","));

                if (BCCAddress != "")

                                EmailCountMsg.Bcc.Add(BCCAddress.Replace(";", ","));

                EmailCountMsg.IsBodyHtml = true;

                EmailCountMsg.Attachments.Add(new Attachment("C:\\test.txt"));

               

                // add embedded pic

                AlternateView av = new AlternateView(new System.IO.MemoryStream(System.Text.Encoding.UTF8.GetBytes(EmailMsgBody)), System.Net.Mime.MediaTypeNames.Text.Html);

                int si = EmailMsgBody.IndexOf("cid:",StringComparison.OrdinalIgnoreCase)+4; // finding "CID:" string to know pic name

                string picName = EmailMsgBody.Substring(si, EmailMsgBody.IndexOf("\"", si, StringComparison.OrdinalIgnoreCase) - si);

               

                LinkedResource pic1 = new LinkedResource("C:\\gmail.jpg", System.Net.Mime.MediaTypeNames.Image.Jpeg);

                pic1.ContentId = picName;

               

                av.LinkedResources.Add(pic1);

                EmailCountMsg.AlternateViews.Add(av);

// end add embedded pic

                SmtpClient SMTPForCount = new SmtpClient(SMTPServer);

                SMTPForCount.Credentials = CredentialCache.DefaultNetworkCredentials;

                //SMTPForCount.Credentials = new NetworkCredential("USER", "PASSWORD", "DOMAIN");

                SMTPForCount.Send(EmailCountMsg);

                // TODO: Add your code here

                Dts.TaskResult = (int)ScriptResults.Success;

}

see also -- SQL script to dynamically UnPivot any Pivot table of SQL Server with example

Dynamically UnPivot a Pivoted table with example, SQL Server

First of all download the SQL Script, UnPivotTable.sql. Run it on your database, i.e. TEST_DB_UNPIVOT. A stored procedure, [dbo].[UnPivotTable], and a supporting function, [dbo].[SplitString],will be available on the database under DBO schema.

Now everything is you have to call [dbo].[UnPivotTable] with enough number of parameter. This procedure has eight (8) parameters. See the below description of all parameters. Luckily, every time you don’t have to use all parameters!

1	@dbName VARCHAR(100) = NULL	Put database name, where pivot table is available and unpivot table will be created. skip it if this procedure in the same database
2*	@tableNameWithSchema VARCHAR(100)	Put full name of input/Pivot table. i.e. [dbo].[TEST_PIVOT]
3	@unPivotTableNameWithSchema VARCHAR(100) = NULL	Put full name of output/unpivot table. i.e. [dbo].[TEST_PIVOT_UNPIVOT]. You can skip it to done with default script provided name.
4*	@measureName VARCHAR(100)	Put measure(s) name. Separated by comma (,). i.e. [UNITS],[SALES]
5*	@measureSearchString VARCHAR(100)	Put measure(s) search string from column name (separated by comma (,)-sync with Measure name), by which a particular measure of different period can be found. i.e. UNIT_US,SALES_US
6*	@dimColumnName VARCHAR(100)	Put column name on which you want to do unpivoe. i.e. [PERIOD]
7	@dimColumnStringStartPosFromMeasureColumnName VARCHAR(100) = NULL	Put start position of Dim Column value from measure column name of different period, this also sync with measure names. Luckily you can skip it
8	@dimColumnStringEndPosFromMeasureColumnName VARCHAR(100) = NULL	Put end position of Dim Column value from measure column name of different period, this also sync with measure names. Luckily you can skip it

 * Star refers that its mandatory

Let’s see below examples, how to call [dbo].[UnPivotTable] …

1.    Let your database name is TEST_DB_UNPIVOT

2.    Run this script UnPivotTable.sql  on this database

3.    Let you have a pivot table name is [dbo].[TEST_PIVOT] and has below data

  

CTY_CODE	UNIT_US_201501	UNIT_US_201502	SALES_US_201501	SALES_US_201502
US	10	11	12.25	30
NK	56	60	43.56	50.96

4.    Now call [dbo].[UnPivotTable] … [see the message tab to know output table, as confirmation if succeed]

a.      EXEC [dbo].[UnPivotTable]

  @tableNameWithSchema = N'[dbo].[TEST_PIVOT]'

            , @measureName = N'[UNITS],[SALES]'

            , @measureSearchString = N'UNIT_US,SALES_US'

            , @dimColumnName = N'[PERIOD]'

b.    EXEC [dbo].[UnPivotTable]

  @dbName = N'[TEST_DB_UNPIVOT]'

, @unPivotTableNameWithSchema = '[dbo].[TEST_PIVOT_UNPIVOT]'

            , @tableNameWithSchema = N'[dbo].[TEST_PIVOT]'

            , @measureName = N'[UNITS],[SALES]'

            , @measureSearchString = N'UNIT_US,SALES_US'

            , @dimColumnName = N'[PERIOD]'

c.    EXEC [dbo].[UnPivotTable]

  @dbName = N'[TEST_DB_UNPIVOT]'

, @unPivotTableNameWithSchema = '[dbo].[TEST_PIVOT_UNPIVOT]'

            , @tableNameWithSchema = N'[dbo].[TEST_PIVOT]'

            , @measureName = N'[UNITS],[SALES]'

            , @measureSearchString = N'UNIT_US,SALES_US'

            , @dimColumnName = N'[PERIOD]'

            , @dimColumnStringStartPosFromMeasureColumnName = N'9,10'

            , @dimColumnStringEndPosFromMeasureColumnName = N'15,16'

5.    Now execute “SELECT * FROM [dbo].[TEST_PIVOT_UPvt]” for (a) and “SELECT * FROM [dbo].[TEST_PIVOT_UNPIVOT]” for (b & c)

6.    For the above executions you will get the below output as unpivot table.

CTY_CODE	PERIOD	UNITS	SALES
US	201501	10	12.25
US	201502	11	30
NK	201501	56	43.56
NK	201502	60	50.96

see also -- UnPivot Pivoted Data using tSql on SQL Setver