SQL Server Performance

  • Home
  • Articles
  • Forums
  • Tips
  • Training
  • FAQ's
  • Blogs
  • Software
  • Books
  • About Us
RSS Feeds
Sign in | Join


FAQ Topics

All FAQ's
General DBA
General Developer
DBA Performance Tuning
Developer Performance Tuning
Clustering
Error Messages

USEFUL SITES :

ASP.NET Tutorials
Windows and SQL Azure Tutorials
Cloud Hosting Magazine
SharePoint Tutorials
Windows Server Help

Write for Us

Share your SQL Server knowledge with others and raise your profile in the community More...
Latest Articles

A High Level Comparison Between Oracle and SQL Server - Part ...
A High Level Comparison Between Oracle and SQL Server - Part ...
A High Level Comparison Between Oracle and SQL Server - Part ...
A High Level Comparison Between Oracle and SQL Server

More     
 
Latest FAQ's

Add Node to A SQL Server failover Cluster failed with invalid ...
SQL Server Destination remote server error
Setting Up Data And Log Files For SQL Server
Will Check Constraints Improve Database Performance?

More     
   
Latest Software Reviews

dbForge Review
Spotlight on ApexSQL Diff - Server-based database comparison tool ...
Spotlight on ApexSQL Data Diff - Server-based database comparison tool ...
Spotlight on ApexSQL Doc 2008

More     

How to Generate an Auto Incremental Number in a SSIS Package?



As many of you aware, you can have incremental integers in tables by setting the identity property for the column. In a T-SQL, you can do this by using the function named ROW_NUMBER(). In SQL Server Reporting Services you can acheive this by using RowNumber(Nothing). So how do you do this in SQL Server Integration Services (SSIS)?

Unlike the examples above there is no function to do this in SSIS. However you can aceive this using the Script component of SSIS by following the steps below.

1. Drag and drop the Script Component to the Data flow and select Script Component Type as Transformation. 2. Double click the Script Component.
3. In the Input Columns tab, select the column you want to pass through the script component.
4. In the Inputs and Outputs tab, add a column with an integer data type. (In this sample below, I have added a column name Column.)

 

5. Next, go to the Script tab and click the Design Script button. Type the following script in the script editor:

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Public Class ScriptMain
    Inherits UserComponent

   
Dim intCounter As Integer = 0 'Set intial value here
    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
         Row.Column = intCounter
         intCounter = intCounter + 1   ' Set the incremental value here
    End Sub
End Class


In the above script you can set the initial value and the incremental value to whatever you want. In the above example both are set to 1.

6. The following is the output of the script in which the column named Column is the auto incremental field generated from the Script Component.

 








C# Help and Tutorials | PHP MySQL Tutorial | Sharepoint Tutorial | Azure Tutorial | Cloud Hosting Magazine | ASP.NET Tutorials | Windows Server Help | Windows Phone Pro | Silverlight Ace | Visual Studio Tutorials | Home | Peformance Articles | Audit Articles | Business Intelligence Articles | Clustering Articles | Developer Articles | Reporting Services Articles | DBA Articles | ASP.NET / ADO.NET Articles | SQL Server Training Videos | DBA FAQ's | Developer Peformance FAQ's | DBA Peformance FAQ's | Developer FAQ's | Clustering FAQ's | Error Messages | Audit Tool Reviews | Backup Tool Reviews | Coding Tool Reviews | Compare Tool Reviews | Documentation Tool Reviews | Design Tool Reviews | Monitoring Tool Reviews | Log Tool Reviews | Reporting Tool Reviews | Clustering Tool Reviews | Security Tool Reviews | Change Management Tool Reviews | Remote Access Tool Reviews | Book Reviews | Security Tool Reviews | ADO.NET / ASP.NET | Administration | Analysis/OLAP Services | Application Development | Configuration | Components | ETL | Hardware | High Availability | Hints | Index | Misc | Operating Systems | Performance Tuning | Replication | T-SQL | Views


              © 2010 Jude O'Kelly. All rights reserved