SSIS's BULK insert error msg

Problem

When you are trying Bulk insert from csv file, here is the error message you may run into:

“Could not bulk insert because file 'C:csv_filename.txt' could not be opened. Operating system error code 3(The system cannot find the path specified.).”

Solution

First of all, confirm that you are specifying the UNC (Universal Naming Convention) path and not just drive letters. If you are trying to create the file to a remote location then the path should follow UNC, i.e:

             \\Server_Name or IP_ADDRESS\Shared_Folder_PATH(Name)\FILE_NAME

Note that the path may be OK if you are trying to create the file on the SAME server running SQL Server.

Secondly, make sure that the SQL Server service account has permissions to SQL Server instance.
For doing this you can use a Domain user or, create a new local user and start the services with that account.
Go to the lower left corner of the desktop, click START, input “services.msc”, then choose “SQL Server” as shown below:

Check Log On AS, a new window will pop up.

The default setting is Local System account. Try changing it with a domain user account and restart SQL Server Service after new changes.

Last but not least, make sure that this account has Read and Write permissions on the folder where you are creating the file. To do so, right click on the folder --> sharing and security --> permissions.

That’s it! Now the error message should have been swept away.

Conversion between SSIS Integers and SQL Server Strings

When you carry out database migration, one of main headaches is the conversion of different data types between sources and destinations. If you are using SSIS, be careful about the conversion between strings and int. Sometimes SSIS will fail because the integer type defined is not big enough to hold the original data. It is also known as overflow problem. So here is a summary that indicates which kind of integer data types you should use for SSIS for strings in the source table. Hope it helps!

SSIS	SQL Server	Range	Maximum String Converted
DT_I1	tinyint	0 to 255	varchar(2)
DT_I2	smallint	-2^15 (-32,768) to 2^15-1 (32,767)	varchar(4)
DT_I4	int	-2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647)	varchar(9)
DT_I8	bigint	-2^63 (-9,223,372,036,854,775,808) to 2^63-1 (9,223,372,036,854,775,807)	varchar(18)

Note that for an empty string, you can use derived column task in SSIS with expression below to convert an empty string into a NULL integer in SSIS.

LEN(strCol) == 0 ? NULL(DT_I4) : (DT_I4)(RTRIM(strCol))

You can also refer to BOL: int, bigint, smallint, and tinyint (Transact-SQL)

Hidden Gem for Data Analysis: Data Profiling Task

Data analysis and cleansing are the most time consuming parts when you want to load source data, or prepare sampling data for data mining. You can write TSQL to find out distinct values or lengths of a column that are existing in the testing data. However, when you have millions of rows or more than thirty columns for analysis, TSQL scripts might take a whole day to get the answer. Since SQL Server 2008,  SSIS has Data Profiling task that can conveniently help analyze the content and structure of data, and uncover patterns, inconsistencies, anomalies and even redundancies. Today we will give a brief demonstration about this hidden gem for data analysis.

How to view the result by Data Profiler Viewer?

Data Profiler Viewer is used to view the generated profiler by data profiling task in SSIS. You create a new package and drag “Data Profiling Task” to the control flow pane as shown.

Double click the task, a new Data Profiling Task Editor window pops up and then click “Open Profile Viewer” button.

Click the open button at the upper left corner and locate the output profile file in xml format.

Types of profiles and statistics for analysis

Then you can explore the profile. Below only show four kinds of profiler but you can have eight kinds of profiles to request:

1. Column Length Distribution
2. Column Null Ratio
3. Column Pattern
4. Column Statistics including min, max, mean, standard deviation for each column.
5. Column Value Distribution including values, counts, percentages.
6. Functional Dependency: fully or partially dependent of other column to check redundancy.
7. Candidate Key: check which column might be a good candidate for a primary key or business key.
8. Value Inclusion: check whether all values in a column exists in another table. It can be dimensions or lookup table.
   

Demo

Let us take a look at the Column Value Distribution Profile first. Click each column on the top pane and then the lower pane will display Value, Count, Percentage for that column. You can sort by value, counts, or percentage by clicking the dropdown arrow at the right side of item. For this column, you can easily tell the top three kinds of type codes are M, P, R.

Note that it won’t be an exhausted list for all the values. Here is an example for drug code. It only listed three values in the lower pane but number of distinct values is 48720. So if the variation for column values is a lot but for some values, there are only a tiny percentage records, the profile won’t display those small percentage.

In summary, data profiles generated by Data Profiling Task of SSIS is very convenient to help you understand data, find patterns, derive data rules, detect the outliners of columns for data cleansing. You can even perform data profiling to test the foreign key relationship. If you would like to find out how to set up each kinds of profile requests, Jamie Thomson’s series of SSIS: Data Profiling Task are an awesome reference.

