Scenario : Download Script
You are working as SQL Server Integration Services developer. You need to create an SSIS Package that should execute Stored Procedure and write the results to flat file. Every time the SSIS Package will run, it should create new flat file with date-time.
This is simple requirement. You might think to use OLE DB Source and then use Flat file destination. But there is small problem. The definition of Stored Procedure can change anytime, that means the number of columns returned by Stored Procedure can change.
You need to develop an SSIS Package in such a way that you don't have to reopen and remap the columns every-time the definition of Stored Procedure change.
Solution:
We are going to use the Script Task in SSIS Package to create flat file dynamically form Stored Procedure result-set.
Step 1: Create new SSIS Package and create Variables
Open your SSDT ( SQL Server Data Tools) and create new SSIS Package. Once done, create below variables as shown.
FlatFileNamePart: Provide the file name that you would like to create. Date-time part will be added to it.
DestinationFolder: Provide the destination folder path where you would like to create your files.
StoredProcedureName: Provide the Stored Procedure name with schema from which you like to get data
LogFolder: Provide the Log folder path where you would like to create log file in case of error in Script Task.
FileDelimiter : Provide the file delimiter you would like to use, you can provide comma, pipe etc.
FileExtension: Provide the extension for your file such as txt or csv etc.
Create variable in SSIS Package to generate flat file dynamically from Stored Procedure
Step 2: Create ADO.NET Connection in SSIS Package to use in Script Task
Create ADO.NET Connection Manager so we can use in Script Task to get Stored Procedure data to export to flat file. This ADO.Net connection should be pointing to Database which contains the Stored Procedure from which we want to export data to flat file.
Create ADO.NET Connection in SSIS Package to use in Script Task to export Stored Procedure results to flat file dynamically
Step3: Add Variables to Script Task to use from SSIS Package
Bring the Script Task on Control Flow Pane in SSIS Package and open by double clicking Check-box in front of variable to add to Script Task.
Add variables to Script Task to generate flat file dynamically from Stored Procedure results in SSIS Package
Step 4: Add Script to Script task Editor in SSIS Package to Export Data from SQL Server Table or View to flat file dynamically.
Click Edit Button and it will open Script Task Editor.
Under #region Namespaces, I have added below code
using System.IO; using System.Data.SqlClient;
Under public void Main() {
I have added below code.
string datetime = DateTime.Now.ToString("yyyyMMddHHmmss"); try { //Declare Variables string FileNamePart = Dts.Variables["User::FlatFileNamePart"].Value.ToString(); string DestinationFolder = Dts.Variables["User::DestinationFolder"].Value.ToString(); string StoredProcedureName = Dts.Variables["User::StoredProcedureName"].Value.ToString(); string FileDelimiter = Dts.Variables["User::FileDelimiter"].Value.ToString(); string FileExtension = Dts.Variables["User::FileExtension"].Value.ToString(); //USE ADO.NET Connection from SSIS Package to get data from table SqlConnection myADONETConnection = new SqlConnection(); myADONETConnection = (SqlConnection)(Dts.Connections["DBConn"].AcquireConnection(Dts.Transaction) as SqlConnection); //Execute Stored Procedure and save results in data table string query = "EXEC "+StoredProcedureName; SqlCommand cmd = new SqlCommand(query, myADONETConnection); DataTable d_table = new DataTable(); d_table.Load(cmd.ExecuteReader()); myADONETConnection.Close(); string FileFullPath = DestinationFolder +"\\"+ FileNamePart +"_" + datetime + FileExtension; StreamWriter sw = null; sw = new StreamWriter(FileFullPath, false); // Write the Header Row to File int ColumnCount = d_table.Columns.Count; for (int ic = 0; ic < ColumnCount; ic++) { sw.Write(d_table.Columns[ic]); if (ic < ColumnCount - 1) { sw.Write(FileDelimiter); } } sw.Write(sw.NewLine); // Write All Rows to the File foreach (DataRow dr in d_table.Rows) { for (int ir = 0; ir < ColumnCount; ir++) { if (!Convert.IsDBNull(dr[ir])) { sw.Write(dr[ir].ToString()); } if (ir < ColumnCount - 1) { sw.Write(FileDelimiter); } } sw.Write(sw.NewLine); } sw.Close(); Dts.TaskResult = (int)ScriptResults.Success; } catch (Exception exception) { // Create Log File for Errors using (StreamWriter sw = File.CreateText(Dts.Variables["User::LogFolder"].Value.ToString() + "\\" + "ErrorLog_" + datetime + ".log")) { sw.WriteLine(exception.ToString()); Dts.TaskResult = (int)ScriptResults.Failure; } }
TSQL Scripts used in the video
--Create Procedure without Paramter
Create procedure dbo.prc_TotalSale
AS
Begin
Select * from dbo.TotalSale
End
--Alter Stored Procedure to add parameter
Alter procedure dbo.prc_TotalSale
@Region VARCHAr(50)
AS
Begin
Select [SalePersonFName]
,[SalePersonLName]
,[ProductName]
,[ItemsSold]
,[SoldPrice]
,Region
from dbo.TotalSale
where Region=@Region
End
--Execute Stored Procedure
EXEC dbo.prc_TotalSale 'ASia'
--Get Distinct Regions
Select distinct REgion from dbo.TotalSale
Step 5: Save the script and run the Package
Save the script in Script Task editor and then run your SSIS Package. It should create the flat file with date-time every time you will execute package. You have freedom to change the extension of file such as txt or csv. Also you can change delimiter by changing value of variable.
I execute the package and then dropped the column in the stored procedure definition and it was able to create flat file with date-time without any problem.
How to create flat file dynamically from Stored Procedure results in SSIS Package-SSIS Tutorial
Video Demo: How to create flat file dynamically from Stored Procedure results in SSIS Package
Video Demo: How to create flat file dynamically from Stored Procedure results in SSIS Package
Related Posts / Videos on Dynamic Text / CSV files by Script Task
- How to Import Multiple Text or CSV files to SQL Server Table by using Script Task in SSIS Package
- How to Load Text /CSV files with same or less columns than destination table by using Script Task in SSIS Package
- How to load Data from Multiple Text / CSV Files to SQL Server Table with File Name by using Script Task in SSIS Package
- How to Create Tables Dynamically from Flat Files and load Data in SSIS Package
- How to load Flat files to SQL Server Tables according to the Name of Flat File in SSIS Package
- How to Create Multiple Text/CSV Files Dynamically From a SQL Server Table depending upon Distinct Column Value in SSIS Package
- How to Export large table to multiple text/csv files by row count in SSIS Package
- How to create Text / CSV File Dynamically from Table or View in SSIS Package
- How to create Text / CSV File Dynamically from Stored Procedure Results in SSIS Package
- How to Load all text/csv files to single Text/CSV File from a folder in SSIS Package
- How to export all the tables from a database to CSV files with date-time in SSIS Package
how to handle commas and double quotes in the data. Please help
ReplyDelete