Can't Expand MSDB in Integration Services Server

TCP Provider: No connection could be made because the target machine actively refused it. (MsDtsSrvr)

OR

"Named Pipes Provider: Could not open a connection to SQL Server [2].  (Microsoft SQL Native Client)"

Other day one of the team here had to work on a fairly static server which has few SSIS packages installed on it. The Team wasn't able to expand MSDB node to view packages installed on the Intergration Services.
Error they were receiving was:

Steps to check if you are not able to expand MSDB in Integration Services:
1. User has permission on MSDB database as SSIS pacakges are stored in MSDB

2. Check if SQLBrowser services is running. Run > Services.msc

3. Check if TCP-IP/Named pipe are enabled for the Sever instance from SQL Server Configuration Manager

4. Windows firewall is not blocking SQL Sever access. Add SQLServer.exe or port 1433 to exceptions.

5. Find MsDtsSrvr.ini file at drive:\Program Files\Microsoft SQL Server\90\DTS\Binn (for 2008 find in 100\DTS\bin folder) and check for Server tag if it is <ServerName>.ServerName> (this was the case with the team here :-) )

If yes then replace "." with Servername\instance name and restart Integration Sevices.

Have fun!!

Select ALL in parameter of SSRS report

Select ALL as parameter value is one of the most common functionality which most of the reports have and there are number of ways to implement it.

I will explain below the way I do it and I find easiest. The driver of the solution is CASE option under WHERE clause of SELECT DataSet Query.

        SELECT * FROM TableName
         WHERE
         (
               CASE
                      WHEN  @RepParam <> 'ALL' AND ColName= @RepParam THEN 1
                      WHEN  @RepParam= 'ALL'  THEN 1
               END
         ) = 1 ;

Lets see it through an simple Student Table example where we will Select student either on the basis of the Grade they are in or select all of them.

1. Records in Student table are

2. Create a simple report with DataSet Student as SELECT * FROM Student;

3. To add the Grade parameter and option for Select ALL

3.a. Create a DataSet for available Grades for Report parameter

3.b. Configure a Report parameter "Grade" as

3.c. Modify query for Student Dataset  to allow filtering on Grades as

4.a. Run the Report for ALL grades

4.b. Run the Report for grade - X

Cheers!!

Execute Scripts from a folder using Powershell

For last few days I am kinda got wired up with Powershell and trying to do whatever I used to do otherwise using SQL PowerShell e.g. other day we had some 40 odd scripts to be deployed on our database so I worte a simple 2 liner SQL PS script which would go and execute each script one by one.

1. To illustrate I have 3 Scripts in a folder called ScritpsFolder to create 3 Stored Procedures

2. Script inside sp_A

3. Now the Powershell code to execute scripts from the folder.

ForEach ($S In Gci -Path "C:\ScriptsFolder\" -Fliter *.sql | Sort-Object Name )
{
Invoke-SqlCmd -InputFile $S.FullName
}

a) ForEach : will iterate on each item insire $S.
b) Gci (Get-Child Item): will get all the filenames from given path and will store in variable $S.
c) Invoke-SqlCmd: will take the script and run it on the Database.


To run the script you should be in context of your SQL Server/Database and particular database if it not defined in the script.

4. Check the SPs created in the database

Execution Tree in SSIS

I have been asked many times, the way we can see Execution Query Plan for Queries in SSMS, Can we see something similar in  SSIS too. Well YES WE CAN, it wont give you factors like I/O, CPU or SubTree Cost but will give you fair idea of path and buffers SSIS engine will create to do the DATA FLOW.

At run time Data Flow Engine divides the Data Flow Task operations into Execution Trees which demonstrate how package uses buffers and threads. These execution trees specify how buffers and threads are allocated in the package.

Each tree creates a new buffer and may execute on a different thread. When a new buffer is created such as when a partially blocking or blocking transformation is added to the pipeline, additional memory is required to handle the data transformation; however, it is important to note that each new tree may also give you additional worker thread.[TechNet]

Let's take an example to show the Execution Tree in the Data Flow Task. I will create a simple Data Flow Task and have two flows in it.

1) Direct transfer of data from SrcEmployee table to DestEmployee table.
2) SrcDepartment to DestDepartment by having one Sort Component in between to sort on DepName.

To see Execution Tree log you will have enable build-in logging and to see log entries add Log Event window from View>OtherWindows.

Now when you run package, SSIS will log an entry for User:PipelineExecutionTrees which discribes the Trees/paths SSIS has created to run the Package.

Message:

Begin Path 0 [Tree 1]
   output "OLE DB Source Output" (11); component "SrcEmployee" (1)
   input "OLE DB Destination Input" (29); component "DestEmployee" (16)
End Path 0

Begin Path 1 [Tree 2]
   output "OLE DB Source Output" (124); component "SrcDepartment" (114)
   input "Sort Input" (147); component "SortDepartment" (146)
End Path 1

Begin Path 2 [Tree 3]
   output "Sort Output" (148); component "SortDepartment" (146)
   input "OLE DB Destination Input" (142); component "DestDepartment" (129)
End Path 2

Happy Extraction and Inception!!

MERGE Statement in SQL SERVER 2008

SQL SERVER 2008 has introduced MERGE statement which is very helpful in synchronizing two tables.We can perform INSERT, UPDATE, or DELETE operations on target table based on the result of the join with the source table.


Well Syntax for MERGE is very complex so I will use a very simple example where I will create two table SOURCE  and TARGET and then perform insert, update and delete on SOURCE table and then synchronize TARGET table using MERGE Statement.


1. Lets create two tables Source and Target with same schema and data and after updates in Source we will sync it with Target table.

 CREATE TABLE SOURCE
(
ID INTEGER,
Name VARCHAR(50),
);
GO
INSERT INTO SOURCE
( ID, Name)
VALUES
(1, 'Rahul'),
(2, 'Mark'),
(3, 'Jen');

 SELECT *  INTO TARGET FROM SOURCE;

2. Lets modify data in Source table ( Delete , update and Insert new record).

DELETE FROM SOURCE
    WHERE ID =2;
--Update in source
UPDATE Source
    SET Name = 'Jason'
WHERE Id = 3;

-- new record in source
INSERT INTO SOURCE
    (ID, Name)
VALUES
    (4, 'Antonia');

3. Now comes the MERGE Statement
Source table will be joined with Target table on ID and then WHEN clause is used to identify type of changes
a.Update: Where both ID match but other columns doesnt match.
MATCHED and Target.Name <> Source.Name
b. New/Inserts: When IDs are not matched by Target
c. Deletes: When IDs are not matched by Source.

MERGE INTO TARGET
USING (SELECT * from Source) AS SOURCE
ON Target.ID = Source.ID
WHEN -- upadate
    MATCHED and Target.Name <> Source.Name THEN
        UPDATE
        SET Name = SOURCE.Name
WHEN -- new record in source
    NOT MATCHED BY TARGET THEN
        INSERT  ( ID ,Name)
        VALUES ( Source.ID,Source.Name)
WHEN --records deleted in source
    NOT MATCHED BY SOURCE THEN
        DELETE
--see action
OUTPUT $action
    , Inserted.ID AS InsertedID
    , Inserted.NAME AS InsertedName
    , Deleted.ID AS DeletedID
    , Deleted.Name AS DeletedName;

I have used an OUTPUT to see operations performed by MERGE Statement.

4. Tables after MERGE

Happy Coding!!

Script Out Database objects using PowerShell

PowerShell is a very powerful tool and sometimes provide an amazing way for accomplishing which otherwise would have taken lot of effort and time.. One such scenario I had recently when I had to script out few stored procedure from my SQL Sever Database. There we go.. Below I will explain how we can script out Database Objects using PowerShell.

Lets get rolling and start PowerShell-  We start PowerShell directly from SSMS by Right clicking any node and select Start PowerShell. It will open PowerShell connected to your Server

I will script Stored Procedures from the Database and I will show various options of doing that.

First let get down to Stored Procedures

1. First thing first .. let try to display our Stored Procedure on Screen

gci *uspGetAllNodes* | %{$_.Script()}

2. Displaying it on Screen doesnt help much, lets Sript-Out this in a file

gci *uspGetAllNodes* | %{$_.Script()} | Out-File c:\Scripts\uspGetAllNodes.sql

 

3. Thats cool we can get one Stored Procedure, how about getting all the StoredProcedures

gci  | %{$_.Script()} | Out-File c:\Scripts\AllStoredProcs.txt

4. Great, but what if I have few Stored procedures to extract and I dont what to run the script again and again.

Use an Array Variable.

$SPNameList = @("uspGetAllNodes", "uspGetAllPaths")
FOREACH ( $SP in $SPNameList)
{
gci *$SP* | %{$_.Script()} | out-file c:\Scripts\$SP.sql
}

5. OutPut Directory

Thanks!!

Data from Dynamic Sheets of an Excel

This is with reference to a Question asked on the blog:

Question:

How to get data from different sheets in an Excel WorkBook  having same column names?

Solutions:

Basic idea to get the done is get name of all the sheets in an variable and then iterate over that variable and get data from respective Sheet.

To Demonstrate I have created an Excel sheet having 3 sheets having only one column "ColumnName"

1. Excel WorkBook

2. Now lets create the package with below variables

3. Variable QueryStmt will hold a dynamic value which would be used as SQL Command passed to Excel.

Check EvaluateAsExpression Property as True and set Expression as

4a. Next Step is to use Script Task to get all the sheet names in Variable SheetNamesList.

4b. Write below code in Script Task to get all the sheets names in SheetNamesList Variable.

Directive needed using System.Data.OleDb;

        public void Main()

        {

            OleDbConnection con = null;

            System.Data.DataTable dt = null;

            string ExlPath = Dts.Variables["ExcelPath"].Value.ToString();

           

            String conStr = "Provider=Microsoft.Jet.OLEDB.4.0;" +

                "Data Source=" + ExlPath + ";Extended Properties=Excel 8.0;";

            con = new OleDbConnection(conStr);

            con.Open();

            dt = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);



           

            String[] excelSheetNames = new String[dt.Rows.Count];

            int i = 0;



            foreach (DataRow row in dt.Rows)

            {

                excelSheetNames[i] = row["TABLE_NAME"].ToString();

                i++;

            }



            Dts.Variables["SheetNameList"].Value = excelSheetNames;



            // TODO: Add your code here

            Dts.TaskResult = (int)ScriptResults.Success;

}

5  Create a For Each Loop to iterate over SheetNameList

a.

b. Set the index to SheetName to get counter sheet name

6. Add a DATA FLOW TASK and use Excel Source to connect to the WorkBook and Set the properties as

7. Thats it.. Execute the package and get the data...

Thanks!!