Reference: TechNet’s Data Profile Viewer

Decipher SSIS Error Codes: -1071607685

When using SSIS as tools to loading files, you usually can get a very clear error message that indicates what is going wrong. You can tell which column is wrong from ErrorColumn and for which reason the column brought failure from “ErrorCode – Description”.

However, when loading a source file that is not formatted as expected, if you have got an error output with "No Status is available" as ErrorCode and “0” as ErrorColumn as shown below, what do you feel?

Do you feel like lost in darkness? Somewhat …

Here is my recent experience in helping out troubleshooting file loading problem. Since there is no clue, all I can do is to check all constraints on all columns for potential trouble-makers.

Finally, it turned out that the trouble-makers is one of the obsolete columns that used to be NOT NULL, but no more input at the current loading. The solution is easy. You need to allow that column to have NULL values.

During the research for the clue, I uncovered two helpful resources to decipher SSIS error codes: 

• Integration Services Error and Message Reference
• header file dtsmsg.h

The header file dtsmsg.h is under the folder

C:\Program Files (x86)\Microsoft SQL Server\110\SDK\Include (for SQL Server 2012)

The two resources covered the five kinds of messages as shown. The online one is in table form and easier to follow, while the header file is more precise and detailed in technical terms.

So next time, hope you will feel more confident when you get SSIS error messages!

Debugging a Script Component in SSIS

As we have discussed how to debug a script task in SSIS, some readers asked “how to debug a script component in SSIS”. Before I wrap up my own examples, I highly suggest reading these two excellent articles:

1. Script Component Debugging in SSIS 2012

2. Breakpoint does not work within SSIS Script Component

You will learn three main methods to monitor a script component:

1. Display a modal message by using the MessageBox.Show.
2. Raise events for informational messages, warnings, and errors.
3. Log events or user-defined messages.

Also you will get an idea about the limitation of situations that you can debug a script component for current versions of SSIS. 

Debugging SSIS Script Tasks

SSIS Script tasks provide codes to implement customized functions that a built-in SSIS task cannot achieve directly. It is often necessary to debug through a Script task to ensure it works properly. You can set breakpoints for events such as OnPreExecute or OnPostExecute from ten break conditions as we discussed in Set Breakpoints for SSIS Debugging. Moreover, you can define stopping points in a script task through Microsoft Visual Studio Tools for Applications (VSTA).

Set a breakpoint in VSTA for a Script Task?

After you click “Edit Script” button in Script Task, a VSTA window will open.

Click the line you would like to set a breakpoint. Then right-click it and select Breakpoint –> Insert Breakpoint.

Below shows setting breakpoints in SQL Server 2008 R2.

Now you have the breakpoint set right before Messagebox.Show(). This means that the execution should break immediately before the message box is popped up.

SQL Server 2012

The way to set breakpoints in Script Tasks for SQL Server 2012 is similar as shown.

Also, after clicking Insert Breakpoint, a popup window will let you to do more fine tunings for your breakpoint through locations of Line and Character.

Note that SQL Server 2012 has one additional option: Insert Tracepoint besides “Insert Breakpoint”.

 

How breakpoints work?

After you set up the breakpoint, you can close Script Task Editor window. Right Click the Script Task and then select Execute Task. You would expect a yellow arrow displayed at the breakpoint.

After you press F10 button, a message box pops up with the message “Hello World”. After you close the popup window, the VSTA window should appear like this.

Tweaks for SQL Server 2008

If you use SQL Server 2012, you are lucky to get the breakpoint hit as described above.

If you use SQL Server 2008, you need some tweaks to make breakpoints working as expected:

Step 1: Set SSIS runtime mode as 32 bit at PROJECT Level.

Step 2: Re-open Script Task Editor window and save it again. In this way, your script task code is set to be compiled as 32 bit.

 

Shortcut Keys for Debugging

You can use debugger shortcut keys to speed up debugging. SSIS Script tasks share the same set of shortcut keys for Visual Studio. Here is a list of some common shortcut keys.

Keys	Functions
F5	Run the application.
F10	Step Over (Execute the next line of codes but not follow execution through any function calls).
F11	Step Into.
SHIFT+F11	Step Out.
CTRL+BREAK	Stop execution (Break).
F9	Toggle breakpoints.

Now I hope that you have a solid understanding of how to debug script tasks in SSIS. Take time to play with it and you will find it save the day for you in troubleshooting. If you have any questions, please feel free to leave a comment.

Reference:

1. Debug a Script by Setting Breakpoints in a Script Task and Script Component
2. Debugger Shortcut Keys for Visual Studio

