DEV Community: Venkatesh Jonnagadla

SSIS - Send HTML Email from SSIS Job using DTSX package

Venkatesh Jonnagadla — Mon, 10 Nov 2025 15:05:38 +0000

When you are working in automating the reports you often require sending the result sets as part of the email body. Unfortunately, there is no direct way in SSIS to do so. We need to use some trick to achieve this.
First take the result set into a package variable (ex-gStrResultSet). You must be using the execute sql task for this. The query in the execute sql task should return the XML form of the table. For which you need to use the below format for your query.

select
(select COL1 as td for XML path(''),type),
(select COL2 as td for XML path(''),type),
(select COL3 as td for XML path(''),type),
(select COL4 as td for XML path(''),type)
from
(SELECT COL1,COL2,COL3,COL4
FROM   dbo.Table
) tbl
FOR XML PATH('tr')

Now you need to send the XML table as part of the email body along with the other text you need. As the email body is of HTML, you need to make changes in such a way that XML output fits into the HTML body of the email. The sample code(C# & HTML) goes like below-

public void Main()
                        {
            try
            {
                var emailBody = "<html><head></head><body>" +
                    "<p> Hi ,<br><br>Please find the table as HTML Report below.<br></p>" +                    
                    @"<table border=""1"" cellpadding=""2"" style=""border-collapse: collapse;"">" +
                    @"<tr style=""color:black;background-color:skyblue;"">" +
                    "<th> COL1 </th>" +
                    "<th> COL2 </th>" +                    
                    "<th> COL3 </th>" +
                    "<th> COL4 </th>" +                   
                    "</tr>";

                // Ensure variable exists and is not null
                var htmlTable = Dts.Variables["User::gStrResultSet"].Value?.ToString() ?? "";
                emailBody += htmlTable.Replace("<ROOT>", "").Replace("</ROOT>", "");
                emailBody += "</table><br><br><p>Regards,<br>SSIS Job</p></body></html>";

                string smtpServer = "Test.orgnaization.COM"; // get it from your organization
                int smtpPort = 25;


                string reportTitle = "HTML Report";
                string todayDate = DateTime.Now.ToString("MM/dd/yyyy");
                string subject = $"{reportTitle} - {todayDate}";


                string senderEmail = notification@organization.com;
                string recipientEmail = recipient@organization.com;


                MailMessage mail = new MailMessage(senderEmail, recipientEmail, subject, emailBody)
                {
                    IsBodyHtml = true
                };

                SmtpClient smtpClient = new SmtpClient(smtpServer, smtpPort)
                {
                    DeliveryMethod = SmtpDeliveryMethod.Network,
                    UseDefaultCredentials = true
                };
                mail.To.Add(toaddress1@organization.com);
                mail.To.Add(toaddress2@organization.com);                

                mail.CC.Add(ccAddress1@organization.com);
                mail.CC.Add(ccAddress2@organization.com);

                smtpClient.Send(mail);
                Dts.TaskResult = (int)ScriptResults.Success;

            }
            catch (Exception ex)
            {
                Dts.TaskResult = (int)ScriptResults.Failure;
            }            
                        }

Hope this helps!!!! Thank you!!!!!!!!!

PowerBuilder - Connect to MS SQL Server

Venkatesh Jonnagadla — Mon, 10 Nov 2025 10:24:10 +0000

You can connect to Microsoft SQL Server from PowerBuilder in different ways.
1/ Using SQL Native Client Provide
2/ Using SQL OLEDB Provider
3/ Creating a DSN and using ODBC driver
4/ Using ODBC and default SQL Server provider provided in windows machines

Using SQL Native Client

// Using SQL Native Client
SQLCA.DBMS = "SNC SQL Native Client(OLE DB)" or "SNC"
//Using OLEDB Provider
SQLCA.DBMS = "MSOLEDB" 
// below All parameters will be same
SQLCA.Database = "YourDatabase"
SQLCA.ServerName = "YourServer"
SQLCA.LogId = "your_username"
SQLCA.LogPass = "your_password"
SQLCA.AutoCommit = False
SQLCA.DBParm = "Provider='SQLNCLI11',Database='YourDatabase',TrimSpaces=1,StaticBind=0"
If it uses windows authentication, you need to add this parameter and don't use the LogID and LogPass
SQLCA.DBParm = "Provider='SQLNCLI11',Database='YourDatabase',TrimSpaces=1,StaticBind=0,TrustedConnection=1"

// Using ODBC Provider

SQLCA.DBMS = "ODBC"
SQLCA.ServerName = "YourServer"
SQLCA.dbparm="ConnectString='Driver={SQL Server};Server=myServerAddress;Database=myDataBase;PORT=portno;Trusted_Connection=Yes;'"
// If you are using SQL authentication
SQLCA.dbparm="ConnectString=Driver='{SQL Server};Server=myServerName,myPortNumber;Database=myDataBase;Uid=myUsername;Pwd=myPassword;'"

// Also using the ODBC provider you can connect to sqlserver with different types of middleware software like below

SQLCA.DBMS = "ODBC"
SQLCA.AutoCommit = False
SQLCA.DBParm="ConnectString='DRIVER={SQL Server};SERVER=servername;DATABASE=DBNAME;PORT=1435;PROTOCOL=TCPIP;Trusted_Connection=yes'"
SQLCA.DBParm="ConnectString='Driver={ODBC Driver 17 for SQL Server};Server=servername;Database=DBNAME;Trusted_Connection=yes;'"
SQLCA.DBParm="ConnectString='Driver={SQL Server Native Client 11.0};Server=servername;Database=DBNAME;Trusted_Connection=yes;'"
Connect using SQLCA;

You can use the reference - https://www.connectionstrings.com/microsoft-sql-server-odbc-driver/

Please make sure you are including the related DLL in the build
for SNC you need to include - pbsnc.dll for oledb- pbmsoledbsql.dll

PowerBuilder - select multiple rows on datawindow

Venkatesh Jonnagadla — Fri, 07 Nov 2025 17:27:17 +0000

On Datawindow clicked event -

if row>0 then
    if keydown(keycontrol!) then
        this.selectrow(row,true) 
        Else
            this.selectrow(0,false)
            this.selectrow(row,true)
    end if 
    if keydown(keyshift!) then
        this.event trigger ue_shift(row)
    end if
end if
li_selectedrow=row
this.setrow(row)

Custom event code ue_shift

long ll_oldrow,ll_newrow,ll_counter,ll_startrow,ll_endrow
ll_oldrow=li_selectedrow 

if ll_oldrow>al_row then
    ll_startrow=al_row
    ll_endrow=ll_oldrow
else
    ll_startrow=ll_oldrow
    ll_endrow=al_row
end if

for ll_counter=ll_startrow to ll_endrow step 1
    this.selectrow(ll_counter,true)
Next

this.setrow(al_row)
this.setcolumn(1)


return 0

Thank you!!

PowerBuilder - Disable Copy Paste on a dataWindow

Venkatesh Jonnagadla — Fri, 07 Nov 2025 17:23:35 +0000

create a event and map to pbm_keydown on a datawindow to disable copy paste (you can customize according to the columns)

boolean lb_1
string ls_colname

lb_1 = true 

IF lb_1 and KeyDown(KeyControl!) and KeyDown(65 ) THEN 

    messageBox("Info","Copy/Paste Disabled")

    ls_colname = this.getcolumnname( )

    If ls_colname ="lanme" then
        messageBox("Info","Copy/Paste Disabled on last name" )
    end if

END IF

PowerBuilder - TreeView Control

Venkatesh Jonnagadla — Fri, 07 Nov 2025 17:14:11 +0000

Here is an example of Treeview control code -

integer li_a,li_b,li_c
long ll_level2
// First level Operationsw
li_a=tv_test.insertitemlast(0," First Generation",1)
li_b=tv_test.insertitemlast(0," Second Generation",1)
li_c=tv_test.insertitemlast(0," Third Generation",1)

// Second level under the First Generation i.e., li_a
tv_test.insertitemlast(li_a,"1G-1stItem",2)
tv_test.insertitemlast(li_a,"1G-2ndItem",2)

// Second level under the Second Generation i.e., li_b
tv_test.insertitemlast(li_b,"2G-1stItem",2)
ll_level2=tv_test.insertitemlast(li_b,"2G-2ndItem",2)
tv_test.insertitemlast(li_b,"2G-3ndItem",2)
tv_test.insertitemlast(li_b,"2G-4ndItem",2)
tv_test.insertitemlast(li_b,"2G-5ndItem",2)
tv_test.insertitemlast(li_b,"2G-6ndItem",2)

// sub level under second generation
tv_test.insertitemlast( ll_level2, "2G-2nItem-1SubItem", 3)


tv_test.insertitemlast(li_c,"3G-1stItem",2)

To view the level and lable of the item you have clicked on a tree view control -

Selection Changed/Clicked on TreeView -

treeviewitem tvi_tvitem

//set treeview item here below
tv_test.getitem( newhandle, tvi_tvitem)

Messagebox(" Level of the treeview selected item ",String(tvi_tvitem.level))
Messagebox("Lable of the treeview Selected Item",tvi_tvitem.label)

Thank you!!!!!!

PowerBuilder - Custom window for DW properties

Venkatesh Jonnagadla — Fri, 07 Nov 2025 17:00:35 +0000

If you are utilizing PFC or any custom datawindow object in your project, this will be extremely beneficial.

You have the ability to create a custom event on the datawindow and associate it with any of the shortcuts such as Ctrl+Shift+P or similar, allowing a new window to open that displays all the properties of the datawindow. The code I am presenting here is fundamental, but you can modify it to suit your requirements. Please note that you must create window based on the variables I used in the below code.

Initially, pass the datawindow for which you wish to display properties from an event or function as illustrated below -
openwithparm(w_dwproperties,this)

design w_dwproperties according to the variables in below code -

in the w_dwproperties open() event -

powerobject lpo_arg
datawindow ldw_arg
string ls_colcount,ls_colname,ls_concatstr
int li_counter

lpo_arg=Message.powerobjectparm

if lpo_arg.typeof( )=datawindow! then
    ldw_arg=lpo_arg
    sle_1.text=ldw_arg.dataobject
    sle_2.text=ldw_arg.classname( )
    mle_1.text=ldw_arg.describe('datawindow.table.select')
    ls_colcount=ldw_arg.describe( "DataWindow.Column.Count")
    for li_counter=1 to integer(ls_colcount)
            ls_concatstr='#'+string(li_counter)+'.name'
            ls_colname=ldw_arg.describe(ls_concatstr)
            ddlb_1.additem(ls_colname)
    next
else

end if

Hope this helps!!!!!Thank you!!!!!!!!!!!

PowerBuilder - Popup Menu

Venkatesh Jonnagadla — Fri, 07 Nov 2025 16:49:35 +0000

You will observe this on the data windows. By right-clicking on a data window, you will see options to insert or delete rows, as well as to trigger certain custom events within the data window. This functionality can also be applied to the windows. Here is an example.

1/ Create menu m_popupmenu
2/ If you are going to use it on datawindow then declare instance variable like below
protected: datawindow idw_datawindow

in the menuitem's clicked event - modify accordingly
idw_datawindow.event dynamic ue_insert()

3/ On datawindow rbuttondown(map it accordingly)

m_popup lm_popup
lm_popup=create m_popup
lm_popup.idw_datawindow=this
lm_popup.popmenu( this.pointerx( )+10,this.pointery( )+10)

you must have the ue_insert() created at dw level

int li_i

li_i=this.insertrow(0)
this.scrolltorow( li_i)
if li_i<0 then
    MessageBox("Error - Insert","Error is ~n"+SQLCa.sqlerrtext)
end if

Hope this helps!!!!!!!!!!!!!!!!!!!!!!!!!!!! Thank you!!!

PowerBuilder - Dynamic Search in datawindow

Venkatesh Jonnagadla — Wed, 05 Nov 2025 18:27:42 +0000

Create an event ue_search and map to pbm_enchange event of sle_search on a window, where sle_search is where you enter the text based on which you want to search column data on a datawindow

in the ue_search event write below

string ls_sc

if trim(this.text)='' then
    dw_1.setfilter('')
    dw_1.filter()
else           
    ls_sc="lower(item_name) like '%"+lower(this.text)+"%'"
    dw_1.setfilter(ls_sc)
    dw_1.filter()
end if

PowerBuilder - Dynamically Disable/access controls on a Window

Venkatesh Jonnagadla — Wed, 05 Nov 2025 14:28:19 +0000

Create a global function and pass the window to the function to achieve the same. Or you can create a window function also to achieve the same

int li_counter
DataWindow               ldw_DataWindow
CommandButton                     lcb_CommandButton
StaticText                     lst_StaticText
singleLineEdit              ls_singleLineEdit
checkbox                     lcb_checkbox
picturebutton              lpb_picturebutton
editmask                     lem_editmask
dropdownlistbox                      lddlb_dropdownlistbox
userObject luo_userobject
radioButton     lrb_radiobutton
groupBox         lgb_groupBox


for li_counter = 1 to upperbound(control[] )  
            choose case TypeOf(this.control[li_counter])
                        case DataWindow!
                                    ldw_DataWindow = this.control[li_counter]
                                    ldw_DataWindow.enabled = false                  
                        case CommandButton!                                   
                                    if this.control[li_counter].classname() <> 'cb_cancel' then
                                                lcb_CommandButton = this.control[li_counter]
                                                lcb_CommandButton.enabled = false
                                    end if
                        case StaticText!
                                    lst_StaticText = this.control[li_counter]
                                    lst_StaticText.enabled = false
                                    lst_StaticText.textColor= 134217745 //Disabled Text Color
                        case singleLineEdit!
                                    ls_singleLineEdit = this.control[li_counter]
                                    ls_singleLineEdit.enabled = false
                        case checkbox!
                                    lcb_checkbox = this.control[li_counter]
                                    lcb_checkbox.enabled = false
                        case picturebutton!
                                    lpb_picturebutton = this.control[li_counter]
                                    lpb_picturebutton.enabled = false
                        case editmask!
                                    lem_editmask = this.control[li_counter]
                                    lem_editmask.enabled = false
                        case dropdownlistbox!
                                    lddlb_dropdownlistbox = this.control[li_counter]      
                                    lddlb_dropdownlistbox.enabled = false
                        case userobject!
                                    luo_userobject = this.control[li_counter]       
                                    luo_userobject.enabled = false
                        case radiobutton!
                                    lrb_radiobutton = this.control[li_counter]      
                                    lrb_radiobutton.enabled = false
                        //case groupbox!
                                    //lgb_groupBox = this.control[li_counter]      
                                    //lgb_groupBox.enabled = false
                                    //lgb_groupBox.textcolor = 134217745
            end choose      
next

Powerbuilder - Get windows login

Venkatesh Jonnagadla — Wed, 05 Nov 2025 12:26:35 +0000

Create Global External Function 

FUNCTION Boolean GetUserName ( &
    REF String lpBuffer, &
    REF UnsignedLong nSize &
) LIBRARY "ADVAPI32.DLL" ALIAS FOR "GetUserNameA;Ansii"

// This DLL is windows DLL and will be available in all windows machines

This should be in windows events

// Declare local variables
String ls_userName
UnsignedLong lul_size = 256 // Start with a sufficient buffer size
Boolean lb_rc

// Initialize the string buffer with spaces
ls_userName = Space(256)

// Call the Windows API function
lb_rc = GetUserName(ls_userName, lul_size)

// Check the return code and extract the user ID
If lb_rc Then  
    MessageBox('User ID', 'User ID is: ' + ls_userName)
Else
    // Handle error (e.g., the buffer was too small, although 256 is usually plenty)
    MessageBox('Error', 'Failed to retrieve Windows User ID.')
End If