A code sample for reference: Function
private void FindAndReplace(Microsoft.Office.Interop.Word.Application WordApp, object findtext, object replacewithText)
object matchcase = true;
object wrap = 1;
WordApp.Selection.Find.Execute(ref findtext,
WordApp.Selection.Find.Execute(ref findtext,
{
object matchwholeWord = true;
object matchWildcard = false;
object matchSoundsLike = false;
object nmatchAllWordforms = false;
object forword = true;
object format = false;
object matchKashida = false;
object matchAlefHamza = false;
object matchDiacritics = false;
object matchControl = false;
object read_only = false;
object visible = true;
object replace = 2;
WordApp.Selection.Find.Replacement.ClearFormatting();
WordApp.Selection.Find.ClearFormatting();
WordApp.Selection.Find.Wrap = Microsoft.Office.Interop.Word.WdFindWrap.wdFindContinue;
ref matchcase, ref matchwholeWord,
ref matchWildcard, ref matchSoundsLike,
ref nmatchAllWordforms, ref forword,
ref wrap, ref format, ref replacewithText,
ref replace, ref matchKashida,
ref matchDiacritics, ref matchAlefHamza,
ref matchControl);
}
Tuesday, November 20, 2012
DCOM-how can we convert word document into pdf by asp.net. I browse a ms-word file by FileUpload & when i click on OK, it automatically convert that file into PDF.
A code sample for reference:
object missing = System.Reflection.Missing.Value;
Microsoft.Office.Interop.Word.Application wordapp1 = new Microsoft.Office.Interop.Word.Application();
Microsoft.Office.Interop.Word._Document aDoc1 = null;
if (File.Exists((string)filepath))
{
DateTime toDay = DateTime.Now;
object readOnly = false;
object isVisible = true;
//set word not visible;
wordapp1.Visible = false;
//open word File
aDoc1 = wordapp1.Documents.Open(ref filepath, ref missing,
ref readOnly, ref missing, ref missing, ref missing,
ref missing, ref missing, ref missing, ref missing,
ref missing, ref isVisible, ref missing, ref missing,
ref missing, ref missing);
aDoc1.Activate();
//find the Word and Replace them with values
this.FindAndReplace(wordapp1, "@Name", Name);
}
object format = Microsoft.Office.Interop.Word.WdSaveFormat.wdFormatPDF;
aDoc1.SaveAs(ref SaveAs, ref format, ref missing, ref missing,
ref missing, ref missing, ref missing, ref missing,
ref missing, ref missing, ref missing, ref missing,
ref missing, ref missing, ref missing, ref missing);
object saveOption = Microsoft.Office.Interop.Word.WdSaveOptions.wdDoNotSaveChanges;
object originalFormat = Microsoft.Office.Interop.Word.WdOriginalFormat.wdOriginalDocumentFormat;
object routeDocument = false;
//aDoc1.Close(ref saveOption, ref originalFormat, ref routeDocument);
//wordapp1.Quit(ref missing, ref missing, ref missing);
((Microsoft.Office.Interop.Word._Document)aDoc1).Close(ref saveOption, ref originalFormat, ref routeDocument);
//aDoc1.Close(ref saveOption, ref originalFormat, ref routeDocument);
((_Application)wordapp1).Quit(ref missing, ref missing, ref missing);
object missing = System.Reflection.Missing.Value;
Microsoft.Office.Interop.Word.Application wordapp1 = new Microsoft.Office.Interop.Word.Application();
Microsoft.Office.Interop.Word._Document aDoc1 = null;
if (File.Exists((string)filepath))
{
DateTime toDay = DateTime.Now;
object readOnly = false;
object isVisible = true;
//set word not visible;
wordapp1.Visible = false;
//open word File
aDoc1 = wordapp1.Documents.Open(ref filepath, ref missing,
ref readOnly, ref missing, ref missing, ref missing,
ref missing, ref missing, ref missing, ref missing,
ref missing, ref isVisible, ref missing, ref missing,
ref missing, ref missing);
aDoc1.Activate();
//find the Word and Replace them with values
this.FindAndReplace(wordapp1, "@Name", Name);
}
object format = Microsoft.Office.Interop.Word.WdSaveFormat.wdFormatPDF;
aDoc1.SaveAs(ref SaveAs, ref format, ref missing, ref missing,
ref missing, ref missing, ref missing, ref missing,
ref missing, ref missing, ref missing, ref missing,
ref missing, ref missing, ref missing, ref missing);
object saveOption = Microsoft.Office.Interop.Word.WdSaveOptions.wdDoNotSaveChanges;
object originalFormat = Microsoft.Office.Interop.Word.WdOriginalFormat.wdOriginalDocumentFormat;
object routeDocument = false;
//aDoc1.Close(ref saveOption, ref originalFormat, ref routeDocument);
//wordapp1.Quit(ref missing, ref missing, ref missing);
((Microsoft.Office.Interop.Word._Document)aDoc1).Close(ref saveOption, ref originalFormat, ref routeDocument);
//aDoc1.Close(ref saveOption, ref originalFormat, ref routeDocument);
((_Application)wordapp1).Quit(ref missing, ref missing, ref missing);
Sending HTML formatted email in SQL Server using the SSIS Script Task
Sending HTML formatted email in SQL Server using the SSIS Script Task
I will start my discussion on using the Script Task to send email (both non-HTML and HTML formatted) with an example. First I will create a database table (MailsToBeSent) which will hold the information about the emails which are to be sent by the Script Task and then insert a few records in this table. Next I will create a stored procedure to retrieve the records from the above created tables.
So here is the script for creating these database objects.
Once you are done with the database object creation, let’s move on to create a package with Script Task to send emails.
Create a new project of Integration Services type in the Business Intelligence Development Studio. Drag a Script Task from the toolbox and drop it onto the Control Flow area of the Designer interface, right click on Script Task component and then select Edit, a screen similar to one as given below will come up. This is a screen shot from SSIS 2008. If you are still using SSIS 2005 it will say "Design Script..." instead of "Edit Script...".
Create a new project of Integration Services type in the Business Intelligence Development Studio. Drag a Script Task from the toolbox and drop it onto the Control Flow area of the Designer interface, right click on Script Task component and then select Edit, a screen similar to one as given below will come up. This is a screen shot from SSIS 2008. If you are still using SSIS 2005 it will say "Design Script..." instead of "Edit Script...".
On the Script page of “Script Task Editor” click on “Edit Script” button, it will bring up VSTA environment (On SSIS 2005 it’s VSA environment) for writing .Net code for sending emails.
Copy the code from the below tables and paste it in the VSA/VSTA code editor. There are two sets of code one for 2005 and one for 2008, so make sure you use the right version based on your version of Business Intelligence Development Studio, not the version of SQL Server you are connecting to. Because of changes in the scripting environment between SQL Server 2005 and 2008, there would is a slight change in the code that's why I am providing the below separate code to be used on SQL Server 2005 and on SQL Server 2008. If you are running the below code in your Script Task in SSIS 2005 environment, you may need to reference the System.XML.dll. By default it is included and referenced dll in SSIS 2008, so you would not have to worry if you are using it on SSIS 2008.
Here are the objects that are referenced with the System.XML highlighted below.
Please note you need to change the connection string in the code pointing to the server and database where you have created the above database objects and also change the SMTP server name which will be used to send emails. The two lines of code are as follows:
Also, I have commented out the section that allows you to send emails using authentication to your mail server. So if you want to use a user and password this can be supplied as well.
Script : VB .Net Code for Script Task for SQL Server 2005
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.Net
Imports System.Net.Mail
Imports System.Data.SqlClient
Imports System.Xml
Public Class ScriptMain
' The execution engine calls this method when the task executes.
' To access the object model, use the Dts object. Connections, variables, events,
' and logging features are available as static members of the Dts class.
' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
'
' To open Code and Text Editor Help, press F1.
' To open Object Browser, press Ctrl+Alt+J.
Enum ScriptResults
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
End Enum
Public Sub Main()
Dim mySmtpClient As SmtpClient
Dim ConnString As String
ConnString = "Data Source=ARALI-LAPTOP;Initial Catalog=Learning;Integrated Security=True;"
Try
Dim SqlQuery As String = "GetMailsToBeSent"
Using conn As New SqlConnection(ConnString)
conn.Open()
Dim comm As New SqlCommand(SqlQuery, conn)
comm.CommandType = CommandType.StoredProcedure
Dim adap As New SqlDataAdapter(comm)
Dim ds As New DataSet()
adap.Fill(ds)
If ds.Tables(0).Rows.Count > 0 Then
Dim intCount As Integer
Dim intCCCounter As Integer
Dim intBCCCounter As Integer
For intCount = 0 To ds.Tables(0).Rows.Count - 1
'Create an instance of MailMessage class and pass "From EmailID" and "To EmailID" to the constructor
'"To" list can accept multiple email address deliminated by comma
Using myMessage As New MailMessage(ds.Tables(0).Rows(intCount).Item("From").ToString(), Replace(ds.Tables(0).Rows(intCount).Item("To").ToString(), ";", ","))
'CC List
'MailMessage.CC property is a collection of type MailAddressCollection so you need to add one email address at a time in this collection as I am doing it in a loop for multiple address
'Also you can create a MailAddressCollection first, add all the CC email address to it and finally assign it to MailMessage.CC property
Dim CCAddressList As MailAddress
If ds.Tables(0).Rows(intCount).Item("Cc").ToString().Length > 0 Then
If ds.Tables(0).Rows(intCount).Item("Cc").ToString().IndexOf(";") > 0 OrElse ds.Tables(0).Rows(intCount).Item("Cc").ToString().IndexOf(",") > 0 Then
Dim strEmails As String()
strEmails = Split(Replace(ds.Tables(0).Rows(intCount).Item("Cc").ToString(), ";", ","), ",")
For intCCCounter = 0 To strEmails.Length - 1
CCAddressList = New MailAddress(strEmails(intCCCounter))
myMessage.CC.Add(CCAddressList)
Next
Else
CCAddressList = New MailAddress(Replace(ds.Tables(0).Rows(intCount).Item("Cc").ToString(), ";", ","))
myMessage.CC.Add(CCAddressList)
End If
End If
'BCC List
'MailMessage.BCC property is a collection of type MailAddressCollection so you need to add one email address at a time in this collection as I am doing it in a loop below for multiple address
'Also you can create a MailAddressCollection first, add all the BCC email address to it and finally assign it to MailMessage.BCC property
Dim BCCAddressList As MailAddress
If ds.Tables(0).Rows(intCount).Item("Bcc").ToString().Length > 0 Then
If ds.Tables(0).Rows(intCount).Item("Bcc").ToString().IndexOf(";") > 0 OrElse ds.Tables(0).Rows(intCount).Item("Bcc").ToString().IndexOf(",") > 0 Then
Dim strEmails As String()
strEmails = Split(Replace(ds.Tables(0).Rows(intCount).Item("Bcc").ToString(), ";", ","), ",")
For intBCCCounter = 0 To strEmails.Length - 1
BCCAddressList = New MailAddress(strEmails(intBCCCounter))
myMessage.Bcc.Add(BCCAddressList)
Next
Else
BCCAddressList = New MailAddress(Replace(ds.Tables(0).Rows(intCount).Item("Bcc").ToString(), ";", ","))
myMessage.Bcc.Add(BCCAddressList)
End If
End If
myMessage.Subject = ds.Tables(0).Rows(intCount).Item("Subject").ToString()
myMessage.Body = ds.Tables(0).Rows(intCount).Item("Body").ToString()
If ds.Tables(0).Rows(intCount).Item("IsHTMLFormat").ToString().ToUpper() = "TRUE" Then
myMessage.IsBodyHtml = True
Else
myMessage.IsBodyHtml = False
End If
If ds.Tables(0).Rows(intCount).Item("Priority").ToString().ToUpper() = "L" Then
myMessage.Priority = Mail.MailPriority.Low
ElseIf ds.Tables(0).Rows(intCount).Item("Priority").ToString().ToUpper() = "H" Then
myMessage.Priority = Mail.MailPriority.High
Else
myMessage.Priority = Mail.MailPriority.Normal
End If
'To be used for sending attachements
'myMessage.Attachments.Add(New Attachment("c:\example1.txt"))
'myMessage.Attachments.Add(New Attachment("c:\example2.txt"))
mySmtpClient = New SmtpClient("smtpserver")
'You can set the SMTP port number if it is not listening on default port
'mySmtpClient.Port = 26
'The credentials returned by DefaultNetworkCredentials represents the authentication credentials for the current security context in which the application is running.
mySmtpClient.Credentials = CredentialCache.DefaultNetworkCredentials
'If you don't want to use windows authentication to connect to your SMTP host and want to specify a different username and password, you can then use NetworkCredential class as shown below
'The NetworkCredential class is a base class that supplies credentials in password-based authentication schemes such as basic, digest, NTLM, and Kerberos.
'mySmtpClient.Credentials = New NetworkCredential("arshad", "abcd", "corpnet")
mySmtpClient.Send(myMessage)
End Using
Next
End If
conn.Close()
End Using
Catch E As Exception
Dts.Events.FireError(-1, "Failure in Script Task while sending mails.", E.Message.ToString(), "", 0)
Dts.TaskResult = ScriptResults.Failure
End Try
Dts.TaskResult = ScriptResults.Success
End Sub
End Class
Script : VB .Net Code for Script Task for SQL Server 2008
Imports System Imports System.Data Imports System.Math Imports Microsoft.SqlServer.Dts.Runtime Imports System.Net Imports System.Net.Mail Imports System.Data.SqlClient Imports System.Xml_ _ Partial Public Class ScriptMain Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase Enum ScriptResults Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure End Enum Public Sub Main() Dim mySmtpClient As SmtpClient Dim ConnString As String ConnString = "Data Source=ARALI-LAPTOP;Initial Catalog=Learning;Integrated Security=True;" Try Dim SqlQuery As String = "GetMailsToBeSent" Using conn As New SqlConnection(ConnString) conn.Open() Dim comm As New SqlCommand(SqlQuery, conn) comm.CommandType = CommandType.StoredProcedure Dim adap As New SqlDataAdapter(comm) Dim ds As New DataSet() adap.Fill(ds) If ds.Tables(0).Rows.Count > 0 Then Dim intCount As Integer Dim intCCCounter As Integer Dim intBCCCounter As Integer For intCount = 0 To ds.Tables(0).Rows.Count - 1 'Create an instance of MailMessage class and pass "From EmailID" and "To EmailID" to the constructor '"To" list can accept multiple email address deliminated by comma Using myMessage As New MailMessage(ds.Tables(0).Rows(intCount).Item("From").ToString(), Replace(ds.Tables(0).Rows(intCount).Item("To").ToString(), ";", ",")) 'CC List 'MailMessage.CC property is a collection of type MailAddressCollection so you need to add one email address at a time in this collection as I am doing it in a loop for multiple address 'Also you can create a MailAddressCollection first, add all the CC email address to it and finally assign it to MailMessage.CC property Dim CCAddressList As MailAddress If ds.Tables(0).Rows(intCount).Item("Cc").ToString().Length > 0 Then If ds.Tables(0).Rows(intCount).Item("Cc").ToString().IndexOf(";") > 0 OrElse ds.Tables(0).Rows(intCount).Item("Cc").ToString().IndexOf(",") > 0 Then Dim strEmails As String() strEmails = Split(Replace(ds.Tables(0).Rows(intCount).Item("Cc").ToString(), ";", ","), ",") For intCCCounter = 0 To strEmails.Length - 1 CCAddressList = New MailAddress(strEmails(intCCCounter)) myMessage.CC.Add(CCAddressList) Next Else CCAddressList = New MailAddress(Replace(ds.Tables(0).Rows(intCount).Item("Cc").ToString(), ";", ",")) myMessage.CC.Add(CCAddressList) End If End If 'BCC List 'MailMessage.BCC property is a collection of type MailAddressCollection so you need to add one email address at a time in this collection as I am doing it in a loop below for multiple address 'Also you can create a MailAddressCollection first, add all the BCC email address to it and finally assign it to MailMessage.BCC property Dim BCCAddressList As MailAddress If ds.Tables(0).Rows(intCount).Item("Bcc").ToString().Length > 0 Then If ds.Tables(0).Rows(intCount).Item("Bcc").ToString().IndexOf(";") > 0 OrElse ds.Tables(0).Rows(intCount).Item("Bcc").ToString().IndexOf(",") > 0 Then Dim strEmails As String() strEmails = Split(Replace(ds.Tables(0).Rows(intCount).Item("Bcc").ToString(), ";", ","), ",") For intBCCCounter = 0 To strEmails.Length - 1 BCCAddressList = New MailAddress(strEmails(intBCCCounter)) myMessage.Bcc.Add(BCCAddressList) Next Else BCCAddressList = New MailAddress(Replace(ds.Tables(0).Rows(intCount).Item("Bcc").ToString(), ";", ",")) myMessage.Bcc.Add(BCCAddressList) End If End If myMessage.Subject = ds.Tables(0).Rows(intCount).Item("Subject").ToString() myMessage.Body = ds.Tables(0).Rows(intCount).Item("Body").ToString() If ds.Tables(0).Rows(intCount).Item("IsHTMLFormat").ToString().ToUpper() = "TRUE" Then myMessage.IsBodyHtml = True Else myMessage.IsBodyHtml = False End If If ds.Tables(0).Rows(intCount).Item("Priority").ToString().ToUpper() = "L" Then myMessage.Priority = Mail.MailPriority.Low ElseIf ds.Tables(0).Rows(intCount).Item("Priority").ToString().ToUpper() = "H" Then myMessage.Priority = Mail.MailPriority.High Else myMessage.Priority = Mail.MailPriority.Normal End If 'To be used for sending attachements 'myMessage.Attachments.Add(New Attachment("c:\example1.txt")) 'myMessage.Attachments.Add(New Attachment("c:\example2.txt")) mySmtpClient = New SmtpClient("smtpserver") 'You can set the SMTP port number if it is not listening on default port 'mySmtpClient.Port = 26 'The credentials returned by DefaultNetworkCredentials represents the authentication credentials for the current security context in which the application is running. mySmtpClient.Credentials = CredentialCache.DefaultNetworkCredentials 'If you don't want to use windows authentication to connect to your SMTP host and want to specify a different username and password, you can then use NetworkCredential class as shown below 'The NetworkCredential class is a base class that supplies credentials in password-based authentication schemes such as basic, digest, NTLM, and Kerberos. 'mySmtpClient.Credentials = New NetworkCredential("arshad", "abcd", "corpnet") mySmtpClient.Send(myMessage) End Using Next End If conn.Close() End Using Catch E As Exception Dts.Events.FireError(-1, "Failure in Script Task while sending mails.", E.Message.ToString(), "", 0) Dts.TaskResult = ScriptResults.Failure End Try Dts.TaskResult = ScriptResults.Success End Sub End Class
You should make sure you properly dispose of the instance of MailMessage class especially if you are sending attachments with the email otherwise you will end up having your files locked by Windows OS and you will not be able to delete them. The easiest way to avoid overhead of disposing of the unused objects is to use the USING statement and write your code inside its block, similar to the way it has been done in the above code.
So now for the execution of the above created package, two emails are sent to the intended audience as shown below. As expected the first email (MailMessage.IsBodyHtml = False) is non-HTML email whereas the second email (MailMessage.IsBodyHtml = True) is HTML formatted, look at color in the message body.
- If you are running the above code in your Script Task in SSIS 2005 environment, you may need to make reference to System.Xml.dll. By default it is included and referenced dll in SSIS 2008, so you would not have to worry if you are using it on SSIS 2008.
- You need to reference System.Net.Mail namespace in your code which contains MailMessage and SmtpClient classes which are required for sending emails.
- There are three differences to note here between SSIS 2005 and SSIS 2008 in terms of the Script Task.
- Now you have two language options to write your code i.e. Visual Basic .Net and C# .Net in SSIS 2008 whereas you had only one language option in SSIS 2005.
- The scripting environment in SSIS 2008 is VSTA and it is VSA in SSIS 2005. It means almost full .Net capabilities in SSIS
- In SSIS 2008, in Script Task Editor Dialog box, the Script page has got preference over other pages and is the first page in list on the left side, it means it saves one more click to reach to your development environment to write your code.
Analysis Services Processing-ssis
Analysis Services Processing Task in SQL Server Integration Services SSIS
The Analysis Services Processing Task allows you to process dimensions, measure group partitions, and mining models in an SSIS package. While you can process all of these objects at one time, you can also select a subset of these objects to be processed as well. For example you may update certain dimension and fact tables in your data warehouse on a periodic basis by running an SSIS package. As a final step in the SSIS package, you would like to process just the dimensions and measure group partitions that use those data warehouse tables as their data source. The Analysis Services Processing Task allows you to do that.
In this tip we will walk through the steps to use the Analysis Services Processing Task in an SSIS package. We'll create a sample package that will process a dimension and a measure group partition in the Adventure Works DW Analysis Services database that comes with SQL Server 2005. Our hypothetical scenario is that we run an SSIS package to update the Product and Currency Rate tables in our data warehouse on a daily basis. We would like to add a step to the SSIS package to process the Product dimension and the Currency Rate fact table, thereby updating the information available in our SQL Server Analysis Services cube.
Create the Sample SSIS Package
To begin launch Business Intelligence Development Studio (BIDS) from the Microsoft SQL Server 2005 program group and create a new Integration Services project. An SSIS package named Package.dtsx will be created automatically and added to the project. Rename the package to SSASProcessingTask_Demo.dtsx then perform the following steps on the SSIS package:
Step 1: Add a Connection Manager for the SSAS server. Right click in the Connection Managers area and select New Analysis Services Connection from the context menu. Accept the defaults in the dialog to connect to the local SSAS Server (or edit as appropriate if you want to connect to an SSAS Server on another machine):
Step 2: Drag and drop the Analysis Services Processing Task from the Toolbox onto the Control Flow of the SSIS package. Edit the Analysis Services Processing Task; select the connection manager defined in step 1 above and click the Add button to select the objects to be processed:
The Process Options selected work as follows:
- Process Incremental on a measure group partition is used to load just new rows from the fact table. It requires additional settings which we will complete in the next step.
- Process Update for a dimension will update the dimension with any inserts, updates or deletes from the data warehouse.
Step 3: Click the Configure hyperlink in the Currency_Rates row shown in step 2 above. Since we have selected Process Incremental as the Process Option we need to either specify a table or view to load the new fact rows from or specify a query; we'll specify a query and assume that the stg_FactCurrencyRate table is populated with just the new fact rows to be added to the measure group partition.
- If you don't already have the AdventureWorks SSAS sample projects and databases available, you can download them here to get the starting point for this tip. Click the AdventureWorksBICI.msi link. Also click on the Release Notes link for the details on attaching the relational database. The default install location for the project is C:\Program Files\Microsoft SQL Server\90\Tools\Samples\AdventureWorks Analysis Services Project; you will see Enterprise and Standard folders. We used the project in the Enterprise folder.
- Take a look at the technical article Analysis Services 2005 Processing Architecture for an in-depth discussion of the processing options available for cubes, dimensions, and mining models.
Null -SSIS packages using scripting
NULL Defense to your SSIS packages using scripting
For the discussion of the problem in question, we will leverage the same package that we built in the first part of the NULL Defense Series.
In order to simulate the issue, create another table "NULLDefenseReplica" in the "TestDB" database. This table should have the same definition / structure as that of the NULLDefense table. Basically we are creating a destination table that we would use to insert records from the NULLDefense table which we are using as a source in our package.
Create a new ADO.Net connection to the "TestDB" database and name it MyADOConn.
Add a Script Transformation to the package, select the type as "Destination", and name it "Insert Records". Add the datapath from ValidRecords multicast output and join it to this Script Transform. Now configure the Script component as follows.
Rename the input stream from "Input 0" to "MyInputStream".
Select all the columns for the available input columns.
Add a connection on the connections page, select the ADO.NET Connection we just created and name it ADOTestDBConnection as shown below.
Now we need to configure this script transform to insert records into our NULLDefenseReplica table. I will not go into the details of explaining the entire code, but if you would like to learn more you can read about it on MSDN BOL. Add the code as can be seen in the below. You can download it from here.
Execute the package now and it should execute successfully and insert one record in the NULLDefenseReplica table.
Now for testing, remove the Script destination from the "Valid Records" multicast transformation and add the output from "Invalid Records" multicast to the input of the script transformation. Execute the package and you should encounter an error as shown in the picture below. The reason for this is what Microsoft states in MSDN BOL as "If the Script component contains a script that tries to read the value of a column that is NULL, the Script component fails when you run the package. We recommend that your script use the IsNull method to determine whether the column is NULL before trying to read the column value".
To build our NULL Defense for this problem there are two parts to this solution. One part is validating if the field in the row being processed contains a null value and the second part is assigning NULL or valid values to parameters based on values found in the fields. Edit the script and change the code as shown below and execute the package. The package should execute successfully.
So finally we have successfully built a NULL Defense for one of the most common scenarios in a Script transformation where NULL values in data can make your package fail.
NULL Management-SSIS packages
NULL defense for dates in SSIS packages
In order to create a test bed for our NULL Defense series, create a new database ( name it something like TestDB ) which we will use for simulating problems and developing solutions.
Create a new table called NULLDefense and populate it with data exactly as shown in the picture below. To keep the focus on the problem, we would keep the structure simple, but inclined towards the problem for which we will create our NULL Defense.
Now follow the steps below to create the SSIS package which will simulate the problem in question.
Create a new table called NULLDefense and populate it with data exactly as shown in the picture below. To keep the focus on the problem, we would keep the structure simple, but inclined towards the problem for which we will create our NULL Defense.
Now follow the steps below to create the SSIS package which will simulate the problem in question.
- Create a new SSIS Project
- Add a new package to it and name it "NULLDefense".
- Add a new Data Flow Task to the package.
- Add an OLE DB Source and name it "NULLDefense - TestDB".
- Edit the OLE DB Source and connect it to the table we just created.
- Add a Conditional Split transformation and name it "Split valid and invalid rec". The expected use of this transformation is to split records which contain dates and records which do not contain dates. Edit this transformation as shown in the picture below and also make sure you change the Default output name to "Valid Records" as shown below.
Since this is only for demonstration, we are not going to waste time inserting these records, so we will just use two Multicasts for the destinations to analyze the number of records going to each destination.
- Add a Multicast transform and name this "Invalid Records". This should be tied to the "Dates Unavailable" output from the Conditional Split.
- Add another Multicast transform and name this "Valid Records" and this should be tied to the "Valid Dates" output from the Conditional Split.
After you have completed these steps, your package should look something like the below picture.
Now if you take a look again at the data we have inserted into our table, we should have two records on the invalid output side and one record on the valid side as there are two records which do not have dates. The reason why we inserted a record with NULL and one record with '' is this is one of the scenarios that you will find in real life projects. Applications insert data into OLTP systems and many times application developers insert data using '' as a placeholder for no date instead of using a constant for NULL from the data access libraries (something like SQLDBNULL) which sends a NULL value to the database instead of a blank value. But from a user or business point of view any date that is not available is NULL, though blank and NULL might carry a different meaning and behavior from a technical standpoint.
Execute the package and you will find two records on the valid side and only one on the invalid side. You would find the same result even if you put a condition using the LEN function to measure the length of the value in the DOB field.
Go back to SSMS and view the data in the NULLDefense table we just created and you will find the reason for this. Whenever a blank value is entered inside a datetime field, when you run a query it will return a default value of "1900-01-01 00:00:00.000" as can be seen in the below picture.
We now move towards the solution for this.
I would not consider any options of making changes at the database level as we might not have the liberty of changing anything for our ETL solution as many other applications might be depending on the existing settings of the database.
Coming to the next alternative, we can place additional checks at the database level to ensure that the value is neither NULL nor the default value, but this kind of solution cannot be considered for a real-time solution as there might be numerous functions and conditions operating on this field and everywhere a dual check or replacement function would be required to fix this issue.
Finally, the option that I recommend is to bring uniformity in perception of NULL to our package and feeding only data that is either NULL or non-NULL to the package. This can be done through a very simple step at the database level itself, without changing any existing database settings, values or logic.
For our test package, just create a new "view" as shown in the picture below where we replace the default value with NULL.
Now change the connection of the source in our package to this view and execute the package. The results should now be as expected, which can be seen in the below figure.
Datetime is one special datatype which can trick you with NULLs and we just developed a defense against NULL date values.
Now if you take a look again at the data we have inserted into our table, we should have two records on the invalid output side and one record on the valid side as there are two records which do not have dates. The reason why we inserted a record with NULL and one record with '' is this is one of the scenarios that you will find in real life projects. Applications insert data into OLTP systems and many times application developers insert data using '' as a placeholder for no date instead of using a constant for NULL from the data access libraries (something like SQLDBNULL) which sends a NULL value to the database instead of a blank value. But from a user or business point of view any date that is not available is NULL, though blank and NULL might carry a different meaning and behavior from a technical standpoint.
Execute the package and you will find two records on the valid side and only one on the invalid side. You would find the same result even if you put a condition using the LEN function to measure the length of the value in the DOB field.
Go back to SSMS and view the data in the NULLDefense table we just created and you will find the reason for this. Whenever a blank value is entered inside a datetime field, when you run a query it will return a default value of "1900-01-01 00:00:00.000" as can be seen in the below picture.
We now move towards the solution for this.
I would not consider any options of making changes at the database level as we might not have the liberty of changing anything for our ETL solution as many other applications might be depending on the existing settings of the database.
Coming to the next alternative, we can place additional checks at the database level to ensure that the value is neither NULL nor the default value, but this kind of solution cannot be considered for a real-time solution as there might be numerous functions and conditions operating on this field and everywhere a dual check or replacement function would be required to fix this issue.
Finally, the option that I recommend is to bring uniformity in perception of NULL to our package and feeding only data that is either NULL or non-NULL to the package. This can be done through a very simple step at the database level itself, without changing any existing database settings, values or logic.
For our test package, just create a new "view" as shown in the picture below where we replace the default value with NULL.
Now change the connection of the source in our package to this view and execute the package. The results should now be as expected, which can be seen in the below figure.
Datetime is one special datatype which can trick you with NULLs and we just developed a defense against NULL date values.
Steps
- Try to size the efforts that you would need to patch this NULL or blank issue of dates if you would be fixing this in your existing SSIS packages.
- Implement this solution and compare the benefit and issues of implementing it at the database level instead of at the package level.
Thursday, January 26, 2012
Get the GridView control from the TextBox insides the GridView
1.You have one or more than one GridView(s) on your page.
2.Each GridView has one or more TextBox(s) in it.
3.At the TextChanged() event, you want to/have to know which GridView owns the event.
Here is the way to get the parent control of that TextBox:
Once you get the GridViewRow, you can get the RowIndex value. Once you get the GridView, you get everything...
enj.........................
2.Each GridView has one or more TextBox(s) in it.
3.At the TextChanged() event, you want to/have to know which GridView owns the event.
Here is the way to get the parent control of that TextBox:
1
2
3
| //get the parent gridviewGridViewRow gvr = (GridViewRow)(((TextBox)sender).Parent).Parent;GridView gv = (GridView)(gvr.Parent).Parent; |
Once you get the GridViewRow, you can get the RowIndex value. Once you get the GridView, you get everything...
enj.........................
Setting up the DotNetNuke Blog Module and Module Creation In DNN
Recently the members of the DotNetNuke blog team released a new version of the module, version 03.03.01. This article will walk you through this module and how to successfully configure it on a page within your DNN portal. This article assumes that you have downloaded and installed this version of the module in your website. This article also discusses creating a blog that is to be used only by the individual configuring the blog, so you will want to login as the blog owner before continuing with this tutorial. For this example we will be creating a blog for use by the “Admin” of the site.
Creating the Blog!
To get started we will add a new page and call it “Blog”, you complete this step by clicking the 
icon in the control panel. Input the following information for the blog page, or feel free to input your own specific information.
icon in the control panel. Input the following information for the blog page, or feel free to input your own specific information.
This creates a page that is visible to all users that will contain our blog. Now, we will want to add an instance of the blog to this newly created page. Select “Blog” from the list of available modules in the control panel and then select “Add”
Now right away you will notice that the blog module has now added a total of 5 new module displays to your page; most recent blog entries, blog list, new blog, search blog and blog archive. We will work with each of the controls individually to properly configure the blog for display.
Lets start with the “New Blog” module, this is an administrative module that is used to manage the blogs that are part of the module so we will first want to change the display properties of this module to disable viewing by all users. Hover over the action menu and select “Settings” to enter the module settings. In the permissions section, uncheck the “Inherit View Permissions from Page” and ensure that no values are selected, then click “Update”. The module should now show that it is only available for administrators.
This view control is the one that you will use to create and manage the properties for your blog and sub-blogs. Once you have the view listed above click on “Create My Blog” to start the configuration of the blog module. You will be presented with a “Create new Blog” page that will prompt you for various bits of information; each of these will be discussed below.
Title – This is the display title for your blog, it is displayed at the top of the entry listing and in the blog directory. For this example we will call our blog “Test Blog”
Description – This is a short description to the purposes of the blog. This content is displayed on RSS feeds and as general blog information.. For this example we will use the text “Demonstration blog description text”.
Blog Options – This section is a collection of checkboxes and looks like this, each setting will be discussed below.
Make this blog public – This option is used to make the blog visible to the public
Allow users to post comments – This allows registered users to post comments to your blog
Approval for user comments required – This option is only enabled for selection if user comments are allowed. If enabled this option will prevent any comments from displaying publicly until they are approved
Allow anonymous users to post comments – this allows anonymous users to post comments to your blog
Approval for anonymous comments required – Just like the rule for user comments this adds an approval processes to the anonymous comments.
Allow Trackback Comments – This option allows users of other blogs to link back to your blog inputting a comment automatically
Approval for trackback comments – Just like the other items this will enable an approval process flow
Trackback auto discovery – This is a mode that will allow trackbacks to be discovered automatically
Send mail notifications… - This option will send an e-mail notification to the blog owner after each blog comment and/or trackback
Use CAPTCHA for comments – This option will require the completion of a visual CAPTCHA before a comment can be submitted.
The final option in this section is the “When displaying your identity use” option. This allows you to use either your username or full name as the identifying name for blog postings.
Typically when configuring modules we would enable comments for registered users without approval and for anonymous users with approval. These will be the options that are selected for this tutorial.
Syndication Options – This collection of settings relates to RSS feeds and if/how they will be created. Each option will be described below.
Syndicate this blog – If this is enabled the content for this blog will be introduced into an RSS feed
Syndicate independently – If this is enabled the blog will be syndicated as its own separate RSS feed. You can use this with child blogs to prevent them from all being listed in 1 feed.
Managing Editor - This textbox is where you can put an e-mail address that should be listed as the managing editor for the RSS feed. This is something that should be populated with a valid e-mail address as it provides a method for feedback and questions from consumers of the RSS feed.
Date and Time Options – These options are used to control how the dates and times are displayed in the blog. This is where you select the time zone for your postings as well as your desired date format. Below is an example of the default setting for US Central time.
Once you have gone through these settings you can skip past the “Child Blogs” section as this section is outside the scope of this article. After clicking on “Update” your blog will be created.
Configuring your page layout (What modules are what?)
Now that we have created a blog it is a bit easier to see what each of these individual modules on our page are used for. Lets take a bit of time to organize and discuss each of these modules. The following steps will take you through a process to provide you meaningful information for each control as well as will give you instructions on how to rename the components to be more descriptive.
New_Blog
This control is the first control that we worked with an serves as an administration module, initially used to create the blog and eventually used to modify blog settings and to add new blog entries. Typically renaming this module to “Administration” is helpful as it makes it easy to remember what its purpose is. You can click in the module title to use the inline editor to change the name of the module.
It is also very common to move this module to the left pane to condense it’s display. The module display provides you with three options.
Blog Settings
Clicking on this link will return you to the blog settings page that we used above to create your blog; you can use this to administer the various configuration elements of your blog.
View My Blog
Clicking on this link is the same as clicking your specific blog form the “Blog_List” module; it simply updates the “Most recent blog entries” window to show your blog listing.
Add Blog Entry
This is where you can make a new post to your blog. You will be presented with an input screen that will provide you with fields for entry date, blog, title, summary, and blog. Each of these fields are fairly self explanatory you can use these fields to control the CONTENT of your blog post. You additionally have an “Entry Options” section where you can set options for your blog. These items exist to allow you to “publish” a blog, to allow comments, and/or to display a copyright notice at the bottom of your blog posting. Below is an example of these settings under their defaults
After you populate all values for the fields you may press “update” to actually make your new blog post! It is that easy!
Blog_Archive
This module is a simple calendar that shows days that have blog posts and allow users to navigate the blog based on a date range. Typically this module is renamed to “Archive” or something of that nature. This is another control that for both space and usability is typically moved to the “RightPane” to allow it to display alongside the blog postings. An example of the interface after modifying this control and the previous control is below.
As you can see we are starting to create something that looks more like a blog that you might expect to see.
Blog_List
This module is a control that allows users to select the various views from your blog. They are presented options for “View All Recent Entries” which will display recent entries from any and all blogs and child blogs. They are also given the ability to click on each individual blog and they will be displayed that information. This is another control that condenses display very nicely and is deserving of a new name and location. Typically this is found on the “LeftPane” of a site and is called “Navigation” or “Blogs” something a bit more descriptive to your users.
The action menu for this module provides you access to the “Blog Settings” page and “Add New Blog” pages. This makes the “Administration” module unneeded for administration purposes now that you have created your blog. Some individuals remove the module entirely but most just leave it for administration display.
Search Blog
This module is the search feature for the blog module itself allowing users to search for desired content, again this is a module that can condense to display in smaller areas and is typically located near the “Archive “ control as they are similar in function. For this example we will rename it to “Search” and place it directly below the “Archive” module.
This module has no configuration options and provides users basic searching of blog content.
Most Recent Blog Entries
This module is the actual display control for the module that will show your blog posts to your users and allow them to view/leave comments. The view of this specific module changes depending on how the other modules are configured. You can view summary information for you blog postings and then drill down into the full detail display.
This module does have a configuration element that is accessible via the action menu by selecting “Module Options”. These options apply to the blog module as a whole and will be discussed in two separate sections.
Basic Settings
These settings are basic settings that control the display of the blog and the input of content for the module. Below are the default settings for this module. They are all fairly self explanatory and are used to mostly limit the content to ensure that you have a proper display on your site for content length, search results length, and RSS feed length.
Advanced Settings
These settings are more advanced topics and relate to integration and other elements of the blog module. Each of these values will be discussed below.
Allow Upload Options – Can blog posters upload items to their posting?
Show Blog Summary – Is the summary displayed when viewing the single blog detail? Typically this is disabled as your summary is the first portion of your full blog post and it wouldn’t make sense to display it before the blog content.
Show unique title – If enabled this module will use a blog specific title to describe the content of the page. For SEO this is a very good practice!
Personal Blog Page – If you want to display 1 and ONLY 1 blog on the page you can select the blog that is associated with the module here. This essentially disables the function of the “Navigation”/”Blog_List” module discussed earlier and selects the given blog directly.
Enable DNN Search – This option is disabled by default. If selected the module will use the ISearchable interface to provide the core DNN search engine with information about your blog postings.
Summary
After walking through this example you should now have a blog page that looks something like the following.
This should give you a good overview of the core DNN blog module. The module is a very robust item and can be intimidating at first due to the overall complexity with 5 view controls and the dynamic configuration.
Enj............DNN Is Very Tool ....................................
Subscribe to:
Posts (Atom)