Set Breakpoints for SSIS Debugging

Like debugging codes for other languages or tools, it is often necessary to set breakpoints to pause execution so you can examine variable values where you think the problem can be. SSIS provides a very straightforward GUI to help you set breakpoints in SSIS packages.

Where to set breakpoints?

You can set breakpoints on a task or a container. A task can be Execute SQL Task, data flow tasks, script tasks, etc. A container can be a For Loop container, a Foreach Loop container, or a Sequence container.

Moreover, there are eleven break conditions that you can choose from as shown below

These break conditions are defined as:

1. OnPreExecute: Called by a task or a container immediately before it runs.
2. OnPostExecute: Called by a task or a container immediately after it runs.
3. OnError: Called by a task or container when an error occurs.
4. OnWarning: Called when the task is in a state that does not justify an error, but does warrant a warning.
5. OnInformation: Called when the task is required to provide information.
6. OnTaskFailed: Called by the task host when it fails.
7. OnProgress: Called when there is measurable progress about task execution.
8. OnQueryCancel: Called at any time in task processing when a cancel execution is fired.
9. OnVariableValueChanged: Called when the value of a variable changes. The RaiseChangeEvent of the variable must be set to true to raise this event.
10. OnCustomEvent: Called by a custom task-defined events.
11. Loop iterations: Called when the iteration condition in a loop is satisfied. This only appears for a For Loop container and a Foreach Loop container.

Moreover, there are four Hit Count types you can define:

• Always
• Hit count equals
• Hit count greater than or equal to
• Hit count multiple

After you define a Hit Count Type, you can specify a Hit Count at which the breakpoint executes. This is especially useful when you want to skip some iterations and break at some specific iterations.

How to set breakpoints?

In SSIS designer, navigate to the control flow panel.  Right-click the object where you want to set the breakpoint and then click the Edit Breakpoints option. You will see a Set Breakpoints window popup just like the picture shown above.

Next, select break conditions you like to have. Here you can combine multiple break conditions. For example, you can let it break at both OnPreExecute and OnPostExecute events so that you can examine the changes on variables. The default Hit Count Type is grayed out when the related break condition is unchecked. Once a break condition is selected, you can go further to define Hit Count Type and Hit Count. Below is a breakpoint set at a loop when its loop iteration is equal or larger than 2.

After you close the Set Breakpoints window, you will notice a red dot appears on the object with breakpoints.

Furthermore, setting breakpoints in SSIS Script tasks is a little different from the way shown above. If you would like to know more, please stayed tuned.

How to modify breakpoints?

You can modify the breakpoint in the same way you set breakpoints. Right-click the object and then click the Edit Breakpoints option.

Now you have a powerful tool to help your troubleshooting effectively. With breakpoints, you can step through your ETL package to keep track of your variables and status of packages.

How to: Run SSIS in 32 bit Mode

SSIS is an excellent tools for extract, transform, and loading data. Nowadays, more and more machines that SQL Server runs on are 64 bit. However, there are some conditions that require SSIS to be run in 32 bit mode, such as

1. when SSIS tries to interact with Microsoft Excel or Access files that only support 32 bit.

2. ActiveX script task or Execute DTS 2000 package task.

3. Debug script tasks.

Sometimes if it is not set in a right runtime mode, you will get error messages like:

Error: 0xC00F9304 at Package, Connection manager "Excel Connection Manager": SSIS Error Code DTS_E_OLEDB_EXCEL_NOT_SUPPORTED: The Excel Connection Manager is not supported in the 64-bit version of SSIS, as no OLE DB provider is available.

Or even without error messages when you try to get your script task pause at a breakpoint inside the script. It seems never hit the breakpoint and does not pause as expected.

How to set 32bit runtime mode at PROJECT Level?

After the project is opened in BIDS, right-click the project on the Solution Explorer panel and then click Properties.

Then on the Property Pages, first select Debugging under “Configuration Properties” on the left. Then change the property for Run64BitRuntime to be FALSE. (The default value for Run64BitRuntime is True.)

 

Now every package under this Demo Debug Project is set to run in 32 bit.

How to set 32bit runtime mode at PACKAGE Level?

To set 32bit runtime mode at PACKAGE Level means that you can run just one package in 32 bit. This can be accomplished through SQL Agent job settings.

It is required to have X86 DTExec.exe installed in order to have SQL Agent job to execute in 32 bit mode. 32 bit version of DTExec.exe is often installed at

C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\DTExec.exe (for SQL Server 2008)

C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\DTExec.exe (for SQL Server 2012)

As shown below, when you schedule jobs, you can set the Execution Options in job steps to “Use 32 bit runtime”.

