How to give read permission to non administrative accounts to Event Viewer in Windows 2008R2/2012

This is quick video in which you will learn how to provide permission to non administrative accounts to see Windows Event Viewer. This can be helpful when the developers need to read log for SSIS events or application developers need to read the information related to their application which is logging information to Windows Event Log.

Provide Non Administrators permission to read Event Logs

SQL Server | How to recover sa pasword in sql server 2012 or 2014

In this video post, you will be learning how to recover password for sa account in SQL Server 2012 and SQL Server 2014.

Recover sa password in SQL Server

Integration Services (SSIS) Event Handlers

In this video post, you will learn What are Event Handlers in SSIS Package?

SSIS Event Handlers

Merge Transformation in SSIS

In this video post, we will learn how to use Merge Transformation in SSIS Package. It is short demo that explains what are the requirements to use Merge Transformation.

SSIS Merge Transformation Example

Difference between Union All and Merge Transformation in SSIS

In this video post, you will learn what is the difference between Merge Transformation and Union All in SSIS.
The video it short demo so you understand the difference between Union all and Merge Transformation in SSIS in-detail.

Diff between Union All and Merge Transformation in SSIS

Split Single Column Data into Multiple Columns in SSIS Package

In this video, we will learn how to split a single column data that is comma delimited into multiple columns. We will use the Derived Column Transformation to split the single column into multiple columns in SSIS Package.

In this video we will also learn how to use functions in Derived Column Transformation. In our case, we will use FindString function in derived column Transformation to split single delimited column into multiple columns.

Split Column into multiple Columns in SSIS by using Derived Column Transformation

How to display variable value from script task in SSIS

In this video you will learn how to display variable values in script task in SSIS Package. This can be helpful when you are developing your SSIS Package and need to see the value of variable.

This is one of technique SSIS Developer use to see the value of Variable in SSIS Package. The video will show you how exactly you can use Script Task to watch the value of Variable. The video will show you how to save the record count from Data Flow Task to variable and then display.

Script Task Variable Value Display

Delete/Update Records in Data Flow Task in SSIS Package

In this video you will learn how to delete or update records in Data Flow Task in SSIS Package by using OLE DB Command Transformation.

SSIS OLE DB Command Transformation

Expressions in Conditional Split Transformation in SSIS Package

In this video we will learn how to use the Conditional Split transformation in SSIS to split the data to multiple outputs depending upon the data values. You will learn how to write expressions in Conditional Split transformation.

SSIS Conditional Split Transformation Example

SSIS Multicast Transformation Demo

In this video post you will learn What is Multicast Transformation in Data Flow Task in SSIS Package.
The Multicast Transformation can be used to load the same input data to multiple destinations.

Multicast Transformation in SSIS Package

Resolved: Specify the valid certificate to authenticate Microsoft Dynamics Lifecycle Services System Diagnostic Service

This issue occurred as result of security update from Microsoft. Below is the KB number for the particular update.

KB2862973

As a result, when you try to use cert as explained in below link, you will get error:

Specify the valid certificate to authenticate Microsoft Dynamics Lifecycle Services System Diagnostic Service

or

Specify the valid certificate to authenticate

This would be for any application you are trying to connect to using the Cert.

Resolution:

1 - Go to Control Panel
2- Uninstall a program or Program and Features in case of win 2007
3- View installed update
4- Find the Correct KB as mentioned above under security updates
5- Uninstall
6- Create new certificate and put it in trusted Root ( start->mmc->file->Add/Remove Snapin) as shown in the Fig 1.0

Fig 1.0

OR

Please go to below link and download the fixed Package.

http://support.microsoft.com/kb/2862973

I hope it helps.

If Else Statement in Derived Column Transformation in SSIS Package

In this video post , you will be able to see how to use if else statement in derived column transformation in Data Flow task in SSIS Package.

If Else condition in Derived Column Transformation in SSIS Package

Audit Transformation in SSIS Package

In this video you will learn how to get Audit information such as Package Start time, User name etc. by using Audit Transformation in Data Flow Task in SSIS Package.

Audit Transformation in SSIS Demo

SSIS Precedence Constraints with Expressions

