[ B1zT@Lk ]

SQL MDS - Updating the Name attribute of member using Staging Table

Randy Aldrich Paulo — Thu, 11 Mar 2010 08:20:40 GMT

Creating member is usually done by populating the Member Staging Table (tblStgMember), during this process you assign a value for member code and member name. Now if you want to update the member name attribute you can do this by adding record in Attribute staging table (tblStgMemberAttribute) with Attribute Name = "Name". If you try populating the tblStgMember table it will say that the member code already exists.

INSERT INTO mdm.tblStgMemberAttribute (ModelName, EntityName, MemberType_ID, MemberCode, AttributeName, AttributeValue) VALUES

(N'Product', N'Product', 1, N'BK-M101', N'Name',N'Updated Member Name Description')

Uploading documents to WSS (Windows Sharepoint Services) using SSIS

Randy Aldrich Paulo — Tue, 16 Feb 2010 09:33:09 GMT

Recently I was tasked to create an SSIS application that will query a database, split the results with certain criteria and create CSV file for every result and upload the file to a Sharepoint Document Library site. I've search the web and compiled the steps I've taken to build the solution.

Summary:

A) Create a proxy class of WSS Copy.asmx.

B) Create a wrapper class for the proxy class and add a mechanism to check if the file is existing and delete method.

C) Create an SSIS and call the wrapper class to transfer the files.

A) Creating Proxy Class

1) Go to Visual Studio Command Prompt type wsdl http://[sharepoint site]/_vti_bin/Copy.asmx this will generate the proxy class (Copy.cs) that will be added to the solution.

2) Add Copy.cs to solution and create another constructor for Copy() that will accept additional parameters url, userName, password and domain.

public Copy(string url, string userName, string password, string domain)

{

this.Url = url;

this.Credentials = new System.Net.NetworkCredential(userName, password, domain);

}

3) Add a namespace.

B) Wrapper Class

Create a C# new library that references the Proxy Class.

C) Create SSIS

SSIS solution is composed of:

1) Execute SQL Task, returns a single column rows containing the criteria.

2) Foreach Loop Container - loops per result from query (SQL Task) and creates a CSV file on a certain folder.

3) Script Task - calls the wrapper class to upload CSV files located on a certain folder to targer WSS Document Library

Note: I've created another overload of CopyFiles that accepts a Directory Info instead of file location that loops thru the contents of the folder.

Designer View

Variable View

WCF-SQL Adapter MSDTC / Identity Insert Issue

Randy Aldrich Paulo — Fri, 03 Jul 2009 08:58:25 GMT

I was working on a Biztalk integration project that perform a CRUD operation using old SQL adapter (the new one is available via Microsoft Adapter Pack 2.0), everything works fine on the development environment but when we've tried to deploy it to test environment which have the same structure with the production server we stumble again on MSDTC issues.

To cut the story short it was too complicated and too many settings (firewall, NETBIOS etc) needs to be change since the database server is located on DMZ. The next day while I'm configuring the mySAP adapter, I found out that license has expired, since we've downloaded the trial version of BizTalk adapter pack 1.0. So I have to look for another setup, it so happen that for some reason I can't find the 1.0 version that's why I've installed the Adapter pack 2.0. I was a little bit surprise to see that there's a new SQL Adapter included. So I've installed it and give it a try and it works fine.

Two important sql binding settings that needs to be set are:

1) useAmbientTransaction = false, to solve MSDTC issue.

2) allowIdentityInsert = true, if you're trying to add rows to a table that has identity column.

SSIS - “OLE DB provider ‘STREAM’ for linked server ‘(null)’ returned invalid data for column ‘[!BulkInsert].column_name’."

Randy Aldrich Paulo — Fri, 06 Mar 2009 10:24:20 GMT

I've been trying all day to solve the problem with export of data using SSIS and keep encountering this error: "OLE DB provider 'STREAM' for linked server '(null)' returned invalid data for column '[!BulkInsert].{column_name}'.

It seems that data in that column is invalid so I've tried to add validation to my source OLEDB source. I've created a dataflow to filter all records that have bad data and redirect it to a file. To do this in the query of my source OLEDB I've add the following condition: WHERE isnumeric(convert(varchar(250), coalesce([{column_name}],0))) = 0 then put the results in the file IDs and etc, but I removed the {column_name} to prevent errors.

I now have the IDs of bad data and will try to some fixes before do the import.

SQL 2005 Configuring SQL Job to send email notification if it failed.

Randy Aldrich Paulo — Wed, 04 Mar 2009 15:24:19 GMT

This are the simple steps to enable it.

1) Run SQL Server Surface Area Configuration - > Surface Area Configuration for features ->