If you execute the package using DTEXEC via command-line scheduling process, you need to specify the 32-bit version of DTEXEC by explicitly running the DTEXEC.EXE from the right folder as shown:

"C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\DTExec.exe" /f C:\PackageFolder\Package.dtsx /Conf C:\ConfigFile.dtsConfig

How to: Query BIT Data Type in SQL Server and SSIS

If you have ever met problems when trying to export BIT data type columns to files or use them in SSIS, here are the tips for you.

The bit data type is an integer data type that can take a value of 1, 0, or NULL. Thus it is often used to represent Boolean type values such as Yes/No, True/False, and On/Off. However, BIT columns might be interpreted differently by different providers. When you query BIT columns in SSMS, it displays as 1 and 0 as its value. However, when you either query BIT columns in SSIS or export them to text files, it won’t be 1 or 0 any more. Below will show you some tricks to handle BIT columns.

How SSIS handle BIT columns?

In SSIS, BIT columns are interpreted as Boolean [DT_BOOL]. How to tell this?

You need right click the OLE DB Source component and choose “Show Advanced Editor”. Then click the “Input and Output Properties” Tab. Expand “Output Columns” under OLE DB Source Output. You will notice that the BIT column is Boolean type as shown below. Here column IsSHP is the BIT data type column.

So when you need to query BIT column, you need to directly use TRUE or FALSE. Below shows that how you can query BIT columns in the conditional split task.

Note that there is no quote around true in the expression.

How to export BIT columns to files as integers in SSIS?

Solution 1: Using Derived Columns

Or if you want to store it with less storage, you can use DT_UI1 data type.

IsSHP_bit = [IsSHP] ? (DT_UI1)1 : (DT_UI1)0

Solution 2: Tweak OLE DB Source

First of all, you need to convert BIT columns in OLE DB connection manager from OLE DB Source Editor,

select convert(int, IsSHP) as IsSHP

Then change the DataType in the OLE DB Source to integer type DT_I4 as shown below.

Now you will get 1 or 0 as output for BIT columns.

That’s it and hope it will help!

SSIS Validation Status “VS_NEEDSNEWMETADATA”

Problem                                                

Here is the error message:                                                   

Error on Component:[DFT_xxx]: "component "OLE DB Destination" (2327)" failed validation and returned validation status "VS_NEEDSNEWMETADATA".

It looks like some data mapping for columns in the SSIS package were lost after being deployed to the production. Both dev and production have the same number of columns. Also the name looks the same!

Solution

Check the columns to see whether they have the same case in the production as in the development. SSIS is CASE sensitive and it cannot automatically recognize those columns if the only difference is the case.

How to update column name?

You need to use sp_RENAME to update column name as below.

sp_RENAME '[dbo].[tableName].columnname', 'ColumnName' , 'COLUMN'
GO

Hope it will help you debug faster!

Script Task: Find the Day of Week in SSIS

Problem

How to find out the day of week in SSIS to achieve the same result as DATENAME( dw, datecolumn ) as in TSQL? For example, we need to find out “2013-11-24” is Sunday or Saturday.

Solution

Suppose FROM_SERVICE_DATE is the date column to derive the info about day of week. Below are two options. Using “Script Task” seems more complicate than directly using “Derived Column”. But it is more readable, isn’t it? Moreover, from this example, you can learn to use “Script Task” to handle multiple CASE situations.

Using “Derived Column”

Below is the expression to input in “Derived Column” task by using nested Conditional Operator in SSIS.

DATEPART("dw",FROM_SERVICE_DATE)==1?"Sunday":DATEPART("dw",FROM_SERVICE_DATE)==2?"Monday":DATEPART("dw",FROM_SERVICE_DATE)==3?"Tuesday":DATEPART("dw",FROM_SERVICE_DATE)==4?"Wednesday":DATEPART("dw",FROM_SERVICE_DATE)==5?"Thursday":DATEPART("dw",FROM_SERVICE_DATE)==6?"Friday":DATEPART("dw",FROM_SERVICE_DATE)==7?"Saturday":""
 
Using “Script Task”

Step 1: Derive a new column with "dayNoOfWeek” as Name for the numeric number of day of week, such as 1st or 6th of the week.

Step 2: Drag and drop Script Component task to the Data Flow Task (DFT) pane, you will be prompted to select script component type as shown below. Check “Transformation”. Then connect it with the above Derived Column Task.

Step 3: Under “Input Column” page, check “dayNoOfWeek” as shown below.

