SSIS–DFT does not loads last row from flat file

Yesterday, I had an interesting issue.  It has been reported me that an ETL always misses the last row from the flat file.  No matter what is the row is it happens for every file that the last row is missing.

It is wired issue, I started check the ETL and created a new ETL with DFT pointing the same share file.  I could able to get all the rows.  It was happening only in that particular ETL.

I copied the DFT from the error ETL to my new ETL.  The problem disappeared.  I was wondering about the magic happening here.  The DFT is same, data file is same but the last row is getting missed.

I started comparing the ETL config and connections.  oh…at last I could able to figure out the issue.  It was due the Text Qualifier setting which update on the Flat File Connection component.  Make sure it set to None, if you don’t have any special handling.

Related MSDN discussion

Web API

Web API is the emerging trend in the Web programming.

The WiKi defines as “A web API (Application Programming Interface) is typically a defined set of HTTP request messages along with a definition of the structure of response messages, typically expressed in JSON or XML. While "web API" is sometimes considered a synonym for web service, the Web 2.0 applications typically have moved away from SOAP-based web services towards more direct REST-style communications.^[1] Web APIs allow the combination of multiple services into new applications known as mashups”

Check out the PDC 10 talk on Building Web API which gives insight on how Microsoft approaches Web API.

Getting Started with ASP.NET Web API

Check out the blog to get started Tutorial

SQL Table Size

Many times you may want to check the size of set of tables in SQL server.  There are multiple ways to achieve the same.

DECLARE @Unit CHAR(2)
DECLARE @SlicerSize FLOAT

SET @Unit = 'GB' --MB

CREATE TABLE #DataSize
(
    RowId        INT IDENTITY,
    TableName    SYSNAME,
    DataRowCount BIGINT,
    Reserved     VARCHAR(20),
    Data         VARCHAR(20),
    IndexSize    VARCHAR(20),
    Unused       VARCHAR(20)
)

SELECT @SlicerSize = CASE WHEN @Unit = 'GB' 
                          THEN 1024.0 / 1024.0
                          ELSE 1024.0 
                     END

-- Add the table you want to check the size
INSERT #DataSize EXEC sp_executesql N'EXEC sp_spaceused [dbo.Emp]'
INSERT #DataSize EXEC sp_executesql N'EXEC sp_spaceused [dbo.Address]'

SELECT TableName, 
       DataRowCount / 1000 AS RowCountInKs ,
       CONVERT(DECIMAL(10,2),CONVERT(INT,REPLACE(Reserved,' KB','')) / @SlicerSize)  AS ReservedSpace ,
       CONVERT(DECIMAL(10,2),CONVERT(INT,REPLACE(Data,' KB','')) / @SlicerSize)      AS DataSize ,
       CONVERT(DECIMAL(10,2),CONVERT(INT,REPLACE(IndexSize,' KB','')) / @SlicerSize) AS IndexSize,
       @Unit                                                                         AS Unit 
       FROM #DataSize

DROP TABLE #DataSize

Encryption

Encryption is used to product the data.  There are 2 types of encryption.

1. Symmetric: The key used to encrypt and decrypt are same.
2. Asymmetric

Symmetric Encryption

There 4 different algorithms we can use to encrypt data in C#

1. DES
2. TripleDES
3. RC2
4. Rijndeal

Asymmetric Encryption

• In Asymmetric encryption data is get signed using public key and decrypted using private key.  for complete list and detail check MSDN Article

Securing you Azure Connection String

With windows Azure you have dependency on the Keys, like storage key, sql authentication.  It is important to encrypt them and secure it.  There is good article which details the steps to secure your connection strings.

Configuring specific section of web.config

1. Creating Certificate
2. Uploading Certificate to Azure portal
3. Encrypting & updating web.config
4. Config Provider

for Encrypting and Decrypting string using certificate refer http://msdn.microsoft.com/en-us/library/windowsazure/hh697511.aspx