Select Database Mail - > Check Enable Database mail stored procedures.

2) Open SQL Management Studio - > Management - > Database mail - > Configure SMTP Settings.

3) Go to SQL Server Agent - > Operators - > Create New Operator

4) Go to SQL Job - > In the Notifications, Check email and select the Operator. You can either

select the option to send notification if job fails / succeeds/ completed.

Lastly, don't forget to restart the SQL Agent.

Installing Master Data Management on Windows x64

Randy Aldrich Paulo — Tue, 24 Feb 2009 07:56:03 GMT

I've tried to installed it on Windows x64 machine and based on documentation it's pretty straight forward install. But upon running the site I'm getting an error which is W3SVC-WP could not load ISAPI Filters, at first I've no idea what this is, I tried serveral thing like reinstalling IIS, registering asp.net and even reinstalling the MDM and reconfiguring report server. I've been doing this for the next 2 hours then finally it worked, this is the steps I've performed ( see Highlighted part )

1) .NET 2.0/.NET 3.5

2) Install SQL, SSRS

3) Create a user with create db permission

4) Configure SSRS - optional

5) Reinstall IIS 6.0

6) Run Script: C:\Inetpub\AdminScripts cscript.exe adsutil.vbs set W3SVC/AppPools/Enable32BitAppOnWin64 true
7) Register ASP.NET in 32Bit : Navigate to c:\Windows\Microsoft.Net\Framework\v2.0.50727 - this is the 32bit version

Run aspnet_regiis.exe -i to install asp.net 2.0 32bit

8) In Web Service Extension, Permit ASP.NET 2.0 (32 bit)

9) Create Web Site

10) Create MDM App Pool

11) Install MDM and use the new site and app pool.

Storing files to database and downloading it using ASP.NET

Randy Aldrich Paulo — Thu, 20 Nov 2008 08:59:47 GMT

1) First create a new column and set it as varbinary(MAX) for the datatype.

2) Use this code to store the file to database:

            //Read File to Bytes
                    FileStream st = new FileStream(<Location of the file>, FileMode.Open);
                    byte[] fileData= new byte[st.Length];
                    st.Read(fileData, 0, (int)st.Length);
                    st.Close();

SqlParameter[] param = new SqlParameter[] { new SqlParameter("@ID",<Reference ID>),
new SqlParameter("@Data",fileData) };

int i = SqlAccessor.ExecuteNonQuery(<ConnectionString>, SqlAccessor.SqlCommandBuilder(new SqlCommand (<Stored Procedure Name>), param), CommandType.StoredProcedure, out retVal);

I've used Application Blocks to simplify data operation.

To Retrieve the data and to be downloaded from an ASP.NET Page:

3) I created a function that returns a byte array with ID as parameter

public static byte[] GetFileFromDB(string id)
    {
        object[] retVal = null;
        byte[] file = null;
        SqlParameter[] param = new SqlParameter[]
        {
            new SqlParameter ("@id", id)
        };
        string _connString = ConfigurationManager.ConnectionStrings[<ConnectionStringName>].ConnectionString.ToString();
        SqlDataReader reader = SqlAccessor.ExecuteReader(_connString, SqlAccessor.SqlCommandBuilder(new SqlCommand("SourceFileLogSelFileDataProc"), param), CommandType.StoredProcedure, out retVal);

        if (reader.Read())
        {
            file = (byte[])reader["<varbinary column that we define in Step 1>"];
        }

return file;
}

4) And on the ASP.NET Page itself, on the Page_Load event:

          byte[] fileData = Utils.GetFileFromDB(<ID>);
            Response.ClearContent();
            Response.AddHeader("Content-Disposition", "attachment; filename=" + <filename>);
            BinaryWriter bw = new BinaryWriter(Response.OutputStream);
            bw.Write(fileData);
            bw.Close();
            Response.ContentType = ReturnExtension(<file extension>);
            Response.End();

5) I have another method to determine the ContentType:

  private string ReturnExtension(string fileExtension)
    {
        switch (fileExtension)
        {
            case ".htm":
            case ".html":
            case ".log":
                return "text/HTML";
            case ".txt":
                return "text/plain";
            case ".doc":
                return "application/ms-word";
            case ".tiff":
            case ".tif":
                return "image/tiff";
            case ".asf":
                return "video/x-ms-asf";
            case ".avi":
                return "video/avi";
            case ".zip":
                return "application/zip";
            case ".xls":
            case ".csv":
                return "application/vnd.ms-excel";
            case ".gif":
                return "image/gif";
            case ".jpg":
            case "jpeg":
                return "image/jpeg";
            case ".bmp":
                return "image/bmp";
            case ".wav":
                return "audio/wav";
            case ".mp3":
                return "audio/mpeg3";
            case ".mpg":
            case "mpeg":
                return "video/mpeg";
            case ".rtf":
                return "application/rtf";
            case ".asp":
                return "text/asp";
            case ".pdf":
                return "application/pdf";
            case ".fdf":
                return "application/vnd.fdf";
            case ".ppt":
                return "application/mspowerpoint";
            case ".dwg":
                return "image/vnd.dwg";
            case ".msg":
                return "application/msoutlook";
            case ".xml":
            case ".sdxl":
                return "application/xml";
            case ".xdp":
                return "application/vnd.adobe.xdp+xml";
            default:
                return "application/octet-stream";
        }
    }