Step 4:
Under “Inputs and Outputs” page, expand “Output0” and select “Output Columns”. Click “Add Column” button. A new column will appear. You can rename it to “NameOfWeekDay”.
Next, modify the column properties on the right. Set the new column to be a string with length of 10.

Now go back to “Script” page on the top, select the “ScriptLanguage” to be C# related. Then click “Edit Script” button at the low right corner.

Enter the following codes in the Sub function Input0_ProcessInputRow.

public override void Input0_ProcessInputRow(Input0Buffer Row)
    {

        switch (Row.dayNoOfWeek) {             
            case 1: Row.NameOfWeekDay = "Sunday";       break;
            case 2: Row.NameOfWeekDay = "Monday";       break;
            case 3: Row.NameOfWeekDay = "Tuesday";      break;
            case 4: Row.NameOfWeekDay = "Wednesday";    break;
            case 5: Row.NameOfWeekDay = "Thursday";     break;
            case 6: Row.NameOfWeekDay = "Friday";       break;
            case 7: Row.NameOfWeekDay = "Saturday";     break;
        }
   }

If you prefer to Visual Basic as your script language, add the following code in the sub function of Input0_ProcessInputRow.       

SELECT CASE Row.dayNoOfWeek
            Case 1
                Row.NameOfWeekDay = "Sunday"
            Case 2
                Row.NameOfWeekDay = "Monday"
            Case 3
                Row.NameOfWeekDay = "Tuesday"
            Case 4
                Row.NameOfWeekDay = "Wednesday"
            Case 5
                Row.NameOfWeekDay = "Thursday"
            Case 6
                Row.NameOfWeekDay = "Friday"
            Case 7
                Row.NameOfWeekDay = "Saturday"
END SELECT
Next, build and close the Script Edit window.  

How to: Calculate Current Age of Members

Age is an important measure in healthcare. We need to calculate the right age to differentiate aged or non-eldly enrolled members. Moreover Age is an essential part of conditions in HEDIS measures. However, it is very easy to get it skewed by birthday or some special case, such as leap years. Here you can find out the solutions by using TSQL or SSIS.

Before we go to the right answers, what do you think of the following two solutions?

• DATEDIFF(“YYYY”, BirthDate, GETDATE())
• DATEDIFF(“DD”,BirthDate, GETDATE()) / 365

As the first glance, they may look right. Now suppose one child was born at 2012-11-15, when you try to use the following TSQL to get his age

select DATEDIFF(YY, '2012-11-15',  '2013-10-15')

You will get 1 as the result. The year has been rounding to one although the first birthday has not arrived yet.

TSQL Solution 

In the case that you need to derive a column for the members' age, use the following:

Case
    when (MONTH([MEMBER_BIRTH_DATE]) > MONTH(CURRENT_TIMESTAMP))
                  or ((MONTH([MEMBER_BIRTH_DATE]) = MONTH(CURRENT_TIMESTAMP)) and (DAY([MEMBER_BIRTH_DATE]) > DAY(CURRENT_TIMESTAMP)))
                  then DATEDIFF(YY, [MEMBER_BIRTH_DATE], CURRENT_TIMESTAMP) - 1
                else DATEDIFF(YY, [MEMBER_BIRTH_DATE], CURRENT_TIMESTAMP)
           end MEMBER_AGE

SSIS  Solution

SSIS expression for derived column task is:

DATEDIFF("Year",MEMBER_BIRTH_DATE,GETDATE()) - ((MONTH(MEMBER_BIRTH_DATE) == MONTH(GETDATE()) && DAY(MEMBER_BIRTH_DATE) > DAY(GETDATE())) || MONTH(MEMBER_BIRTH_DATE) > MONTH(GETDATE()) ? 1 : 0)

Isn't it easy?

How to: Load DATE Type Data by SSIS

We have discussed Implicit conversion from varchar to datetime by SQL Server, today I will show you how to take advantage of this kind of implicit conversion when loading date type data using SSIS. 

Problem

Every business needs time dimension to measure its profit or growth. Date-related columns can be birth date of persons, order dates or ship dates of products, prescription date, etc. However, date-related columns can be tricky to load because there seems always some data issues with them. For example, suppose we have a source file with PRESCRIPTION_DATE column in format of DD-MMM-YYYY  in source files. Some values are just empty or set as a dot (that might come from SAS) while the destination table defines this column as date type or datetime type.

How to load date-type columns?

Step 1. Define all date-related columns as VARCHAR in flat file connection manager and load all data  as VARCHAR first in Flat File Source.

