This blog post is just to give a step by step instruction to work with Unpivot Transformation in SSIS.
Unpivot Transformation is used to convert unnormalized dataset into normalized dataset. Database Normalization.
For instance, we have got a data as show in the below image and we want to normalize the data to make the user table compact.
In this dataset, Column 1 contains Customer names, column 2 to 6 contains product names and each data cell represents the quantity purchased by the customer. But to store this data in the RDBMS, we need to convert this dataset into normalized structure. So, In normalizes structure this data will looks like this.
Now, we are going to implement this scenario in using SSIS.
CREATE TABLE [dbo].[tblPurchase](
[Customer] [nchar](10) NULL,
[Ham] [nchar](10) NULL,
[Soda] [nchar](10) NULL,
[Milk] [nchar](10) NULL,
[Beer] [nchar](10) NULL,
[Chips] [nchar](10) NULL
) ON [PRIMARY]
INSERT INTO [dbo].[tblPurchase]
(‘Kate’,’2′, ‘6’, ‘1’, ’12’,NULL),
Step 1: Create a new package
Step 2: Add a Data Flow Task in to package and Go to Data Flow environment
Step 3: Add a OLE DB source task to configure to read the table.
Step 4: Add Unpivot transformation. This task is used to convert unnormalized dataset to normalized dataset. Unpivot Transformation Editor.
Notice that the customer column is removed from the pivot input column and setup the output column as quantity and the product column as pivot key. So, we will get the output of Product and its quantity for each customer in multiple rows.
Note: All null value quantity rows will be removed by this transformation.
Step 5: Use the OLE destination column to store the unpivoted output in the normalized table. In this example, I am using the union all transformation as the destination and added data viewer to browse the output in the pipeline.
Step 6: Execute the package
This output show that the unpivoted dataset is in normalized structure.
Now, You can easily normalize any unnomalized dataset using Unpivot Transformation task in SSIS. I hope you have enjoyed reading this article. So, please share your thoughts and question in the comment section.
In this blog post, I would like to show a simple example on how to handle or use the stored procedure that has input , output parameter and return status value in SSIS.
Create Stored Procedure in SQL Server
CREATE PROCEDURE usp_TestProcedure
@MyOutput varchar(100) OUTPUT
SET NOCOUNT ON;
SET @MyOutput = ‘SQL Server Rider';
This user procedure has two parameters input and output. I also returns a return status. All these parameters and return values are imaginary and has no purpose.
Execute usp_TestProcedure in Query window
DECLARE @Output AS VARCHAR(100)
EXEC [dbo].[usp_TestProcedure] ‘Blogger’, @Output OUTPUT
@Output is the character variable and also act as an output parameter variable to bring back the value of the @MyOutput parameter in the stored procedure.
To execute the same stored procedure in the SSIS, we are going to use Execute SQL Task. So, We need to create three variables in the package to execute this stored procedure.
Step 0: Create SSIS solution in SS
Step 1: Add 3 package variables with correct data type.
Step 2: Add and Configure the SQL Execute Tast
Configure Database connection, Stored procedure and its parameters.
SQL Statement: exec ? = usp_TestProcedure ?,? OUTPUT
Map variables to Input/output parameters and return value .
Step 3: Executing the package and watch the variable’s value during run-time.
Package execution succeeded by calling the stored procedure.
We need to Map the parameters in sequential order. It means, in this example parameter 0 is return value and 1 is the input and 2 is the output parameter.
Download latest and up-to-date list of SQL Server versions and its support dates. Click here to download (CSV file)
It is always important to know what versions of SQL Server running in our environment to keep the business healthy and to avoid unintended issues such as security risks, system failure, performance issues and etc. Keeping the server up-to-date will give the best solution to business for complex customer problems.
This is the list of the end of mainstream support for SQL Server 2008 and SQL Server 2008 R2
So, go check the system and update or upgrade the old versions.
Thanks for reading.
Cumulative Update (CU) #13 has been released for SQL Server 2008 R2 SP2. It is the final update for this version because the mainstream support ends on July 8, 2014.
Following are the fix for the BI services in CU #13
- FIX: Memory leak occurs during Process Update on a dimension in SSAS 2008 R2
- FIX: Incorrect result and exception occur when you use MDX query to do session cube grouping in SSAS 2008 R2
Full list of CU downloads for SQL Server 2008 R2 SP2
CU#13 KB Article
CU#12 KB Article
CU#11 KB Article
CU#10 KB Article
CU#9 KB Article
CU#8 KB Article
CU#7 KB Article
CU#6 KB Article
CU#5 KB Article
CU#4 KB Article
CU#3 KB Article
CU#2 KB Article
CU#1 KB Article
Microsoft released Cumulative Update 2 for SQL Server 2014 RTM. This blog post contains hot-fix details for BI (SSIS, DQS, SSAS, SSRS), In-Memeory OLTP, SQL Server Performance topics.
- FIX: The execution of SSIS package takes a long time when you cannot enable garbage collector server mode in SSIS 2012 or SSIS 2014
- FIX: Errors when you use export/import data wizard to move data in SQL Server
- FIX: The execution of an SSIS package that is stored in MSDB is slower than it is stored on the file system
- FIX: SSIS 2012 or SSIS 2014 XML validator only outputs “TRUE” or “FALSE” when you perform XML validation
- FIX: Error when you add a custom connection manager in SSIS 2014 Designer
Data Quality Services (DQS)
- FIX: Error when you execute DQS stored procedure [internal_core].[RestoreDQDatabases] in SQL Server
- FIX: Error when you use DQS Cleansing component with large batch in an SSIS 2012 or SSIS 2014 package
- FIX: Totals are wrong after you filter on a pivot table item and remove the filter in SSAS 2012 or SSAS 2014
- FIX: SSAS instance crashes when you perform writeback action by using the role with cell security defined
- FIX: The execution of an MDX query in SSAS 2012 or SSAS 2014 is much slower than it is in SSAS 2008 R2
- FIX: Access violation occurs when running a Process Recalc on a tabular table in SSAS 2012 or SSAS 2014
- FIX: Data models do not load after you run a PowerPivot scheduled data refresh job when you work with SQL Server 2014
- FIX: You cannot create a power view report against a UDM cube in SSAS 2014
- FIX: Poor performance when you use DAX functions on a String or Boolean column in SSAS 2014
- Can now disable checking whether dimension and partition data originate from the same data source when you run a ROLAP query in SQL Server 2014
- FIX: “SQL Server performance counters are disabled” when you move the SQL Server resource in SQL Server 2014
- FIX: Error in checking master database after you bind a memory-optimized database to a resource pool in SQL Server 2014
- FIX: Sys.indexes returns incorrect value for indexes in SQL Server 2014
- FIX: Intense query compilation workload does not scale with growing number of cores on NUMA hardware and results in CPU saturation in SQL Server
- FIX: Assertion failure when you execute a query specifying TOP N and ORDER BY in SQL Server
- FIX: Incorrect results when you run queries that contain UNION operator by using parallel query plan in SQL Server
- FIX: An access violation occurs when you execute update query on a table that has a DML trigger in SQL Server
- FIX: Poor cardinality estimation when the ascending key column is branded as stationary in SQL Server
- FIX: Metadata corruption occurs when you update index statistics with INCREMENTAL=ON in SQL Server 2014
DBF is a database file format and it is one of the oldest and successful data storage in the past and present. This blog post is just to introduce a utility software called MyDBFViewer. This is used to view dbf files and convert it to CSV format with simple button clicks. Please download and use this software and share your feedback in the comment section.
Sample screen of the software:
Step 1: Select a DBF file using Select file button or type the file path in the text box.
Step 2: Click Browse Data button to load the data on the screen.
Step 3: Click Export to CSV button for exporting data to CSV file. To perform this step you have to complete Step 2.
DBF file used in this demo is available here.
Note: Please download and rename the file MyDBFViewer.pdf to MyDBFViewer.exe
This is a hotfix from Microsoft for SQL Server 2012 to prevent the data loss while building cluster index.
Microsoft provided two options to solve this problem
1. Work around – run the online index build in serial mode (DOP = 1).