Adding a Script - SSIS - SQL Programmers Chicago

Send a Message


 

 

 


 
  

Need help with Microsoft SQL Server? Ask our Experts, or simply Email your query.

About Sql Server Programmers

Our clients can be found both locally in the Chicagoland area and throughout the country. We have over 18 years of experience and are extremely proud of our track record of successfully assisting hundreds of our clients to improve their productivity while focusing on cost.

Microsoft Certified Partner

  

Adding a Script

  • Double click the Script Component
  • Script Transformation Editor Window will be displayed.

SSIS Steps

  • 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.

SSIS Steps

  • Now click the Add Column button and a column will be added, rename to MyColumn and change the data type to string [DT_STR]

SSIS Steps

  • 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)
End Sub

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.

SSIS Steps

  • Click the Script Coponent Control. You will notice that there is one green line arow.

SSIS Steps

  • 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

SSIS Steps

  • 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

SSIS Steps

  • Now run the package.
  • Two rows will be transmitted to the Company Table and Error row will be transmitted to the TempErrDescription table.