Step 2.  Use derived column to clean up those dot values or other issues and to "transform" the data.

	Solution 1:	Define a new derived column [Conv_DateColumn] with expression set as below:  LEN(TRIM(PRESCRIPTION_DATE)) < 2 ? NULL(DT_DBDATE) : (DT_DBDATE)(SUBSTRING(PRESCRIPTION_DATE,1,2) + "-" + SUBSTRING(PRESCRIPTION_DATE,3,3) + "-" + SUBSTRING(PRESCRIPTION_DATE,6,4))
	Solution 2:	Define a new derived column [Conv_DateColumn] as a (DT_STR,10,1252), in format of "YYYY-MM-DD".

             

Step 3. When you do Mappings for OLE DB Destination Editor, map the [Conv_DateColumn] to the dateColumn with date or datetime as column type.

That's it!

Implicit Conversion From Varchar to Datetime

Sometimes a simple little thing may bring us some sights. Today I'd like to share with you something related to implicit conversion in sql server.

Problem

A colleague brought me this scenario: when she tried to update some table by joining with other tables, She got the following error:

Msg 242, Level 16, State 3, Line 1
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
The statement has been terminated.

So the action to work around this is to change data type of the related column from varchar(10) to datetime by just using ALTER COLUMN function. However, another error jumped out:

Msg 241, Level 16, State 1, Line 1

Conversion failed when converting date and/or time from character string.

The statement has been terminated.

What? This is kind of common practices for me as well. What did the error come from?

So I went ahead to dig into the date column that was defined as varchar(10). The moment I saw the data, I got some hint. In that column, the date was displayed as "DD-MM-YYYY". 

Solution

After changing the format from "DD-MM-YYYY" to "YYYY-MM-DD" by the following script:

UPDATE schemaName.tableName

SET datecolumn = SUBSTRING(datecolumn,7,4)+'-'+ SUBSTRING(datecolumn,4,2)+'-' +SUBSTRING(datecolumn,1,2)

there is no more problem to use ALTER COLUMN to update that column's data type from Varchar to Datetime. 

Conclusion

 As a summary, SQL Server only allows implicit conversion from Varchar to Datetime when the date format is "YYYY-MM-DD". If you want to learn more about implicit/explicit data conversion, please read technet's article: Data Type Conversion.

How to: Send Emails by Script Task in SSIS

Sending emails are common tasks in control flow of SSIS packages. We need to inform users when packages run completely or fail. Script Task in SSIS provides a more simple but more flexible approach than the 'Send Mail Task'. Using 'Script Task', we can customize our sending email tasks. That is to say, with 'Script Task', we can configure senders, recipients, subjects, and bodies of an e-mail message. OK, let's get started!

First, you need to add the following for the header of your script.

Imports Microsoft.SqlServer.Dts.Runtime

Imports System.Net.Mail

Imports System.Net

Then, define variables:

Dim myHtmlMessage As MailMessage

Dim mySmtpClient As SmtpClient

The sender, recipient, subject, and body of an e-mail message may be specified as parameters when a MailMessage is used to initialize a MailMessage object.

Below is the whole script body:

Imports System
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.Net.Mail
Imports System.Net

Public Class ScriptMain
  Public Sub Main()
    Dim myHtmlMessage As MailMessage
    Dim mySmtpClient As SmtpClient

    myHtmlMessage =
    New MailMessage(sender, recipient, "Subject", "body1." + vbCrLf + vbCrLf + "body2:" + Dts.Variables("System::MachineName").Value.ToString + vbCrLf + "Execution by:" + Dts.Variables("System::UserName").Value.ToString + vbCrLf + "Package StartTime: " + Dts.Variables("System::StartTime").Value.ToString + vbCrLf + vbCrLf + strSuccessMessage)

   mySmtpClient =
    New SmtpClient(Dts.Variables("cvarSMTPServer").Value.ToString())
  mySmtpClient.Send(myHtmlMessage)

  Dts.TaskResult = Dts.Results.Success
  End Sub
End Class

Now you can send emails by executing the task!

More freebies to addon

If you would like to achieve even more, keep reading.

1. Attachments 
   
   In order to add attachment to emails, you need use    myHtmlMessage..Attachments.Add(New Attachment("c:\textfile1.txt"))
   

     

2. Users and Password
   
   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
   
    mySmtpClient.Credentials = New NetworkCredential(UserName, Password, Domain)

Stairway to Integration Service: Script Task

Another effective way to make SSIS packages dynamic is to write custom code that perform tasks you cannot perform with the built-in components. Here is a cookbook/cheatsheet that will speed up your learning of customized script tasks or components.

There are two important features you need to determine before you click "Edit Scripts" button. First of all, you need to add existing variables to the ReadOnlyVariables and ReadWriteVariables lists in the Script Transformation Editor or Script Task Editor to make them available to the custom script. Secondly, you need to determine which languages you would like to use: C# or Visual Basic.
Besides these, you need to add necessary libraries at the beginning of scripts. You can refer the article of Adding the Script Task to Your SSIS Packages for detailed steps.

