SSIS and Sql Server Journey

Personal Notes of Sarabjit Singh and Bhavpreet Singh

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

Fragmentation – De-Fragmentation.. Must Read..

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

Sql Server 2008: Compound Assignment Operation

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 post 3 more words

Leave a comment »

SQL SERVER – Exporting Query Results to CSV using SQLCMD

Result to CSV file

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 »

Must Read: Improve productivity with SSMS

http://www.sqlservercentral.com/blogs/db-newsfeed/2013/04/30/five-ways-to-improve-your-productivity-with-ssms/

Leave a comment »