Ashish Agarwal

CREATE Function [dbo].[replaceSpecialCharacters] (@str varchar(256)) returns varchar(256) with schemabinding begin SET @str = LTRIM(RTRIM(@str))     DECLARE @expres  VARCHAR(50) = '%[~,@,#,$,%,&,*,{,},(,),.,!,:,?,^,+,=,;,-,/,\]%'       WHILE PATINDEX( @expres, @str ) > 0          BEGIN   SET @str = REPLACE(@str, SUBSTRING( @str, PATINDEX( @expres, @str ), 1 ),'_') END SET @str = Replace(@str,' ','||') SET @str = Replace(@str,'’','') SET @str = Replace(@str,'_','') SET @str = Replace(@str,'||','_') SET @str = Replace(@str,'__','_') SET @str = Replace(@str,'__','_') SET @str = LTRIM(RTRIM(@str))     return @str END GO

Hello Everyone, so today my focus is on to give insights about how can we deal with scenarios where the client wants us to help them with Excel based reporting. We will be doing this using SSIS as we will need automation of the report on a daily basis. Technology Items used - 1. SSIS 2. Excel files (format files) 3. Macro codes 4. Conditional formatting. 5. SSIS 6. C#.Net / VB.Net Script Components Step-1:  - We will be writing a few macro codes to do the same as we will need the numeric columns to be in the numeric while we insert the data from SSIS it will automatically convert it to Text. Macro-1 - Code to Convert Text to Integer. Sub Function_Name() sheetlist = Array("Sheet1", "Sheet2", "Sheet3", "Sheet4", "Sheet5", "Sheet6", "Sheet7") For i = LBound (sheetlist) To UBound (sheetlist) Worksheets(sheetlist(i)).Activate ' Your Code Goes Here Range("F:F").Select 'specify the r

Please Execute the Below Mentioned Script in Either the SQL Agent Job or XMLA Script Window or in the SSIS Package using the "Analysis Services Execute DDL Task" -  < Synchronize  xmlns:xsi = " https://www.w3.org/2001/XMLSchema-instance " xmlns:xsd = " https://www.w3.org/2001/XMLSchema " xmlns = " https://schemas.microsoft.com/analysisservices/2003/engine " > < Source > < ConnectionString > Provider=MSOLAP.6;Data Source=SOURCESERVER; Integrated Security=SSPI;Initial Catalog=SourceCubeName </ ConnectionString > < Object > < DatabaseID > SourceCubeName </ DatabaseID > </ Object > </ Source > < SynchronizeSecurity > CopyAll </ SynchronizeSecurity > < ApplyCompression > true </ ApplyCompression > </ Synchronize >

XmlDataDocument xmldoc = new XmlDataDocument (); XmlNodeList xmlnode,xmlnode1; int i = 0,j=0; string str = null ; string FilePath = Dts.Variables[ "User::File_Name" ].Value.ToString(); int nodecount = 0; FileStream fs = new FileStream (@FilePath, FileMode .Open, FileAccess .Read); xmldoc.Load(fs); xmlnode = xmldoc.GetElementsByTagName( "TagName" ); xmlnode1 = xmldoc.GetElementsByTagName( "AnotherTagName" );             nodecount = xmlnode.Count; for (i = 0; i <= xmlnode.Count - 1; i++)      {         for (j = 8; j <= xmlnode[i].ChildNodes.Count - 2; j++)             {                 str = xmlnode[i].ChildNodes.Item(0).InnerText.Trim() + "|"                 + xmlnode[i].ChildNodes.Item(1).InnerText.Trim() + "|"                 + xmlnode[i].ChildNodes.Item(2).InnerText.Trim() + "|"                 + xmlnode[i].ChildNodes.Item(3).InnerText.Trim() + "|"