In this video you will learn how to use Expressions with Precedence Constraint to control the execution flow of Tasks in Control Flow pane in SSIS Package.

Expressions to Precedence Constraints in SSIS Package

Integration Services (SSIS) System Variables

In this video post you will learn about System variables in SSIS. The video explain which one are mostly used in real time scenarios.

System Variables in SSIS

How to use Stored Procedure in Oledb Source in SSIS

In this video post , You will learn How to use Stored Procedure in OLE DB Source in Data Flow task and pass parameter to Stored Procedure.

We encounter so many scenarios where we have to use Stored Procedure in OLE DB Source.

OLE DB Source with Stored Procedure in SSIS

Row Sampling Transformation in SQL Server Integration Services (SSIS) Package

In this video post you will learn what is Row Sampling Transformation and why do we need to use Row Sampling Transformation in SSIS Package?

You will also learn what other options we have if we don't want to use this blocking transformation in SSIS Package to get sample data.

Row Sampling Transformation in SSIS Demo

Percentage Sampling Transformation in SSIS Package

In this video post you will learn What is Percentage Sampling Transformation in SSIS Package. You will learn step by step how to configure Percentage Sampling Transformation to get sample data, also learn advantages/disadvantages of using Percentage Sampling Transformation.

SSIS - Percentage Sampling Transformation

Data Viewers In SSIS

In this video post you will learn how to use Data Viewer in Data Flow Task to see data while debugging/troubleshooting your SSIS Package.

The video also explains what is the difference between Data Viewer in old version such as SSIS 2005, SSIS 2008/R2 and new versions such as SSIS 2012 and SSIS 2014.

Data Viewer in Data Flow Task in SSIS Package

SSIS Precedence Constraints

In this video post you will learn What are SSIS Precedence Constraints in SSIS Package? and How to use different values of Precedence Constraints according to your requirement. You will also learn how to use Precedence Constraints in Control Flow Pane for executes, Tasks and Containers to control the flow of these items.

Precedence Constraints in SSIS Package

SSIS Package Annotation

In this video post you will learn What is Annotation in SSIS Package and how to use it. What are limitations in old versions such as SSIS 2005, SSIS 2008. You will also learn that when it comes to Auto format SSIS Package Tasks/Transformations,What happen with Annotation.

SSIS Package Annotation

SSIS - How to use Bulk Insert Task To Load Text File to SQL server Table in SSIS Package

In this video post, we will learn how to use Bulk Insert Task in SSIS Package to load text file to SQL Server table.

Bulk Insert Task in SSIS Package

SSIS - How to Perform Cross Join in SSIS Package by using Merge Join Transformation

In this video post, we will learn the technique how to perform Cross Join in SSIS Package. The built in Transformations does not provide Cross Join operation by default. we will learn a trick how to use the same transformations to perform Cross Join!

Cross Join in SSIS Package by using Merge Join Transformation

SSIS - How to create Folders as Month Name in SSIS Package

It is common practice to archive files to folder/s after loading into our destinations in ETL process. The files are archived so we can take a look anytime if we need to.
In this video post, you will learn how to create folder as Month name dynamically each month.

How to create folder with Month Name in SSIS Package

SSIS - How to Enable SQL Server Logging Type for SSIS Package

In this video, we will learn how to enable SQL Server Logging for an SSIS Package. The video will walk you through each step for enabling SQL Server Logging Type in SQL Server Integration Services (SSIS) Package.

It also explains the advantages/disadvantages of using SQL Server Logging in SSIS Package.

SQL Server Type Logging In SSIS Package

SSIS - How to Create Trace file with DateTime for SQL Server Profiler Type Logging In SSIS Package

In this video we will learn
How to enable SQL Server Profiler type logging in SQL Server Integration Services Package. The video show how to add datetime to trace file created for logging.

How to enable SQL Server Profiler Type Logging in SSIS

SSIS - How to enable Windows Event Log Type Logging in SSIS Package

In this video we will learn how to Enable Windows Event Log type logging in SQL Server Integration Services ( SSIS Package).

You will be able to see all the step how to enable logging and then go to Windows Event Viewer to read the log information for different events from SSIS Package.

Windows Event Log Type Logging in SSIS Package

SSIS - How to Create XML Log File with Datetime for SSIS Package Execution

