SP run helper

// Get Result from specific SP
int id   = 1;
int type = 0;
DataTable attributes = GetSpResults(conn
    , "[dbo].[sp_GetSomeAttributes]"
    , new SqlParameter("@id", (Int32)id)
    , new SqlParameter("@type", (Int32)type)
    );
   ...
// General request for DataTable
private DataTable GetSpResults(SqlConnection conn, string spName, params SqlParameter[] parms)
{
    SqlCommand cmd = new SqlCommand();
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.CommandText = spName;
    foreach(SqlParameter parm in parms)
        cmd.Parameters.Add(parm);
    cmd.Connection = conn;

    SqlDataAdapter sqlAdapter = new SqlDataAdapter(cmd);
    DataTable dtable = new DataTable();
    sqlAdapter.Fill(dtable);

    return dtable;
}

run once block

string some_text;
string file = "test.txt";
// Run once and break where necessary
do
{
    // If no file then SKIP
    if (!File.Exists(file))
        break;
        
    if (any_condition_to_skip)
        break;

    // Sample code to execute
    using (StreamReader sr = new StreamReader(file))
    {
        some_text = sr.ReadToEnd();
    }

// Run once
} while (false);            

run external package with custom listener

Microsoft.SqlServer.Dts.Runtime.Application app = new Microsoft.SqlServer.Dts.Runtime.Application();
CustEventListener eventListener = new CustEventListener(Dts);
// Load package
string packageName = "test.dtsx";
Package childPackage = app.LoadPackage(packageName, eventListener);
// Execute
DTSExecResult dtsResult = childPackage.Execute(null, null, eventListener, null, null);
if (dtsResult == DTSExecResult.Success)
{
    Log("Success running child package {0}", packageName);
}
  ...

public class CustEventListener : Microsoft.SqlServer.Dts.Runtime.DefaultEvents
{
    Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptObjectModel dts;
    public CustEventListener(Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptObjectModel dts)
    {
        this.dts = dts;
    }

    public override bool OnError(DtsObject source, int errorCode,string subComponent, string description, string helpFile,int helpContext, string idofInterfaceWithError)
    {
        dts.Events.FireError(errorCode, subComponent, description, helpFile, helpContext);
        return false;    
    }

    public override void OnInformation(DtsObject source, int informationCode,string subComponent, string description, string helpFile,int helpContext, string idofInterfaceWithError, ref bool fireAgain)
    {
        dts.Events.FireInformation(informationCode, subComponent, description, helpFile, helpContext, ref fireAgain);

    }

    public override void OnWarning(DtsObject source, int warningCode,string subComponent, string description, string helpFile,int helpContext, string idofInterfaceWithError)
    {
        dts.Events.FireWarning(warningCode, subComponent, description, helpFile, helpContext);
    }
}

logging methods for script component

Log("Some log text {0}, {1}, {2}", 1, 2, 3);
   or
Log("Error-{0}; Description-{1}\n", Get("System::ErrorCode"), Get("System::ErrorDescription"));

 ...

private void Log() { Log(new string('*', 20)); }
private void Log(string str, params object[] pars)
{
    bool fireAgain = false;
    Dts.Events.FireInformation(1, "custom", string.Format(str, pars), "", 0, ref fireAgain);
       or
    string logFile = "d:\\SSIS.log";
    File.AppendAllText(logFile, string.Format(str, pars));
}

format number with comas in select

1. select statement to format number with decimals and without, using money type and convert function

;WITH cte(d) AS
    (SELECT CONVERT(VARCHAR, CONVERT(MONEY, 123456789), 1) AS delimited_number)

SELECT 
    d 
    , REPLACE(d,'.00', '') sol1
    , SUBSTRING(d, 1, LEN(d)-3) sol2
    , PARSENAME(d, 2) sol3
FROM cte

2. result of select

d                 sol1               sol2               sol3
----------------- ------------------ ------------------ ------------------
123,456,789.00    123,456,789        123,456,789        123,456,789

helper methods to get, set package variables during long running script

Set package variable in Script component without long locking

Set("User::number", GetInt("User::number") + 1);
...
// Helper methods to be added to Script code
private int GetInt(string varName)
{
    Variables lockedVariables = null;
    int var;
    Dts.VariableDispenser.LockOneForRead(varName, ref lockedVariables);
    var = (int)lockedVariables[varName].Value;
    lockedVariables.Unlock();
    return var;
}
private string GetStr(string varName)
{
    Variables lockedVariables = null;
    string var;
    Dts.VariableDispenser.LockOneForRead(varName, ref lockedVariables);
    var = lockedVariables[varName].Value.ToString();
    lockedVariables.Unlock();
    return var;
}
private void Set(string varName, int value)
{
    Variables lockedVariables = null;
    Dts.VariableDispenser.LockOneForWrite(varName, ref lockedVariables);
    lockedVariables[varName].Value = value;
    lockedVariables.Unlock();
}
private void Set(string varName, string value)
{
    Variables lockedVariables = null;
    Dts.VariableDispenser.LockOneForWrite(varName, ref lockedVariables);
    lockedVariables[varName].Value = value;
    lockedVariables.Unlock();
}

transfer multirow parameters to one var

1. Create test table

CREATE TABLE dbo.Params(
   id int IDENTITY(1,1) NOT NULL
 , param varchar(50) NULL )

2. Add test records for parameters

