- Double click the Script Component
- Script Transformation Editor Window will be displayed.
- Now list of input columns will be displayed with checkboxes. None of the checkboxes are selected.
- Select all the checkboxes.
- Now select the Inputs and outputs menu from the left side pane.
- Now Expand the Output 0 and you can see Output Column Folder.
- Now click the Add Column button and a column will be added, rename to MyColumn and change the data type to string [DT_STR]
Next Click the Script item from the left side pane.
Click “Design Script” button. You will have an editor and you will in the Input0_ProcessInputRow event section.
Note: In order to work with Design Script section, you should have installed Sql Server Service Pack 2. Otherwise you may get an error.
We have created one output column MyColumn. In that column we are going to assign the Error Description.
Write the following code in the Input0_ProcessInputRow event
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Row.MyColumn = ComponentMetaData.GetErrorDescription(Row.ErrorCode)
GetErrorDescription function will return the Description of the current error with the help of the Error Code.
Our next task is, we have to store the error row in another temp table.
Save and Close the Script designer.
Click ok in the Script Transformation Editor
Now drag the OLE DB Destination control under the Script Component Control.
- Click the Script Coponent Control. You will notice that there is one green line arow.
- Drag that arow and join with OLE DB Destination
- Now Double click on the OLE DB Destination control from the package designer.
- You will get OLE DB Destination Editor window. Change the Data Access Mode to “Table or View”
- Next Click the “New” button for the “Name of the table or view” dropdown.
- Now you will get the create table window
- Change the Table Name to “TempErrDescription”
- Then Click ok
- Click the Ok button in the “OLE DB Destination Editor” window.
- If the Ok button is still disabled, then just clik the “Mappings” from the Left site pane and then click the “Connection Manager” once again. Now Ok button will be enabled.
- So now you can click the ok button. Click it.
- Now your screen looks like
- Now run the package.
- Two rows will be transmitted to the Company Table and Error row will be transmitted to the TempErrDescription table.