Friends, This issue is very common when you are pulling data from Sharepoint list to load parameter values. In this post let us discuss the solution for the same. Step 1 : Create a report and Go to the Report Tab at the top. Step 2 : Go to the Report properties and then code and write the following code in it. Public Function RemoveDuplicates(parameter As string) As String Dim value as String Dim items As Object() = Split(parameter,”~”) System.Array.Sort(items) Dim k As Integer = 0 For i As Integer = 0 To items.Length –...
Friends, This post is very useful to those who wants to learn SSRS with MDX. I have covered almost all concepts and please do drop a message if you are happie with the tutorials. Video 1 - Video 2 - Video 3 - Video 4 - Video 5 - Video 6 - Video 7 - Video 8 - Video 9 - Video 10 - Video 11 - My Best Wishes !! Regards, Roopesh Babu V ...
Friends, I am bringing together all the SSAS Videos. In this post we are gonna discuss about all the SSAS topics in details. Hope you will enjoy the journey through SSAS in detail. My best Wishes !! Video 1 Video 2 Video 3 Video 4 Video 5 Video 6 Video 7 Video 8 Video 9 Video 10 Video 11 Video 12 Video 13 Video 14 Video 15 Regards, Roopesh Babu V ...
Friends, I dunno how many of you guys are aware of the property IsBig in SSIS Aggregate Transformation. If you are aware of it then just skip this post else worth reading this. As you all aware that using aggregate we can calculate SUM,AVG,COUNT and COUNT DISTINCT of a column or columns. In some cases the return value will be soo big like more than 4 billion and to handle those kind of outputs we need this IsBig Property. This can be set on output columns to invoke special handling of big or high-precision numbers. If a column value may...
Friends, You might have seen the properties “Keys Scale” and “Number of Keys” in SSIS Aggregate transformation. What is the difference and in which scenario we use these properties ? Like to know, then continue .. The above two properties are given to improve the performance of the transformation. For the columns with GROUP BY operation in the transformation, we set these TWO properties. Both deals about the number of groups that are expected to result from a Group by operation. For example, if I used Color column...
Merge Join Transformation is one of the useful tool in SSIS to join two sources. The Merge Join transformation provides an output that is generated by joining two sorted datasets using a FULL, LEFT, or INNER join. Let us see how it works. You can configure the Merge Join transformation in the following ways: • Specify the join as FULL, LEFT, or INNER join. • Specify the columns the join uses. • Specify whether the transformation handles null values as equal to other nulls. NOTE: If null values are not treated as equal values, the...
The Import Column transformation reads data from files and adds the data to columns in a data flow. Using this transformation, a package can add text and images stored in separate files to a data flow. For example, a data flow that loads data into a table that stores product information can include the Import Column transformation to import customer reviews of each product from files and add the reviews to the data flow. A column in the transformation input contains the names of files that hold the data. Each row in the dataset can specify...
RowCount Transformation : The RowCount transformation provides a way to track how many rows are flowing through a particular path. You can use this information in logs as diagnostic information or for sending in an email as a notification of the work a package has completed. Counts the rows flowing through the data flow and writes the final count in a variable. Here we are gonna discuss an example on the same. In the Control Flow tab, add a variable named “RowCnt.” Ensure that the variable is package-scoped and of type Int32...
Friends, In this post we are gonna discuss about Character Map Transformation in SSIS 2008R2 with Example. Character map transformation is used to make changes to string type columns only. String functions can be applied using this transformation like upper case, lower case, byte reversal and so on. To understand in detail let’s take one data flow task and add character map transformation as per below screen print. In this transformation we have to select the column. Note: we can only use string type columns in this transformation. Here...
Summary: Here we will be doing a demonstration for conditional split transformation provided in SSIS. To demonstrate this we will upload data from two text file to two different table based on some condition. Below is a screen shot of text file which will be uploaded. Here we will upload all rows with PlantType Seasonal to Seasonal table and all Plant Type Perennial to other table named Perennial. In our example we will use following objects/task of SSIS: 1) ForEach Loop container 2) Data Flow Task 3) Flat...
Friends, As you all know, Logging is a very useful feature given in SSIS which helps in Auditing and Troubleshooting. Logs are associated with packages and are configured at the package level. In this post, let’s discuss how to send LOG Info to a text file. Please follow the below steps - 1) Open the package to which you wish to enable logging. Right click on the control flow of the package and select Logging option. 2) We have option at which level of package we have to capture log details,it can either at the whole package...
Friends, The data conversion transformation takes input columns and creates a copy of that column with selected (new) data type. If source columns data types are not matched with target columns, some cases SSIS will throw validation/failure error. SQL Server to SSIS data type: SQL server SSIS smallint two-byte signed intger (DT_I2) int four-byte signed integer (DT_I4) bigint eight-byte signed integer (DT_I8) float double-precision float (DT_R8) char,varchar string (DT_STR) nchar,nvarchar unicode string (DT_WSTR) decimal,numeric numeric(DT_NUMERIC) decimal decimal...
Friends, Row Sampling and Percentage Sampling both are similar task and perform the same function with little difference. The Row Sampling transformation is used to obtain a randomly selected subset of an input dataset. You can specify the exact size of the output sample, and specify a seed for the random number generator. There are many applications for random sampling. For example, a company that wanted to randomly select 50 employees to receive prizes in a lottery could use the Row Sampling transformation on the employee database to...
Friends, In last post we have seen the use of Aggregate Tranformation with example (Click Here) and in this we are gonna discuss about How to Configure more than one Aggregation in one Aggregate transformation. The Aggregate Transformation can support multiple outputs – this means you can read the data set into memory once, then aggregate it up as many ways as you like. By clicking the Advanced button on the Aggregate tab of the component editor, a new grid is revealed. If you enter a new value in the “Aggregation Name” column,...
Friends, The Aggregate transformation is used to perform aggregate operations/functions on groups in a dataset. The aggregate functions available are- Count, Count Distinct, Sum, Average, Minimum and Maximum. The Aggregate transformation has one input and one or more outputs. It does not support an error output. The Aggregate transformation supports the following operations. Group By: Divides datasets into groups. Columns of any data type can be used for grouping. Sum: Sums the values in a column. Only columns with numeric data types can...
Friends, A Flat File source is a data flow component that uses metadata defined by a Flat File connection manager to specify the format and structure of the data to be extracted from the flat file by a transform process. The Flat File source can be configured to extract data from a single flat file by using the file format definition provided by the Flat File connection manager. PFB the steps in configuring Flat file source: For configuring Flat file source, you should have a Data Flow Task. You can Take Data Flow task in Control Flow...
Friends, Thanks for all your comments for my SSAS and SSRS Videos and also thanks for the interest you have shown on my SSIS Videos. To be honest I didnot touch SSIS in last 2 years and hence I should accept that I am not the best bet for the same tool. But, after getting requests from hundreds (believe me pls) of MSBI techies for my SSIS videos, I am uploading the same here. Please do forgive me if some mistakes rolled in my videos Again, I am making these videos to the ppl who are starting their career on MSBI and hope this helps you...
Friends, SQL Server Integration Services includes log providers that you can use to implement logging in packages, containers, and tasks. With logging, you can capture run-time information about a package, helping you audit and troubleshoot a package every time it is run. For example, a log can capture the name of the operator who ran the package and the time the package began and finished. You can configure the scope of logging that occurs during a package execution on the Integration Services server. Logs are associated with packages...