INSERT INTO dbo.Params (param)
VALUES ('some = 1')
  , ('some = 2')
  , ('some = 3')
  , ('some = 4')
  , ('some = 5')

3. Get parameters as one row using PIVOT

SELECT 
   isnull([1],'') [param1]
 , isnull([2],'') [param2]
 , isnull([3],'') [param3]
 , isnull([4],'') [param4]
 , isnull([5],'') [param5]
FROM (
 SELECT row_number() OVER (ORDER BY param) AS rn, param 
 FROM dbo.Params 
 WHERE param IS NOT NULL
) S
PIVOT
(
 MAX(param)
 FOR rn IN ([1],[2],[3], [4], [5])
) AS PivotTable;

4. Result of SELECT

param1    param2    param3    param4    param5
--------- --------- --------- --------- ----------
some = 1  some = 2  some = 3  some = 4  some = 5
(1 row(s) affected)

5. Use variable expression to get one string

(@[User::param1] == "")?"":@[User::param1] 
+ ( (@[User::param2] == "")?"": " AND " + @[User::param2] )
+ ( (@[User::param3] == "")?"": " AND " + @[User::param3] )
+ ( (@[User::param4] == "")?"": " AND " + @[User::param4] )
+ ( (@[User::param5] == "")?"": " AND " + @[User::param5] )

6. Now we have parameters combined in one variable

some = 1  AND some = 2  AND some = 3  AND some = 4  AND some = 5

test manoli.net/csharpformat/

not pastebin, but generates colored HTML

using System.IO;
public void Main() 
{
    // READWRITE - User::result
    string msg = string.Format("\tsome result {0}", variable);
    Dts.Variables["User::result"].Value = Dts.Variables["User::result"].Value.ToString() + msg;
    Log(msg);
}
private void Log(string str, params object[] pars) 
{
    bool fireAgain = false;
    Dts.Events.FireInformation(1, "control", string.Format(str, pars), "", 0, ref fireAgain);
}

test pastebins for code share

ideone.com - currently better for me, just slowest


pastie.org - easy, light, but green for key words ...

codetidy.com - light, but also green and scrolling as of iframe usage


gist.github.com - black for font ... so-so

pastebin.com - ... those gray lines ...

some file related

1. drop empty files from folder

string[] files = Directory.GetFiles(dirName);
foreach (string file in files)
{
    FileInfo fi = new FileInfo(file);
    if (fi.Length == 0)
        fi.Delete();
}

2. append text to file

string msg = "some text";
bool   append = True;

using (TextWriter tw = new StreamWriter(file, append))
    tw.WriteLine(msg);
  .. or ..
using (StreamWriter sw = File.AppendText(file))
    sw.WriteLine(msg);
  .. or ..
File.AppendAllText(file, msg + "\n");

check if folder exists and can be read

using System.IO;
using System.Security.AccessControl;
...
bool dirExists = Directory.Exists(fileDir);
bool accessToDir = ReadAccess(fileDir);
if (dirExists && accessToDir)
    Console.Write("there is access");
    ...
public bool ReadAccess(string dir)
{
    bool allow = false;
    bool deny = false;
    DirectorySecurity dirSecurity = Directory.GetAccessControl(dir);
    if (dirSecurity == null)
        return false;
    AuthorizationRuleCollection rules = dirSecurity.GetAccessRules(true, true
            ,typeof(System.Security.Principal.SecurityIdentifier));
    if (rules == null)
        return false;
    foreach (FileSystemAccessRule rule in rules)
    {
        if ((FileSystemRights.Read & rule.FileSystemRights) == FileSystemRights.Read)
        {
            if (rule.AccessControlType == AccessControlType.Allow)
                allow = true;
            else if (rule.AccessControlType == AccessControlType.Deny)
                deny = true;
        }
    }
    return allow && !deny;
}

timestamp SSIS expression

SSIS expression to add timestamp to make unique file names

read last line from text file

use filestream to get last line from big text file

using System;
using System.IO;
using System.Text;

class Program
{
    static void Main(string[] args)
    {
        string[] files = Directory.GetFiles("d:\\", "*.txt");
        foreach (string file in files)
        {
            string lastLine = Util.ReadLastLine(file);
            Console.WriteLine("File [{0}] has next last line:\n\t - \"{1}\"", file, lastLine);
        }
        Console.ReadKey();
    }
}
class Util
{
    public static string ReadLastLine(string fileName)
    {
        string lastLine = null;
        int buffsize = 1000; // Should be set to regular Line Size * 2-3

        if (!File.Exists(fileName))
            return lastLine;

        using (FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read))
        {
            if (fs.Length == 0)
                return lastLine;

            // create buffer
            buffsize = (int)Math.Min(buffsize, fs.Length);
            byte[] buffer = new byte[buffsize]; 

            // Read last 1000 bytes from the end
            fs.Seek(-buffsize, SeekOrigin.End);
            fs.Read(buffer, 0, buffer.Length);
            // Convert to ASCII string
            string strBuffer = Encoding.ASCII.GetString(buffer, 0, buffer.Length);

            // get the lines
            string[] strArr = strBuffer.Split('\n');
            // get last line
            lastLine = strArr[strArr.Length - 1];
        }

        return lastLine;
    }
}

start my blogging

just some code lines to copy-paste

new, class, byte - key word
FileStream, FileMode - class names
// comment - comments
"some string", '\n' - string
isnull - SQL function