How to retreive random number of rows from a table

USE THE TABLESAMPLE KEYWORD AFTER THE TABLE NAME.

Following queries retrieve random number of rows each time

SELECT * FROM TABLE_NAME TABLESAMPLE(100 ROWS)

SELECT * FROM TABLE_NAME TABLESAMPLE(10 PERCENT)

How to get all tables row count in a database

The more effective way of getting all tables row count in a database is using sysindexes table. This will not do any table scans to get the values.

Following is the code to get the table name and row counts


select so.name,si.rows from sysindexes si
inner join sysobjects so
on si.id = so.id
where si.indid=0 and so.type='u'
order by so.name



SSIS 2014 New Features

There are no new features introduced in SSIS 2014.
There are no changes to the existing features.

For more information check the MSDN link at http://msdn.microsoft.com/en-us/library/bb522534.aspx

How to bring database in SQL Server from Restore mode to normal

Change the database_name to the database name having issues.

USE master
GO

 ALTER DATABASE database_name SET OFFLINE WITH ROLLBACK IMMEDIATE
GO

 ALTER DATABASE database_name ONLINE
GO

SSIS - Error while running task in transactions

Following is the error message when running the SSIS package which contains Transaction for batch processing of the data.

Error:
0xC001401A at Transaction: The SSIS Runtime has failed to start the distributed transaction due to error 0x8004D01B "The Transaction Manager is not available.". The DTC transaction failed to start. This could occur because the MSDTC Service is not running.

Resolution:
Start the service "Distributed Transaction Coordinator"
1. Go to Start menu - Run command
2. Type services.msc
3. Start the service "Distributed Transaction Coordinator" where the packages are executing.

SSIS 2012 - Eexecute ssis package from command line

1. Open the Visual Studio Command window
2. Go to SSIS pacakage location
3. Execute the following command
dtexec /f package name

SSIS 2012 FTP Task Error to connect to FTP server

You can use the following ftp server for testing FTP Connection.
Use anonymous user without having any password ftp2.census.gov

Following are the error message while connecting and receiving the files from FTP SERVERS.
Error
An error occurred in the requested FTP operation. Detailed error description: 200 Switching to ASCII mode. 200 PORT command successful. Consider using PASV. 425 Failed to establish connection Exception from HRESULT: 0xC001602A

Resolution:

1. Create an inbound and outbound rule to allow the FTP PORT(21) to OPEN
2. Select the FTP Connection to "Use Passive Mode"