SSIS and Sql Server Journey

Personal Notes of Bhavpreet Singh

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

Bhavpreet Singh:

Template Browser

Originally posted on Journey to SQL Authority with Pinal Dave:

Let me start today’s blog post with a direction question. How many of you have ever used Template Browser? Template Browser is a very important and useful feature of SQL Server Management Studio (SSMS). Every time when I am talking about SQL Server there is always someone comes up with the question, why there is no step by step procedure included in SSMS for features. Honestly every time I get this question, the question I ask back is How many of you have ever used Template Browser?

I think the answer to this question is most of the time either no or we have not heard of the feature. One of the people asked me back – have you ever written about it on your blog? I have not yet written about it. Basically there is nothing much to write about it. It is pretty straight forward feature, like any…

View original 290 more words

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

Image

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 »

SSIS: Derived column : Conditional Operator + Hardcoding Null

Using Conditional Operator In SSIS:

«boolean_expression» ? «when_true» : «when_false»

To hardcode Null : NULL(DT_WSTR, 50)

Following is the expression to get the sub string and compare to “N” and return NULL if true

else the sub string string itself.

(DT_I4)((SUBSTRING(LTRIM(Answer),1,1) == “N”) ? NULL(DT_WSTR, 50) : SUBSTRING(LTRIM(Answer),1,1))

Leave a comment »

SQL SERVER – Fragmentation – Detect Fragmentation and Eliminate Fragmentation

Bhavpreet Singh:

Fragmentation – De-Fragmentation.. Must Read..

Originally posted on Journey to SQL Authority with Pinal Dave:

Q. What is Fragmentation? How to detect fragmentation and how to eliminate it?

A. Storing data non-contiguously on disk is known as fragmentation. Before learning to eliminate fragmentation, you should have a clear understanding of the types of fragmentation. We can classify fragmentation into two types:

  • Internal Fragmentation: When records are stored non-contiguously inside the page, then it is called internal fragmentation. In other words, internal fragmentation is said to occur if there is unused space between records in a page. This fragmentation occurs through the process of data modifications (INSERT, UPDATE, and DELETE statements) that are made against the table and therefore, to the indexes defined on the table. As these modifications are not equally distributed among the rows of the table and indexes, the fullness of each page can vary over time. This unused space causes poor cache utilization and more I/O, which ultimately leads to poor…

View original 540 more words

1 Comment »

SqlCmd : Error Cannot generate SSPI co ntext

Got the following error while trying to connect to SqlCmd of local machine.

Error Cannot generate SSPI co ntext

Searching online for the solution figured out that it has to do something with the network.

Workaround: Disable the network and try running the same command it works like charm.

Looking for the exact issue and better solution. Feel free to comment.

Leave a comment »

Sql Server: Using Sqlcmd to export data to text file

A SqlServerCentral friend was looking for a option to export data from Sql Server to a text file on periodic option. Refer the link below for details:

http://www.sqlservercentral.com/Forums/Topic1452185-1550-1.aspx
Adviced him to run the following sqlcmd in the job..
SQLCMD -S YourSqlServer -d YourDatabase -U YourUserName -P YourPassword -Q “Your Query” -s “,” -o “C:\YourFileName.txt”
Feel free to post better work around.
I understand SSIS could be another option.

Leave a comment »

Compound Assignment Operators in SQL Server 2008

Bhavpreet Singh:

Sql Server 2008: Compound Assignment Operation

Originally posted on Systems Engineering and RDBMS:

Continuing in the series on the new features in SQL Server 2008, here is another small little tid-bit. SQL Server 2008 (tested in CTP5) supports the compound assignment operators. Here is a working example of those operators:

declare @i int
set @i = 100

/**************************
Addition and assignment
***************************/
set @i += 1
select @i

———–
101

/**************************
Subtraction and assignment
***************************/
set @i -= 1
select @i

———–
100

/**************************
Multiplication and assignment
***************************/
set @i *= 2
select @i

———–
200

/**************************
Division and assignment
***************************/
set @i /= 2
select @i

———–
100

/**************************
Addition and assignment
***************************/
set @i %= 3
select @i

———–
1

/**************************
xor operation and assignment
***************************/
set @i ^= 2
select @i

———–
3

/**************************
Bitwise & operation and assignment
***************************/
set @i &= 2
select @i

———–
2

/**************************
Bitwise | operation and assignment
***************************/
set @i |= 2
select…

View original 3 more words

Leave a comment »

SQL SERVER – Exporting Query Results to CSV using SQLCMD

Bhavpreet Singh:

Result to CSV file

Originally posted on Journey to SQL Authority with Pinal Dave:

Social media is evolving at a rapid pace and every day I keep on getting question from different methods. Here is the latest question which I received on my Facebookpage. The question was how to export the data of query into CSV using SQLCMD.

This is indeed very easy process and very simple command to export any query data. For example we will use AdventureWorks2012 database. Here is the query we will be using for our demonstration.

USE AdventureWorks2012
GO
SELECT TOP 10 sp.BusinessEntityID,
sp.TerritoryID, sp.SalesQuota,
sp.Bonus, sp.CommissionPct
FROM Sales.SalesPersonsp
GO

The above query will return following result set.

Now we can export above data to CSV using SQLCMD using following command.

SQLCMD -S . -d AdventureWorks2012 -Q “SELECT TOP 10 sp.BusinessEntityID, sp.TerritoryID, sp.SalesQuota, sp.Bonus, sp.CommissionPct FROM Sales.SalesPerson sp” -s “,” -o…

View original 72 more words

Leave a comment »

SSIS: Delete Empty Error Log Files With Dynamic Names

Many a times we have a scenario when we have to read from a file write the dirty row into a text file.
In my case, I had to create the text file for each run hence I created a file with dynamic name.
SSIS would create the file even when there is no error.
Now we need to delete the file in case the file is empty i.e. there are no error rows.

As we all know rowcount task come in hand to calculate the no. of records which error out.

ErrorCount
As the name of the error file is dynamic, we use the EvaluateAsExpression property of the variable.
ErrorFilePathEvaluated
As you can see in the screen short above we have a variable name
DataErrorFilePathEvaluated and the property EvaluateAsExpression is set to true. The expression value is set to append datetime to it.

The variable DataErrorFilePathEvaluated is also used as the ConnectionString of the output error file and same is used as the ConnectionString for the file system task to delete the file in case no records error out.

package

Hope it Helps :)

 

Leave a comment »

Follow

Get every new post delivered to your Inbox.