Data With Gaurav

Start with the Below screen shot ( Before Script Task) and you will come to know about the senario. Note : The Script Component also works for the special characters within the Data as well . for ex "A$hish" Hover over the below Regex. it will show > Show potential fixes > in that Select System.Text.RegularExpressions. The Below will disappear.  Use the Below Script.  public override void Input0_ProcessInputRow(Input0Buffer Row) { Row.CustomerID = RemoveSpecialCharacters(Row.CustomerID); Row.NameStyle = RemoveSpecialCharacters(Row.NameStyle); Row.Title = RemoveSpecialCharacters(Row.Title); Row.FirstName = RemoveSpecialCharacters(Row.FirstName); Row.LastName = RemoveSpecialCharacters(Row.LastName); Row.CompanyName = RemoveSpecialCharacters(Row.CompanyName); Row.SalesPerson = RemoveSpecialCharacters(Row.SalesPerson); Row.EmailAddress = RemoveSpecialCharacters(Row.EmailAddress); Row.Phone = RemoveSpecialCharacters(Row.Phone); } public static string

Skipping of rows from the TSV file can very easily done by just mentioning in a Text box which is present in the Flat File configuration wizard.  Select the no of rows you want to skip and you can see the output in the preview option.  You can also do the same setting by clicking on the flat file connection manager and changing the value in the highlighted field.

 There are mainly 3 ways in which we can skip particular rows in excel and then copy the data in the SQL server.  I have explained 2 ways in this Blog and the third method is by using a Script task, which is presently out of scope for this article.  Method 1 : In the first method , you can configure the openrowset property by right clicking on the Execute Sql task and then opening the properties > OpenRowSet .      Preview :  Method 2 : You can configure the Excel task , but in that choose Sql Command in the Excel connection manager and then type the following. This will solve the issue and also in this method you can rename the column name in the destination table as well.  

 While using the Execute SQL Task, it gives you a choice to connect to the Source or the destination , this is a very important setting as  this enbles the Execute SQL task to either run at the source or at the destination. The highlighted text is the box in which you keep the values.

Drill down Report:  ( Making Reports Interactive/Dynamic ): Ex:           +YearNo                               +Sem                                      +Qtr                                              +Month O/p Drill down Effect: Column Name Visibility Toggles Item ProdCat Show - SubCat Hide ProdCat OrderYear Show - OrderQtr Hide OrderYear OrderMonth Hide OrderQtr Country Hide OrderMonth For Name in the Row Groups. Do the setting below: Similarly for the column groups also you are supposed to do this. Grouping the Data After Grouping  Keep the First Column and Delete the nect Column. If you want to change the colour of the Fonts based on certain condition, you do this , To Add Row number in the right side of the report , you do the following settings in the text box

Create Parameter Report: To get the category as drop down. When you have to choose multiple values , you make the below changes in the Query.      Create Cascade Parameter Report: Cascade Parameters means  dependent  parameter. Ex: Create dataset with following: USe  IBank go SELECT  ACID , CustomerName , BRID ,  PID FROM  AccountMaster WHERE   BRID = @BRID  and  PID = @PID ·           First we need to add 2 parameters in our main SQL query (MainDataset). Once added it will automatically add in the Parameters in Report Data window. ·           Create new datasets to get the values in the 2 parameters. o     We need to get all the distinct values in the 2nd parameter.   Ex:  SELECT   DISTINCT ( BRID )   FROM  AccountMaster   o     We must need to add the WHERE clause in the 1st parameter which means second parameter is dependent on first parameter. Ex:  SELECT   DISTINCT ( PID )   FROM  AccountMaster  WHERE  BRID = @BRID Setting to show the Column Headers on each page : Report Templat