Hope this helps.

BizTalk Database Maintenance

Randy Aldrich Paulo — Mon, 05 May 2008 07:23:16 GMT

Out of the box, BizTalk Server 2006 doesn't provide any tool for auto clearing and cleaning up database used by BizTalk, this cause problems like performance and data storage issues. While browsing BizTalk installation directory (\BizTalk Installation Folder\Schema) I've found out few sql scripts that can used to cleanup the MessageBox = BizTalkMsgBoxDb and Tracking = BizTalkDTADb database.

To clean MessageBox:

1) Stop all BizTalk related services.

2) Open Analyzer and open sql script: msgbox_cleanup_logic.sql, press F5 (this will create the sp) - be sure to use BizTalkMsgBoxDb.

3) run bts_CleanupMsgbox

4) Start services.

For Tracking:

1) Execute dtasp_CleanHMData sp from BizTalkDTADb

There are many sql script located in that Schema Directory and by reading and analyzing this scripts it will give us better understanding on how BizTalk stores the data.

Changing Users Email address in MOSS 2007

raclede — Wed, 25 Jul 2007 07:27:27 GMT

It's been a week since I've installed MOSS on our development server and found out that it's quite easy to install and configure except for changing the email of users associated in MOSS. I've done some research and here is it:

1) Go to Sharepoint Server (physical server), Click Start -> Microsoft Office Server -> Sharepoint 3.0 Central Administration.

2) Login as administrator, on the left pane click Shared Services Administration.

3) Click the default Shared Services by default it's SharedServices1. Login as administrator.

4) Click user profiles and properties.

5) Click view user profiles.

6) User list is displayed. To edit the email address click the dropdown menu and select edit.

Note:

When adding user to group or site just click the address book popup to browse to the list of users (email address can be seen in this list) .

BizTalk Server 2006 Prerequisite

raclede — Tue, 15 May 2007 08:40:19 GMT

Link can be found below:

http://msdn2.microsoft.com/en-us/library/aa578652.aspx

C# Using ThreadPool for Multi-Threaded Application

raclede — Mon, 19 Mar 2007 05:01:58 GMT

I'm currently designing an application specifically to handle multiple processing at a certain time. I've read all articles about threading, whether to use a background worker, custom thread management, or by using a thread pool. What facinates me is the thread pool, just by passing a workitem viola you have a working multi-threaded application. See simple example below:

using System;
using System.Threading;

public class MyProcess
{

private ManualResetEvent _doneEvent;

    public MyProcess(ManualResetEvent doneEvent)
    {
        _doneEvent = doneEvent;
    }

    public void MyProcessThreadPoolCallback(Object threadContext)
    {
        int threadIndex = (int)threadContext;
        Console.WriteLine("thread {0} started...", threadIndex);
        StartProcess();
        Console.WriteLine("thread {0} end...", threadIndex);

    // Indicates that the process had been completed
        _doneEvent.Set();
    }

    public void StartProcess()
    {
        // TODO: Add code for processing here

}

}

public class ThreadPoolExample
{
    static void Main()
    {
        const int totalCountToProcess = 10;

        ManualResetEvent[] doneEvents = new ManualResetEvent[totalCountToProcess];
        MyProcess[] MyProcessArray = new MyProcess[totalCountToProcess];

        // Configure and launch threads using ThreadPool:
        Console.WriteLine("launching tasks...");
        for (int i = 0; i < totalCountToProcess ; i++)
        {
            doneEvents[i] = new ManualResetEvent(false);
            MyProcess p = new MyProcess(doneEvents[i]);
            MyProcess[i] = p;
            ThreadPool.QueueUserWorkItem(p.MyProcessThreadPoolCallback, i);
        }

        // Wait for all threads in pool to finished processing

        WaitHandle.WaitAll(doneEvents);
        Console.WriteLine("All Process are complete.");

           }
}

N-Tier Web Applications using ASP.NET 2.0 and SQL Server 2005

