Problem: We need to download the most recent file in past 7 days from FTP.

Solution: This can be done in various different ways, in this article we will be using script task to accomplish this goal.

Our Control Flow tab would look like this [More]

Problem Statement:

In a company say ABC Inc. you have stored data in the database for each department. Data includes Location, total salary, # of Paid vacation, and total #of employees for each department. We have a department head for every department and once in every month we need to send these department head's a summary report reflecting this data in an excel file. So our goal is to create different excel reports for each department. [More]

In this article, we will be using FTP task to download an .xls file and then use that file to upload it in SQL Server using Data Flow Task base. So let's create a new package and name our package as DownloadFile. [More]

Recently, we had a server migration from 32 bit to 64 bit, once the transfer was complete, my packages developed in BID's started giving an error that read as "SQL Server Destination" failed the pre-execute phase and returned error code 0xC0202071. After searching for resolution I found help on Microsoft here http://support.microsoft.com/kb/2009672 [More]

Recently, I came across a situation where I had to produce space used across tables in a database. Now my database contains more than 500 tables and to execute sp_spaceused for every table was time consuming. So below is the faster way that can work. [More]

Row Constructor is new feature to SQL Server 2008 that allows insertion of multiple rows of data at once. Say we create a table row_constructor create table row_construct (ID int identity(1,1) not null primary key , type varchar(20) not null default 'N/A' , name varchar(100) not null default 'N/A' )   insert row_construct (type,name) values ('A', 'Garments') insert row_construct (type,name) values ('B', 'Sports Equipments') insert row_construct (type,name) values ('C', 'Cosmetics') insert row_construct (type,name) values ('A', 'Swim Wears') insert row_construct (type,name) values ('A', 'Sports Garments')   --but with the help of row constructors we can insert the same data using single insert statement as below   insert row_construct (type,name) values ('A', 'Garments'),('B', 'Sports Equipments'),('C', 'Cosmetics'),('A', 'Swim Wears'),('A', 'Sports Garments'); --we can also write the same as insert row_construct select type, name from (values ('A', 'Garments'),('B', 'Sports Equipments'),('C', 'Cosmetics'),('A', 'Swim Wears'),('A', 'Sports Garments')) as rowtables (type, name);   --Limitations: --We cannot select from other table in values clause as below it will only give "Incorrect syntax near the keyword 'select'."   insert row_construct select type, name from ( values (select 'A', 'Garments'),('B', 'Sports Equipments'),('C', 'Cosmetics'),('A', 'Swim Wears'),('A', 'Sports Garments') ) as rowtables (type, name);   insert row_construct (type,name) values (select 'A', 'Garments'),('B', 'Sports Equipments'),('C', 'Cosmetics'),('A', 'Swim Wears'),('A', 'Sports Garments');   --We can insert from multiple tables only by using UNION/UNION ALL clause as below   insert row_construct select 'A', 'Garments' union select 'B', 'Sports Equipments' union select 'C', 'Cosmetics'

Say we have a stored procedure that we want to copy across multiple databases. One way would be to create the script and execute in all the databases. But if Databases are many it can be time consuming and efforts to change Database name every time before executing the script will be more. One better way is writing a script that can create the Database object in selected Databases or all Databases at once.
[More]

In this article we will see the way execution order works in SQL server, mainly when TOP, Union and order by clause are utilized in a query.

Say we have an Employee table

create table Employees

(EmployeeID int identity(1,1) NOT NULL

,EmployeeName varchar(50) NOT NULL default 'N/A'

,Department varchar(50) NOT NULL default 'N/A'

,Salary float NOT NULL default 0) [More]

To offer better manageability and more security for login and user maintenance Microsoft separated roles into Database roles and Application Roles. Application Roles are mainly used by applications and they do not contain any database users. In this article we will see as how we can create an Application Role in SQL server and use it in a web application.
[More]

In this article we will see as how we can query the result from relational table when the input parameter is sending data in the form of string separated by a delimiter. [More]