Posts

SQL Function to replace special characters from a string

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
Post a Comment
Read more

Excel Based Reporting

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
Post a Comment
Read more

Sync SSAS Cube From One Environment to Another

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 >
Post a Comment
Read more

XML Data Load Using SSIS Script task C#.Net

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() + "|"        
Post a Comment
Read more

List of Columns of a Table in CSV Format

DECLARE @TABLE_NAME VARCHAR ( 128 ) DECLARE @SCHEMA_NAME VARCHAR ( 128 ) ----------------------------------------------------------------------- -- *** User Customisation -- Set up the name of the table here : SET @TABLE_NAME = 'Address' -- Set up the name of the schema here, or just leave set to 'dbo' : SET @SCHEMA_NAME = 'dbo' ----------------------------------------------------------------------- DECLARE @vvc_ColumnName VARCHAR ( 128 ) DECLARE @vvc_ColumnList VARCHAR ( MAX ) IF @SCHEMA_NAME = ''   BEGIN   PRINT 'Error : No schema defined!'   RETURN   END IF NOT EXISTS ( SELECT * FROM sys . tables T JOIN sys . schemas S           ON T . schema_id = S . schema_id           WHERE T . Name = @TABLE_NAME AND S . name = @SCHEMA_NAME )   BEGIN   PRINT 'Error : The table ''' + @TABLE_NAME + ''' in schema ''' +         @SCHEMA_NAME + &
Post a Comment
Read more