In this video, you will be able to learn how to create xml log file with datetime for SSIS Package. Package will be able to create the log file with datetime on each execution.

XML log file with Datetime in SSIS Package

MS Dynamics AX 2012 R2 Video Tutorial

Microsoft Dynamics AX 2012 R2 Video Tutorial

Microsoft Dynamics AX 2012 R2 - Video Tutorial Series

In this tutorial, you will learn Microsoft Dynamics AX 2012 R2 step by step from installation and configuration to implementation. It also provides insight to all the components related to small to enterprise level businesses who have implemented Microsoft Dynamics AX 2012 R2 as their primary ERP. You will learn real-time industry experience following these video tutorials. It provides greater detail of all the components as well as best practices of implementation.

Installation

User Security Administration

Port Configuration

Client Configuration

Services Configuration

Workflow

Batch Server Configuration

Models in Dynamics AX

Report Configuration

Windows Server 2012 R2 Installation Videos

Learn Windows Server 2012 R2

This playlist contains videos to help you learn all about Windows Server 2012 R2, from basic to advanced levels. You'll find tutorials on topics such as:

How to install Active Directory
How to install Domain Services
How to set up the MS Hyper-V Platform
Creating different virtual machines using Hyper-V
Cloning Hyper-V Virtual Machines
Installation of important Windows Server 2012 R2 features, such as .Net Framework 3.5 and 4.0
Windows Server 2012 R2 failover cluster features
Other application feature installations (from basic to advanced)

After watching this playlist, you should be able to set up an entire infrastructure at your home or apply the same concepts at your workplace. All the videos contain real-time industry scenarios.

Windows Server 2012 R2 Tutorials

Microsoft Dynamics AX Videos

How to create flat files per partition from a SQL Server Partitioned Table in SSIS Package

Scenario:

I created a youtube video in which I was showing how to create multiple files from a single SQL Server table depending upon RegionCD. In the comment I got this request how to create multiple flat files from a partitioned table. There should be one file created per partition. So here we go.

Solution:

First of all we have to have a partitioned table to perform this requirement. I have created a sample database with partitioned table by using below code. You can use the same code for your test or learn and then you can change with your actual table name.

USE master

go

--Create Database for our post
CREATE DATABASE sales

go

--Add Multiple file groups
ALTER DATABASE sales ADD filegroup sales_data_1

go

ALTER DATABASE sales ADD filegroup sales_data_2

go

ALTER DATABASE sales ADD filegroup sales_data_3

go

ALTER DATABASE sales ADD FILE ( NAME = sales1, filename =
'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQL2008\MSSQL\DATA\Sales_1.ndf'
, size = 50mb, maxsize = 50mb, filegrowth = 50mb) TO filegroup sales_data_1

go

ALTER DATABASE sales ADD FILE ( NAME = sales2, filename =
'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQL2008\MSSQL\DATA\Sales_2.ndf'
, size = 50mb, maxsize = 50mb, filegrowth = 50mb) TO filegroup sales_data_2

go

ALTER DATABASE sales ADD FILE ( NAME = sales3, filename =
'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQL2008\MSSQL\DATA\Sales_3.ndf'
, size = 50mb, maxsize = 50mb, filegrowth = 50mb) TO filegroup sales_data_3

go

--Create Partitioned Function, I have created per year
USE sales

go

CREATE partition FUNCTION parfunction_sale (datetime) AS range RIGHT FOR VALUES
('2013-01-01', '2014-01-01')

--Create Partition Scheme
CREATE partition scheme schema_sale AS partition parfunction_sale TO (
sales_data_1, sales_data_2, sales_data_3)

--Create our Table
CREATE TABLE dbo.customersale
  (
     customerid      INT IDENTITY(1, 1)NOT NULL,
     salepersonname  VARCHAR(100),
     saleproductname VARCHAR(100),
     saledate        DATETIME NOT NULL
  )
ON schema_sale(saledate)

--Insert some Sample Data
INSERT INTO dbo.customersale
            (salepersonname,
             saleproductname,
             saledate)
