Record Value Frequency In SSIS

I blogged last week about a problem on Record Value Frequency in SSIS. I suggested in the blog post on how to go about taking care of this.

“The only way I could see out of this is to write a CLR function to achieve this which I am feeling so lazy to do but still has to be done.”

This I finally had time to do and pasted below is the simple CLR User Defined Functions (UDF)

static readonly Dictionary<SqlString, SqlInt32> myDic = new Dictionary<SqlString, SqlInt32>();

    [Microsoft.SqlServer.Server.SqlFunction]

    public static SqlString SvSequence(SqlString colValue)

    {

        SqlInt32 nextValue = 1;

        if (myDic.ContainsKey(colValue))

        {

            myDic[colValue] += 1;

            nextValue = myDic[colValue];

        }

        else

            myDic.Add(colValue, 1);

        // Put your code here

        return nextValue.ToSqlString();

    }

    [Microsoft.SqlServer.Server.SqlFunction]

    public static SqlString RestartSequence()

    {

        myDic.Clear();

        return new SqlString();

    }

Notice the readonly used in the static declaration for the dictionary. This is enforced by the compiler for static class member as a requirement. I also had to create a reset function which IMHO is not that cool but I need a way to reset this as the value kept increasing each time the UDF is called.

dbo.RestartSequence ;

select AccountId, dbo.SvSequence(AccountId) from dump

Record Value Frequency

First I’d like to quote my recent tweet on this issue

Have always considered SSIS to be extremely flexible but lately found wrong. Nevertheless, yet to find any ETL too that can beat it

I think I may have been wrong to blame this on SSIS but rather on limitations of TSQL itself. My situation is a need to have frequency of a record value indicated in a column as indices as below

Value
SEQNR
1
1
2
1
2
2
4
1
4
2
4
3

I tried to use Rank() with partition as shown in snippet below but got a different result. Also ‘Bing(ed) this but came up void of a similar issue.

select value, Rank() over (partition by Value order by Value)

This is like to use and inject into SSIS package. Doing this with combination of SSIS task is not quite straightforward.

The only way I could see out of this is to write a CLR function to achieve this which I am feeling so lazy to do but still has to be done.

Ftp’ing files in Folder in SSIS

Recently, I came across a need to copy all csv files generated from an SSIS process to a Unix server. FTP became handy for this purpose. The FTP SSIS task will not work with folder/directories – came up with error message

File Usage type of connection "xxxxxx" should be "FileExists" for operation Send

This error message is quite misleading. The interpretation is that once you the File Connection Manager being used in the FTP task has a property Usage Type and this Usage Type must be set to “File exists”. This automatically limits the usage of the FTP task to files rather than folder/directories.

The solution is to use the Foreach Loop Container task and place the FTP task within it. Literarily, the loop container enumerates all the files and place them in a variable. This variable is then used in the FTP task.

 
Foreach Loop Container with FTP task embedded

Loop Container configuration

FTP task configuration

Replace NewLine in TSQL

It's quite frustrating how tiny task become so time-consuming and worrisome. Never would have imagined this hard to do. 

Had a situation where records gathered from an application will serve as input to a large application. The data first had to be cleaned (glad I got Sql server tools). This was done in seconds. On export to CSV, it was discovered that one of the fields (address field) had newline inputed in it causing wraping of the record as new line in the exported CSV.

Of course I was quick to type

update xxxxx set CustAddress = Replace(CustAddress, char(13), '')

Alas, no change. Kept getting same reponse. Even len(CustAddress ) = Replace(CustAddress, char(13), ''). 

Search for this issue on the net and could not find a direct linking article of blog reporting this scenerio. 

I finally got the answer from an article on Simple-talk by Robyn Page and Phil Factor. 

update xxxx set custaddress = REPLACE(custaddress, COALESCE(char(13) + char(10),'[^-a-z'']'), '')

Quite some other stunt in the article too.