SSIS- Transform SQL table records to Excel with Dynamic Name

SQL Server Integration Services (SSIS) is used to perform a data migration task.

Let's create a simple package. if you have SSIS installed on your system then you can see Integration service project while creating new project in Visual Studio.

Step 1:

Open Visual Studio => Go to Menu => File => New => Project. It will open the dialogue below. Select Integration Service Project.


















It will create new blank SSIS project.


Step 2:
Go to project Menu and select SSIS Import and Export Wizard as shown in below screenshot.















It will open the wizard below and press next



Step 3:
Choose a data source, Select SQL Server Native Client as shown in below screen shot. Set the source database connection string and press Next.

Step 4:
Choose a destination, Select the Excel File Path where you want to place the file. Select the excel version as well and Press Next.

Step 5:

Select the option to write the query and press Next.

Step 6:

Write you SQL statement below. you can run the stored procedure as well.
Create the stored procedure and write EXEC Procedure Name. Press Next

Step 7:

It will automatically convert the datatype to excel format datatype. Press Next.

Step 8:

Press Finish and Complete the wizard.

Step 9:

Once you close the wizard, you can see it will automatically create Data Flow Task as shown in snapshot below.

When you click Data flow tab, you can see it has 3 things.

A. Source (SQL table)
B. Data Conversion (That will convert datatypes)
C. Destination (Excel File Columns)

Step 10:

Run the project, it will generate the excel on specific location that you mentioned on Step 4.

How to give random name to excel file every time you run the SSIS package?
Click on link below

Excel name with time stamp

That is all about the article. Hope you like this.

How to give random name to excel file every time you run the SSIS package

If you don't know how to create SSIS Package then go through below link first.

Create SSIS Package

Now, I'll explain how to give random name to excel file with time stamp.

Step 1:

Right click on the Destination excel connection (Excel Connection Manger) and click properties as shown in the below snapshot.




Step 2:


Click on the Expressions as shown in snapshot below.

Step 3:


Click on the ExcelFilePath in the property dropdown and set the expression

In expression we are concatenating time stamp  with excel file name. As example below

"F:\\Client_"+
RIGHT("0" + (DT_STR,2,1252)DATEPART("MM" ,GETDATE()), 2) + "-"+
RIGHT("0" + (DT_STR,2,1252)DATEPART("DD" ,GETDATE()), 2)  + "-"+
(DT_STR,4,1252)DATEPART("YYYY" ,GETDATE()) +  "_" + Right("0" + (DT_STR,4,1252) DatePart("hh",getdate()),2) + "" +  Right("0" +  (DT_STR,4,1252) DatePart("n",getdate()),2) + ".xls"


Now when you run the package, every time it will generate excel file with time stamp.
This is all about the article.