VALUES      ('Aamir',
             'TV',
             '2013-02-01'),
            ('Aamir',
             'TV1',
             '2013-02-02'),
            ('Aamir',
             'CellPhone',
             '2013-07-20'),
            ('Raza',
             'Computer',
             '2014-02-01'),
            ('Raza',
             'Ipad',
             '2014-11-11'),
            ('John',
             'TV',
             '2013-05-05')

SSIS Package Part:

Now we have the sample data in our partitioned table and we are all set to start developing our SSIS Package that can create multiple flat files from our partitioned table.

Step 1:

Open BIDS or SSDT and then create SSIS Project. Inside SSIS Project create an SSIS Package.

Create variables as shown below

Fig 1: Create variables in SSIS Package

FolderPath:

This variable will have folder path where do you want to create flat files.

PartitionList_Obj:

This variable will be used to save the partition numbers from partition table which has records.

PartitionNumber:

This variable will be holding the value of a partition number in foreach loop container and we will pass this to OLE DB query parameter.

Step 2:

Bring the Execute SQL Task to Control Flow Pane and configure as shown below to read all the partition numbers from Partitioned Table which has records.

We will be using below query to get the partitions from table

SELECT partition_number
FROM   sys.partitions
WHERE  Object_name(object_id) = 'CustomerSale'
       AND rows > 0

Fig 2: Configure Execute SQL Task to read partition numbers into Object Type variable

Map the result set to Object Type variable

Fig 3: Map the results to PartitionList_Obj variable

Step 3:

Bring the Foreach Loop Container.We are going to read the records from Object type variable by using ADO Enumerator and then save the value in PartitionNumber variable on each iteration and then use PartitionNumber variable in OLE DB source parameter.

Fig 4: Read from Object Type variable in Foreach Loop Container

Map the Partition Number to PartitionNumber variable in Foreach Loop Container.

Fig 5: Map the variable in Foreach Loop

Step 4:

Bring the Data Flow Task inside the Foreach Loop. Open the Data Flow Task and then bring OLE DB Source and configure as shown below.

We will be using this query inside OLE DB Source and map the PartitionNumber variable to it.

SELECT *
FROM dbo.customersale
WHERE $partition.Parfunction_sale(saledate) = ?

Fig 6: Read data from Partitioned table per partition at a time by using Parameter

Bring the Flat File Destination and then create Connection manager for it and map the source columns to it. You can name anything you like as we are going to overwrite the connection string in next step.

Fig 7: Flat file destination connection manager with column mapping

Step 5:

As we want to generate file for each partition, we need to build expressions to create unique name for each file created per partition. I am going to have file name = "Customer_PartitionNumber.txt".

Right click on Flat File Connection Manager and go to properties. Then go to expressions and choose ConnectionString under property and provide expressions as shown below.

Expression used in fig.

@[User::FolderPath]+"Customer_"+ (DT_STR,1,1252)@[User::PartitionsNumber]+".txt"

Fig 8: Write Expression on Flat File Connection Manager to generate new file name with partition

Run the SSIS Package and see if files are create per partition in our given folder path.

Fig 9: Complete SSIS Package to read file per partition from Partitioned Table

As we can see that two files are created and they have partition number added to them. Also the data belong to the partition.

Fig 10: Flat files created according to the partition number by SSIS Package

Summary or Items in this post we learn:

How to create Database with multiple Data files in SQL Server
How to create Partitioned Table in SQL Server
How to create Partition Function and Partition Scheme in SQL Server
How to use Execute SQL Task to load data into Object Type variable
How to read data from Object Type variable by using Foreach Loop Container
How to map variables in Foreach loop Container in SSIS Package
How to use Data Flow Task to read data from OLE DB Source with Parameter and create flat file.
How to write expressions on Flat file connection manager to generate files with dynamic name
How to create and use variables in SSIS Package

How to create tables for multiple pipe delimited files and load them dynamically to those tables in SSIS Package

Scenario:

Let's say that we get 100 pipe delimited files and we want to load all of them to different sql server tables. Each of the file has different meta data or column information.

We want to build an SSIS Package, that can read the column name from the file and then create table and load the data to it and it should repeat for all 100 files.

Solution:

To perform this we need to follow below steps.

1. Create an SSIS Package in SQL Server Integration Services Project