Note that the following examples are all for 2008 version. This is not an exhaustive list. Please stay tuned for more tasks accomplished by Script Task in SSIS:

• How to: Send Email by Script Task in SSIS  
• How to: Import HL7 Messages by Script Task in SSIS 
• How to: Convert Weekday Number into Weekday Names by Script Task in SSIS

Problems	Expression
Get files' name from readable variables into String sfilePathname.	Solution 1: Dim sfilePathname As String sfilePathname = Dts.Variables("vFilePathName").Value.ToString Solution 2: Dim sfilePathname As String = Dts.Variables("vFilePathName").Value
Get the date when the file was created.	Dts.Variables("vFileDate").Value = File.GetLastWriteTime(sfilePathname)
Get dateStamp from filename if it contains date info at last 6 strings.	Dim dateStamp As String = sfilePathname.Substring(sfilePathname.LastIndexOf(("_")) + 1, 6)
Output value of dateStamp to ReadWriteVariables vYrMon.	Dts.Variables("vYrMon").Value = CInt(dateStamp)
Get substring of dateStamp.	Dim sYear As String = dateStamp.Substring(0, 4)
Get the system information from system variables.	strSystemErrorEmailFrom = Trim(Dts.Variables("System::MachineName").Value.ToString())
Send SMTP mails.	// Add "Imports System.Net.Mail" at the top Dim myHtmlMessage As MailMessag  Dim mySmtpClient As SmtpClient myHtmlMessage=New MailMessage(strSystemErrorEmailFrom, strSystemErrorEmailTo, "Subject", "body")     mySmtpClient =  New SmtpClient( Dts.Variables("cvarSMTPServer").Value.ToString() )     mySmtpClient.Credentials = CredentialCache.DefaultNetworkCredentials     mySmtpClient.Send(myHtmlMessage)     Dts.TaskResult = Dts.Results.Success
Convert text to proper case (ex. 1st letter in each word is uppercase)	Row.OutputName = StrConv(Row.InputName, VBStrConv.ProperCase)
Build Event Handler for OnError Event.	Public Sub Main()         Dim arrErrorMessages As Collections.ArrayList         Try             arrErrorMessages = CType(Dts.Variables("objErrorMessages").Value, Collections.ArrayList)         Catch ex As Exception             arrErrorMessages = New Collections.ArrayList()         End Try         arrErrorMessages.Add("Error on Component:[" + Dts.Variables("SourceName").Value.ToString() + "]: " + Dts.Variables("ErrorDescription").Value.ToString())         Dts.Variables("objErrorMessages").Value = arrErrorMessages         Dts.TaskResult = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success End Sub
Get the filename from a full file path.	Solution 1: Dim fileName As String = "C:\mydir\test.txt" Dim result As String = Path.GetFileName(fileName) Console.WriteLine("GetFileName('{0}') returns '{1}'", fileName, result) Solution 2: RIGHT( @[User::FileName], FINDSTRING( REVERSE( @[User::FileName] ), "\\", 1 ) - 1 ) //Results: GetFileName('C:\mydir\test.txt') returns 'test.txt'
Dynamically pre-truncate table.	sSQLScript = "DELETE FROM " + sTableName + vbCrLf + " WHERE " + sPKName + " > 0" Dts.Variables("SQLScript1").Value = sSQLScript
Determine a varible's value depending on a file name	If strFileName.Contains("BROUGHT").ToString Then         intFacilityId = "1" Else         intFacilityId = "0" End If
Get the filename without extension from a full file path.	Dts.Variables("vFileName").Value = Path.GetFileNameWithoutExtension(Dts.Variables("vFilePath").Value.ToString)
Split strings that delimited by "|".	Dim sAry AsString() = col1.Split("|")
Check file exists or not.	Solution 1: Dim directory As DirectoryInfo = New DirectoryInfo("c:\") Dim file As FileInfo() = directory.GetFiles("*.txt") If file.Length > 0 Then    Dts.Variables("User::FileExists").Value = True Else    Dts.Variables("User::FileExists").Value = False End If Solution 2: If (System.IO.File.Exists("C:\mydir\test.txt")) Then       Dts.TaskResult = ScriptResults.Success     Else       Dts.TaskResult = ScriptResults.Failure End If

How to: Extract the Name from a Combination of Name and Title

Data transformation often requires to extract info/substring set from part of strings. If this substring set is a fixed length, it is easy to implement by functions of Substring in SSIS. However, when the substring set is in various length, you have to find out the rule first.

For example, if a column NameTitle is a combination of name and title, we want to separate name and title. Suppose that all members has only one string as titles, i.e., we have NameTitle = "Andrew S MD", how to separate "Andrew S" and "MD"?

SSIS expressions makes it easy to implement that kind of data transformation. For introduction of SSIS expressions, you can read Stairway to Integration Service: SSIS Expressions. Now go ahead to drag a Data Flow Task and get the source column. In Derived Column Transformation, define a column that "Add as a New Column", then follow the steps below to get its SSIS expression:

1. Use LEN and REPLACE functions to find out the numbers of spaces NameTitle column contains. Replace all spaces with empty strings first. Then substract the length of original strings with the modified one. Pay attention to match parentheses in same colors.
   LEN([NAMETITLE])-LEN(Replace([NAMETITLE], " ", ""))
2. Determine the location of the space right before titles (or space that separates name and title) by FINDSTRING function.
   FINDSTRING( [NAMETITLE], " ", LEN([NAMETITLE])-LEN(Replace([NAMETITLE], " ", "")) )
3. Use the SUBSTRING function to retrieve names from NameTitle.
   SUBSTRING( [NAMETITLE], 1, FINDSTRING( [NAMETITLE], " ", LEN([NAMETITLE])-LEN(Replace([NAMETITLE], " ", "")) )-1 )

BTW, in Excel, you can use LEFT(A2, LEN(A2)- LEN(RIGHT(A2, LEN(A2)-SEARCH("@", SUBSTITUTE(A2, " ", "@", LEN(A2)_LEN(SUBSTITUTE(A2, " ", ""))))))-1) to extract names from the combination, if A2 is the location of NameTitle.

Troubleshooting Error Messages in SSIS

Troubleshooting is a necessary lesson during SSIS packages' development. I will jot down some error messages during SSIS developments and the way to work around them. Hope that it will help you find solutions faster. 

The rule of thumb of troubleshooting is to identify problematic components first and then find out error types. Breaking big problems into small pieces first and then QA for each small pieces. 

Most of times, error messages are clear. For example, when it says "The file xxx does not exist", you know it is time to check the file's existence. However, sometimes there are so many error messages automatically generated by SSIS that you may feel overwhelmed. You don't know where to start.  

Here common error messages are listed and when you are familiar with these, you definitely will find it much easier to locate the key info: the cause of problem. These key info in the following error messages as denoted in red.

1. Msg: An error occurred while attempting to perform a type cast. or The data conversion for column "DATECOLUMN" returned status value 2 and status text "The value could not be converted because of a potential loss of data."
• Solution: You need to locate the error component. If this is related to a type cast in Derived Column Transform, you need to check whether it is a legal type cast first. You can refer to the diagram for legal data conversion. If you still have no clue about errors, check for dates or SUBSTRING related issues such as whether the length and start_point are set right.
 
2. Msg: Data conversion failed. The data conversion for column "COLUMN1" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page."
• Solution: Check whether you have got truncation issue and whether the COLUMN1 in your source file are wider than expected.
 
3. Msg: There was an error with input column "COLUMN1" (1071) on input "OLE DB Destination Input" (773). The column status returned was: "The value violated the integrity constraints for the column."
• Solution: Check whether COLUMN1 has any constraints such as NOT NULL or unique and whether your source file has violated these constraints.
 
4. Msg: The MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row. A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times. Refine the ON clause to ensure a target row matches at most one source row, or use the GROUP BY clause to group the source rows.
• Solution: Check whether the source table has rows with the same values for your key column(s) that are defined in JOIN conditions for source table and target table.
 
5. Msg: It is illegal to call out while inside message filter.
• Solution: While in the script edit window, after coding, before saving and closing window, click build, then close window. The error won't appear.
 
6. Msg: Invalid object name 'dbo.Member'.
• Solution: Check whether the object 'dbo.Member' exists or not.
 
7. Msg: Failed to acquire connection "Conn_database". Connection may not be configured correctly or you may not have the right permissions on this connection. or The connection "{4D8DBB27-A124-42FE-AFB5-5F2866A64455}" is not found.
• Solution: Check whether connection of Conn_database has been configured correctly.
 
8. Msg: System.OverflowException: Arithmetic operation resulted in an overflow. at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.HandleUserException(Exception e)...
• Solution: Check whether in the script you have defined some arithmetic operation that is not allowed zeros or empty inputs.
 
9. Msg: Error on Component:[FF_Move_File_ToArchive]: An error occurred with the following error message: "The process cannot access the file because it is being used by another process.".
• Solution: Check whether the file is opened by another application. Close it and try again.