SSIS and Sql Server Journey

Personal Notes of Sarabjit Singh and Bhavpreet Singh

SQL SERVER – Template Browser – A Very Important and Useful Feature of SSMS

Template Browser

Leave a comment »

Sql Server: Alter Table in Design

Many a time while making changes to the definition of the Sql Server Table we get the following message:
“Saving not permitted. The changes you have made require the following tables to be dropped and re-created or enable option Prevent saving changes that require the table to be re-created. ”


To disable the option “Prevent saving changes that require the table to be re-created”
Go to Tools > Options > Designers > “Un-check” “Prevent saving changes that require the table to be re-created”

Sql Server Hack-Save Changes

Leave a comment »

SSIS Task: For loop Vs For Each Loop

For Loop

For Loop is used for looping through a number of tasks for a set number of times.

Simply:->  initialize, verify condition, increment\assign

For Each

ForEach Loop loops through various things such as files, objects, ADO connections, etc.

Above screen shot show we have task configured for “Foreach file enumerator” which can be used to move\copy\delete file (of given file) in a given folder

Enumerator configuration:

Folder: Specify the folder path from where we need to process the files

Flies: File name (format) \ file extension

  • Fully qualified: File name along with location is returned .Eg:  C:\Example.txt

  • Name and extension: The file name with its extension is returned.Eg: Example.txt

  • Name only: The file name without its extension is returned.Eg: Example

Variable Mapping

Result of the for loop will be assigned to variable and its value can be used further.

  • Foreach File Enumerator: Enumerates files in a folder

  • Foreach Item Enumeration: Enumerates items in a collection, such as the executables specified in an Execute Process task.

  • Foreach ADO Enumerator: Enumerates rows in a table, such as the rows in an ADO recordset.

  • Foreach ADO.NET Schema Rowset Enumerator: Enumerates schema information about a data source.

  • Foreach From Variable Enumerator: Enumerates a list of objects in a variable, such as an array or ADO.NET DataTable.

  • Foreach NodeList Enumeration: Enumerates the result set of an XML Path Language (XPath) expression.

  • Foreach SMO Enumerator: Enumerates a list of SQL Server Management Objects (SMO) objects, such as a list of views in a database.

Leave a comment »