2. Create ADO.Net Connection Manager in Connection Managers pane. I have named it ADOAudit. You can name anything but you have to make change in below code accordingly.You will be pointing to the database in which you want to create the tables and load the files.

3. Bring the Script Task to Control Flow pane. Open it and go to Edit Script and paste the below script in it.

4.Change the path of folder in which your files exists for SourceDirectory variable in below code.Save the script and exit the editor. You are done and your SSIS Package is ready to read text files and create table for each of them and load them dynamically in SQL Server Integration Services.

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.IO;
using System.Data.SqlClient;


namespace ST_da3127cebb85407989456cc583a3f9e4.csproj
{
    [System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {

        #region VSTA generated code
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion

 

        public void Main()
        {


            SqlConnection myADONETConnection = new SqlConnection();
            myADONETConnection = (SqlConnection)(Dts.Connections["ADOAUDIT"].AcquireConnection(Dts.Transaction) as SqlConnection);
            MessageBox.Show(myADONETConnection.ConnectionString, "ADOAUDIT");


            string line1 = "";
            //Reading file names one by one
            string SourceDirectory = @"C:\SourceFolder\";
            // TODO: Add your code here
            string[] fileEntries = Directory.GetFiles(SourceDirectory);
            foreach (string fileName in fileEntries)
            {
                // do something with fileName
                MessageBox.Show(fileName);
                string columname = "";


                //Reading first line of each file and assign to variable
                System.IO.StreamReader file2 =
                new System.IO.StreamReader(fileName);


                string filenameonly = (((fileName.Replace(SourceDirectory, "")).Replace(".txt", "")).Replace("\\", ""));
                line1 = (" IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo]." + filenameonly + "') AND type in (N'U'))DROP TABLE [dbo]." + filenameonly + " Create Table dbo." + filenameonly + "([" + file2.ReadLine().Replace("|", "] VARCHAR(100),[") + "] VARCHAR(100))").Replace(".txt", "");

                file2.Close();

                MessageBox.Show(line1.ToString());
                SqlCommand myCommand = new SqlCommand(line1, myADONETConnection);
                myCommand.ExecuteNonQuery();

                MessageBox.Show("TABLE IS CREATED");

                //Writing Data of File Into Table
                int counter = 0;
                string line;

                System.IO.StreamReader SourceFile =
                new System.IO.StreamReader(fileName);
                while ((line = SourceFile.ReadLine()) != null)
                {

                    if (counter == 0)
                    {
                        columname = line.ToString();
                        columname = "[" + columname.Replace("|", "],[") + "]";
                        MessageBox.Show(columname);
                        MessageBox.Show("INside IF");
                    }

                    else
                    {
                        MessageBox.Show("Inside ELSE");
                        string query = "Insert into dbo." + filenameonly + "(" + columname + ") VALUES('" + line.Replace("|", "','") + "')";
                        MessageBox.Show(query.ToString());
                        SqlCommand myCommand1 = new SqlCommand(query, myADONETConnection);
                       myCommand1.ExecuteNonQuery();
                    }

                  counter++;

                }

                SourceFile.Close();
            }
            Dts.TaskResult = (int)ScriptResults.Success;
        }
    }
}

What are the best practices for SSIS Configuration

By watching video you will be able to find the answer "What are the best practices for SSIS Configuration". The video discuss following below points

Making a decision about type of SSIS configuration you should use?
Naming your XML Configuration file or SSIS Configuration table properly
Provide meaningful name to Environment Variable for Indirect SSIS Configuration.
Save only required property values in SSIS Configuration file or SSIS Configuration Table. Avoid including any configuration which are not required and you never use them.
Name your Configurationfilter meaningful and unique
Reuse the configuration record if already available in SSIS Configuration table.
Secure your SSIS Configuration files and SSIS Configuration table

Looking forward for comments for this topics so can include in best practices list.

Label

Installation

User Security Administration

Port Configuration

Client Configuration

Services Configuration

Workflow

Batch Server Configuration

Models in Dynamics AX

Report Configuration

Learn Windows Server 2012 R2

Windows Server 2012 R2 Tutorials

Scenario:

Solution:

SSIS Package Part:

Step 1:

Step 2:

Step 3:

Step 4:

Step 5:

Scenario:

Solution: