SSIS - Return Row Count From Execute SQL Task [ Updated and Inserted Row Count To SSIS Variables ]

Scenario: 

We have source table (dbo.ClientSource) and destination table (dbo.Client) . We want to insert the new records from Source to Destination and update already existing records by using Execute SQL Task. We want to return number of rows inserted and updated from Execute SQL Task to SSIS variables so we can use them to send email with Insert/Update stats OR insert these stats in some table for Audit purpose.

Solution:

Step 1: 
Lets write our TSQL query to insert new records and update existing records and also save the row count in variables


--Declare the variables to hold the Row Count
DECLARE @UpdateRowCnt INT
DECLARE @InsertRowCnt INT


--Inserting records from Source to Destination which does not exists
insert into dbo.Client(ClientName,Country,Town)
Select clientName, Country, Town from dbo.ClientSource S 
WHERE NOT EXISTS ( Select 1 from dbo.Client CL WHERE CL.ClientName=S.ClientName)
SELECT @InsertRowCnt=@@ROWCOUNT

--Update Already existing records from Source
Update CL
set CL.ClientType=S.CLientType
from dbo.Client CL 
INNER JOIN dbo.ClientSource S 
ON Cl.ClientName=S.ClientName
SELECT @UpdateRowCnt=@@ROWCOUNT


--Return the Variable values 
Select @UpdateRowCnt ,@InsertRowCnt

Step 2:
Drag Execute SQL task to Control Flow Pane and configure as shown below

Step 3: 
Lets map the values returned by query to SSIS variables


Final Output : 
Just to display the values of SSIS variable I used Script Task. Provided the variables to Script task and wrote below script to display.

Provide the variables as shown below and then hit Edit Script

Write this code in Main
            MessageBox.Show("Records Inserted ::"+Dts.Variables["User::VarInsertCnt"].Value.ToString() +"     Records Updated::"+Dts.Variables["User::VarUpdateCnt"].Value.ToString());


Lets execute our SSIS Package and see if we are getting expected results. According to my source and destination rows the counts are correct.

SSIS - Log Package Execution Time To Audit Table

Scenario: 

We are in process of creating our SSIS Package, one of the requirement is to Audit the information about package execution to below table

CREATE TABLE dbo.PackageExecutionLog
  (
     PkgID              INT IDENTITY(1, 1),
     PackageName        VARCHAR(100),
     ExecutionDateTime  DATETIME,
     TimeTakeninMinutes INT
  ) 


Solution :

As we can see that the important information that we need to log for each execution is Package Name,Execution Date Time and Total time taken in minutes by Package for execution.

Lets created our SSIS Package 

Step 1: 
Create a variable that can hold the start date time for us that we can use at the end for Audit purpose.

Step 2:
Set the value of variable by using Execute SQL task as shown below


Step 3:
Place all the other Tasks such as data flow task, execute sql task or any other those are required by your package.
I have put one data flow task and one execute sql task with statement WAITFOR DELAY '00:2:05' to make it wait for couple of minutes before moving to next task.

Step 4:
Bring Execute SQL Task that we will use to insert information into out log table. Configure as shown below




Expressions:

"insert into dbo.PackageExecutionLog(PackageName,TimeTakeninMinutes,ExecutionDateTime)
Select '"+ @[System::PackageName]+"',datediff(minute,'"+(DT_STR,50,1252) @[User::VarStatTime]+"',getdate()),getdate()"

Final Output: 

This is how our final package should look like


Lets Select some records from our dbo.PackageExeuctionLog table and see what information it inserted

DBA - Script Table/s Definition with Data

Scenario: 

Sometime we want to move table with data from one server to another server. One way to achieve this by using  Generate Script Task in SSMS.

Solution :

We can use Generate Script Task from SSMS to perform this task. Here are the steps

Step 1: 
Right click on database and then go to Tasks and then go to Generate Scripts

Step 2: 
Wizard will start as shown below, Click next

Step 3: 
You can choose to script entire database objects (Tables, views, stored procedure etc) or Choose the tables you want to script with data

Step 4:
Once you Click Next, Next screen will get to to Advance tab where you can choose if you want to script data with definition as shown below

Step 5: 
You have option to save all scripts to file or you can script to new Query window. Press Next after selecting where you want to save script and it will generate scripts with data for you.


TSQL - Remove Database Name Or Some Text From Stored Procedures [ Cursor Example ]

Scenario: 

Sometime when developers create stored procedure they use DatabaseName.SchemaName.StoredProcedureName. It works fine as long as the database name stays same but sometime we have to restore this database with different name. Then the problem starts. The stored procedures try to point to the database but database name has changed. To make this correction we have to alter all stored procedures and remove database name from the definition.

Solution :

Here is code that can loop through all the Stored procedure definitions and remove the text which we want and recreate them.


DECLARE @TextToRemove VARCHAR(100)
SET @TextToRemove='Test1.' -- Provide the text that you want to remove
DECLARE @SpName VARCHAR(100)
DECLARE @SchemaName VARCHAR(100)
DECLARE @SpDefinition NVARCHAR(MAX)
--Declare Cursor
DECLARE CUR CURSOR FOR
  SELECT Name,
         definition,
         Schema_name(schema_id) AS SchemaName
  FROM   sys.procedures P
         INNER JOIN sys.sql_modules M
                 ON M.OBJECT_ID = P.OBJECT_ID
  WHERE  is_ms_shipped = 0
  AND definition LIKE  '%' + @TextToRemove + '%'
--OPEN CURSOR
OPEN CUR
--Fetch First Row
FETCH NEXT FROM CUR INTO @SPName,@SpDefinition,@SchemaName
--Loop
WHILE @@FETCH_STATUS = 0
  BEGIN
      DECLARE @SQL NVARCHAR(MAX)
      DECLARE @DropSPSQL NVARCHAR(MAX)
      SET @DropSPSQL=NULL

      SET @DropSPSQL='Drop Procedure [' + @SchemaName + '].['
                     + @SpName + ']'
      SET @SQL=NULL
      SET @SQL=REPLACE(@SpDefinition, @TextToRemove, '')
--Drop SP
--Print @DropSPSQL
EXEC (@DropSPSQL)

--Create SP
EXEC(@SQL)

      PRINT @SpName+ ' :: Is recreated after removing '+ @TextToRemove

      FETCH NEXT FROM CUR INTO @SPName,  @SpDefinition,@SchemaName
  END
--Close and Deallocate Cursor
CLOSE CUR
DEALLOCATE CUR

SSRS - [ Hide/UnHide Columns and Shrink the Remaining Columns By Using Parameter in SSRS Report ]

Scenario: 

We have created report as shown below but our users request that they want to have a parameter by using that they should be able to hide the Modified Date and Document Summary columns and unhide as well by changing the value of parameter.



Solution : 

Step 1: 
Lets create a parameter called Pra_HideDetails and choose the type boolean as shown below

Step 2: 
Click on Drop down arrow and choose Advance mode as shown below

Step 3:
click on the second Static ( Modified Date) under Group and write expression as shown below


Step 4: 
Click on Last Static under Column Group ( Document Summary) and write expressions as shown in Step 3.

Final Output : 

Lets preview our report by using Parameter value True or False and see if everything working as expected.

As we can see below when we provided True value to Parameter, The report is only displaying 3 columns and excluded modified date and document summary columns.

When we provided False value to Parameter, We can see all the columns

SSIS - Use Variable in Execute SQL Task ( Update Statement )

Scenario : 

We have saved some value in one of the variable in our SSIS Package, now we need to use this variable in Execute SQL task to update values in Table.

Solution : 

Step 1: 
Create a variable ClientType as shown below

Step 2: 
Create connection as shown in below and write query. The ? is used to map the value of a variable.

Lets map the variable

Run the package and it should update values in table with variable value.

SSIS - [ Read Excel File After Skipping Few Rows ]

Scenario:

We have excel file with Sheet1 as shown below. We want to skip first 3 rows as they are only description and start reading from 4th Row. The 4th Row contains Header and rest of them are data rows.


Solution :

Step 1: 
Lets create connection for Excel Source as shown below


Go to Extended properties and set HDR=YES, means the file has header(First Row as header)

With all above changes we are done with configuration for our excel 

Step 2: 
Lets place Data Flow task on Control Flow Pane and then get OLE DB Source and configure as shown below.

The query I used is 
Select * from [Sheet1$A4:D]

I did not put any number with D that means we want to read all the rows. lets say we want to read first ten rows then our query will look like this
Select * from [Sheet1$A4:D10]

Final Output: 

I used multicast as destination so I can put Data Viewer between Source and Multicast to show the records.
As we can see that our Source skipped first 3 rows and start reading from 4th row.

SSIS - [ Read Excel Cell Value In SSIS ( Execute SQL Task) ]

Scenario : 

We have an excel file with Sheet1. We need to get the CreatedBy value that exists in C3 Cell. After reading this information we can use against our validation table to find out if it matches with our requirement so we can proceed with loading Or we can read this information and insert into our Audit table for our record. In short we will be learning how to read a Cell value from Excel in SSIS.

Our input file look like this

If you are interested to use Script Task, Check this post.

Solution : 

We will be using Execute SQL Task in SSIS Package to perform this and Script task to display the value.

Step 1: 
Lets create a variable with name VarCreatedBy as shown below

Step 2:
Place Execute SQL Task on Control Flow Pane and Configure as shown below. Double Click on Execute SQL Task and then Choose Excel in ConnectionType and Click next to Connection that will lead to Source File.

Step 3:
Write Query as shown below and map the Result Set to VarCreatedBy. As you can see in query even I am reading C3 , I did not use C3:C3. we have to provide the range C3:C4 ( means read C3 cell value).


Map the variable


Step 4: 
Lets check the value of VarCreatedBy variable by using Script task

Write the highlighted code as shown below in Script task to display the value at run time.


Final Output :
Lets run the Package and see the final output. As we can see it read the Cell value (C3) and displayed




Struggling with Excel Source and Destinations, Specially Dynamic Columns etc. Check out our posts/videos on Dynamic Excel Source and Destination with real time scenarios
  1. How to Load Data from Excel Files when Number of Columns can decrease or order is changed in Excel Sheet
  2. How to Load Only Matching Column Data to SQL Server Table from Multiple Excel Files (Single Sheet per file) Dynamically in SSIS Package
  3. How to Load Excel File Names with Sheet Names ,Row Count,Last Modified Date, File Size in SQL Server Table
  4. How to Load Multiple Excel Files with Multiple Sheets to Single SQL Server Table by using SSIS Package
  5. How to Load Matching Sheets from Excel to Table and Log Not Matching Sheets Information in SQL Server Table
  6. How to create Table for each sheet in Excel Files and load data to it dynamically in SSIS Package
  7. How to Create Table per Excel File and Load all Sheets Data Dynamically in SSIS Package by using Script Task 
  8. How to create CSV file per Excel File and Load All Sheets from Excel File to it in SSIS Package
  9. How to Create CSV File for Each Excel Sheet from Excel Files in SSIS Package
  10. How to Load Excel File Name and Sheet Name with Data to SQL Server in SSIS Package
  11. How to Import data from Multiple Excel Sheets with a pattern of sheet names from Multiple Excel File in SSIS Package
  12. How to import Data from Excel Files for specific Sheet Name to SQL Server Table in SSIS Package
  13. Load Data To Tables according to Excel Sheet Names from Excel Files dynamically in SSIS Package
  14. How to Load Excel Files with Single/ Multiple Sheets to SQL Server Tables according to Excel File Name Dynamically
  15. How to Read Excel Sheet Data after Skipping Rows in SSIS Package by using Script Task 
  16. How to read data from Excel Sheet and Load to Multiple Tables by using Script Task in SSIS Package
  17. How to create Excel File Dynamically from SQL server Table/View by using Script Task in SSIS Package
  18. How to create Excel File Dynamically for Stored Procedure Results in SSIS Package by using Script Task
  19. How to Export SQL Server Tables from Database to Excel File Dynamically in SSIS Package by using Script Task
  20. How to Convert CSV/Text Files to Excel Files in SSIS Package by using Script Task
  21. How to Load All CSV Files to Excel Sheets ( Sheet Per CSV) in single Excel File in SSIS Package
  22. How to Load All CSV Files to Single Excel Sheet with File Names in an Excel File Dynamically in SSIS Package
  23. How to Create Sample Excel file with Sheet from each table with Top 1000 Rows per sheet in SSIS Package
  24. How to Export Data to Multiple Excel Sheets from Single SQL Server Table in SSIS Package
  25. How to split large table data into multiple Excel Sheets on Single Excel File by using SSIS Package
  26. How to Export All tables of a database to Excel Files with Date-time in SSIS Package
  27. How to read Cell Value from Excel by using Script Task in SSIS Package

SSRS - Export SSRS Report To Excel and Name Sheets to Group Values Automatically

Scenario : 


We have created a SSRS report on which we have Country Name on Each of Page. We want to export this report to Excel but want to create separate Sheet for Each Country Name.

Our report look like this


Solution : 

Lets start to make changes in our report so when we export to Excel , it dump the data to separate sheets and sheets should have the Country Name.

Step 1:  
Under the Row Group, Right Click on group and then go to Group Properties


Go to Page Breaks and make sure "Between each instance of a group" is checked.


Step 2:
Right Click on group as shown below and press F4(properties) , then go to PageName property and click on expressions.

Write expressions as shown below


Step 3: 
Lets export our report to Excel and see if everything worked as expected


Final Output :

As we can see that the report is exported to required format we wanted to do.




How To Insert Value In Identity Column Manually in SQL Server Table - SQL Server / T-SQL Tutorial Part 41

Scenario: 

We have a dbo.Customer table that has ID as identity column. We need to insert one record in this table with -1 for ID.

Solution :

We will be using Identity_insert ON and Off to do this as shown below.It will disable identity property and then we will insert record and then enable it back.

CREATE TABLE dbo.Customer(
  ID INT IDENTITY(1,1)
, CustomerCode VARCHAR(50)
, FirstName VARCHAR(50)
, LastName VARCHAR(50))

SET IDENTITY_INSERT dbo.Customer ON
 GO
INSERT INTO dbo.Customer ( ID,CustomerCode,FirstName,LastName)
VALUES (-1,'UNKNOWN','UNKNOWN','UNKNOWN')
SET IDENTITY_INSERT Dbo.Customer OFF

SELECT * FROM dbo.Customer


How to insert value in Identity Column manually in SQL Server Table


Video Demo :  How to manually insert the value in identity column in SQL Server Table


SSRS - Display Header Row on Each Page

Scenario :

We have created a simple detail report. When we preview report it shows header on first page but after that it does not show Header Row. We want to see the Header Row on each page.

Solution :


Here are the steps we need to perform to shown Header Row on each page.

Step 1 :  Click on Drop down arrow next to Column Groups as shown below


Step 2: 
Click on Static under Row Groups and Press F4 to go to properties as shown below

Step 3: 
Set KeepTogether=True
and KeepWithGroup=After

With above changes you should be able to see the Header Row on each of the page.