raclede — Mon, 19 Feb 2007 10:02:25 GMT

Been looking for articles on how to implement a N-tier Web applications using ASP.NET 2.0 and SQL 2005 and I think this article clearly provide techniques and samples on how to.

http://www.15seconds.com/issue/050721.htm

ASP.NET AJAX "Atlas"

raclede — Thu, 15 Feb 2007 04:26:44 GMT

Microsoft recently released the v. 1.0 of ASP.NET AJAX "Atlas" and I've attended a demo about Atlas and find it really cool. Just simple drag and drop, cut and paste viola... you have an AJAX enabled website. It also include AJAX toolkit (extenders) which can be downloaded for free.

Microsoft offers new certification for ASP.NET Developers named MCDDP which stands for Microsoft Ceritified Drag-and-Drop Professional..

http://ajax.asp.net

Microsoft BizTalk 2004 SP2 Installation

raclede — Thu, 15 Feb 2007 03:32:44 GMT

I've tried to install the latest Service Pack of BizTalk 2004 and prompts me an installation error.

The Patch b3ce917f-b0f4-4b45-8fa7-97e44aec4a0e in the package Microsoft BizTalk Server 2004 Service Pack - (SP2) cannot be applied. It requires the installed version of Microsoft Enterprise Single Sign-On to be between 3.0.6070.0 and 3.0.7405.0. The installed version on this computer is 3.0.4902.0.

I googled the error and found that solution is quite simple.. install the SP1 prior to the installation of SP2. (Huh!) weird, cause the normal Service packs from Microsoft doesn't require the installing the previous SPs.

BizTalk Database Customization

raclede — Fri, 21 Jul 2006 13:28:00 GMT

One of the problems we face during the development of BizTalk Application is that lack of space in the main drive C:\, Since we do default installation for MSSQL and BizTalk 2004, the Data File and Log Files is of course is installed in C:\Program Files\Microsoft SQL Server\MSSQL\Data.

To solve this is we have to move all BizTalk's DB from C:\ to another drive. Another thing is, we also do have the CovastDB which is used in X12 parsing which has to be moved also.

Note: Before performing this operation you should first stop the following services:

Enterprise Single Sign-On Services

BizTalk Service BizTalk Group : BizTalkServerApplication

Covast EDI Service

Script:

sp_detach_db 'BAMArchive'
sp_detach_db 'BAMPrimaryImport'
sp_detach_db 'BizTalkDTADB'
sp_detach_db 'BizTalkEDIDb'
sp_detach_db 'BizTalkHwsDb'
sp_detach_db 'BizTalkMgmtDb'
sp_detach_db 'BizTalkMsgBoxDb'
sp_detach_db 'BizTalkRuleEngineDb'
sp_detach_db 'SSODB'
sp_detach_db 'CovastDB'

sp_attach_db 'BAMArchive',
'E:\DBDat\BAMArchive.mdf',
'F:\DBLOG\BAMArchive_log.LDF'

sp_attach_db 'BAMPrimaryImport',
'E:\DBDat\BAMPrimaryImport.mdf',
'F:\DBLOG\BAMPrimaryImport_log.LDF'

sp_attach_db 'BizTalkDTADB',
'E:\DBDat\BizTalkDTADB.mdf',
'F:\DBLOG\BizTalkDTADB_log.LDF'

sp_attach_db 'BizTalkEDIDb',
'E:\DBDat\BizTalkEDIDb.mdf',
'F:\DBLOG\BizTalkEDIDb_log.LDF'

sp_attach_db 'BizTalkHwsDb',
'E:\DBDat\BizTalkHwsDb.mdf',
'F:\DBLOG\BizTalkHwsDb_log.LDF'

sp_attach_db 'BizTalkMgmtDb',
'E:\DBDat\BizTalkMgmtDb.mdf',
'F:\DBLOG\BizTalkMgmtDb_log.LDF'

sp_attach_db 'BizTalkMsgBoxDb',
'E:\DBDat\BizTalkMsgBoxDb.mdf',
'F:\DBLOG\BizTalkMsgBoxDb_log.LDF'

sp_attach_db 'BizTalkRuleEngineDb',
'E:\DBDat\BizTalkRuleEngineDb.mdf',
'F:\DBLOG\BizTalkRuleEngineDb_log.LDF'

sp_attach_db 'SSODB',
'E:\DBDat\SSODB.mdf',
'F:\DBLOG\SSODB_log.LDF'

sp_attach_db 'CovastDB',
'E:\DBDat\CovastDB.mdf',
'F:\DBLOG\CovastDB_log.LDF'

Luckily, BizTalk 2004 and Covast still work.