July 24, 2016

Any upgrade = disaster waiting to happen

Ah this was a intense week at work, after major upgrade of our ms sql databases most of the week after that was, and still is fixing sql agent jobs, ssis, configurations you name it we probably did it. 
Tomorrow is another day and what can't be finish today it would be there waiting tomorrow, so it is what it is and it can't be help. I have some nice coding to show but that would be a surprise. Finally, after months of trying my local Cassandra instance just die without any warnings it was working great upgrade the virtual box to the latest version and everything when down into a death spiral after that, so now there are more important things to care about and life will continue with or without us " Roaches and Fish will be the new domain species", so just relax and decompress.

July 11, 2016

StreamReading StreamWriting Using SSIS Script Task

How many times you need to stream reading from a file and writing into another well here it is...Here we are using special character char(30) and char(31) as delimited so a quick replace using regex did it. It's Simple,Quick, and Dirty :-)
Enjoy.
/*
   Microsoft SQL Server Integration Services Script Task
   Write scripts using Microsoft Visual C# 2008.
   The ScriptMain is the entry point class of the script.
*/

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.Text.RegularExpressions;
using System.IO;

namespace ST_2b1bee4bed9c4b0b8499c0b8215df02d.csproj
{
    [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {

        #region VSTA generated code
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion

        /*
The execution engine calls this method when the task executes.
To access the object model, use the Dts property. Connections, variables, events,
and logging features are available as members of the Dts property as shown in the following examples.

To reference a variable, call Dts.Variables["MyCaseSensitiveVariableName"].Value;
To post a log entry, call Dts.Log("This is my log text", 999, null);
To fire an event, call Dts.Events.FireInformation(99, "test", "hit the help message", "", 0, true);

To use the connections collection use something like the following:
ConnectionManager cm = Dts.Connections.Add("OLEDB");
cm.ConnectionString = "Data Source=localhost;Initial Catalog=AdventureWorks;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=False;";

Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.

To open Help, press F1.
*/

        public void Main()
        {
            String filePath = Dts.Variables["User::strTempFilePath"].Value.ToString();
            //char US = (char) 31;
            //char RS = (char)30;
            //File.WriteAllText(filePath, Regex.Replace(File.ReadAllText(filePath), "_x001F_",US.ToString()));
            //File.WriteAllText(filePath, Regex.Replace(File.ReadAllText(filePath), "_x001E_", RS.ToString()));
            ReplaceFile(filePath, filePath.Replace(".cHold", ".txt"));
            // TODO: Add your code here
            Dts.TaskResult = (int)ScriptResults.Success;
        }
        protected void ReplaceFile(String ImportFilePath, String ExportFilePath)
        {
           // H201606301639RowCount:50000
                //User::intCntCustomer,User::strTempFilePath,User::YYYYMMDDHHMMSS
            
            char US = (char)31;
            char RS = (char)30;
            String Header = "H"+Dts.Variables["User::YYYYMMDDHHMMSS"].Value.ToString()+"Rowcount:"+Dts.Variables["User::intCntCustomer"].Value.ToString();
            using (StreamReader vReader = new StreamReader(ImportFilePath))
            {
                using (StreamWriter vWriter = new StreamWriter(ExportFilePath))
                {
                    vWriter.WriteLine(Header);
                    while (!vReader.EndOfStream)
                    {
                        String vLine = Regex.Replace(Regex.Replace(vReader.ReadLine(), "_x001F_", US.ToString()), "_x001E_", RS.ToString());
                        vWriter.WriteLine(vLine);


                    }
                }
            }
        }

    }
}

June 24, 2016

String Sequence Combination using SQL

A friend make a great function that do a sequence combination, and after a little thought I said lets shared with the world as this could be a good example of cte recursion.
Cheers,

declare
@String VARCHAR(8000) = 'Hello how do you do today',
@Delimiter CHAR = ' ',
@n INT = 9

--output
declare @List TABLE (
Word VARCHAR(800),
WordNum INT,
n INT
)

DECLARE
@Iterator INT = 1,
@Word VARCHAR(800) = '',
@Pos INT = 1
;

DECLARE @Temp TABLE (
Word VARCHAR(800),
WordNum INT,
n INT
)

SET @String = REPLACE(LTRIM(RTRIM(@String)),'  ',' ');

WHILE @Pos > 0
BEGIN
SET @Pos = CHARINDEX(@Delimiter, @String)
IF @Pos > 0
BEGIN
SET @Word = SUBSTRING(@String,0,@Pos)
SET @String = RIGHT(@String, LEN(@String)- @Pos)
END
ELSE
BEGIN
SET @Word = @String;
END

INSERT INTO @Temp(Word, WordNum) VALUES(@Word, @Iterator);

SET @Iterator = @Iterator + 1;
END
;

WITH cte AS(
SELECT CAST(cte.Word AS VARCHAR(800)) AS Word,
cte.WordNum,
1 AS n
FROM @Temp cte
UNION ALL
SELECT CAST(cte.Word + @Delimiter + l.Word AS VARCHAR(800)) AS Word,
l.WordNum,
cte.n + 1
FROM cte
INNER JOIN @Temp l
ON cte.WordNum + 1 = l.WordNum
WHERE cte.n < @n
)
INSERT INTO @List(Word, WordNum, n)
SELECT Word, WordNum, n
FROM cte
ORDER BY n, WordNum

select * from @List
;

Contact Form

Name

Email *

Message *