Creating dynamically generated CSV files containing SQL Server data
Creating dynamically generated CSV files containing SQL Server data
Steve has presented papers at 8 PASS Summits and one at PASS Europe 2009 and 2010. He has recently presented a Master Data Services presentation at the PASS Amsterdam Rally.
Steve has presented 5 papers at the Information Builders' Summits. He is a PASS regional mentor.
View all posts by Steve Simon Latest posts by Steve Simon (see all) Reporting in SQL Server – Using calculated Expressions within reports - December 19, 2016 How to use Expressions within SQL Server Reporting Services to create efficient reports - December 9, 2016 How to use SQL Server Data Quality Services to ensure the correct aggregation of data - November 9, 2016
SQLShack
SQL Server training EspañolCreating dynamically generated CSV files containing SQL Server data
February 12, 2015 by Steve SimonIntroduction
A few months back, I presented a paper at SQL Saturday 327 in Johannesburg, South Africa. Late last month I received an email from one of the attendees. His issue was quite interesting and I decided to share it with you. The gentleman wanted a SSIS script that would permit him to extract data from a SQL Server database table and place it in a CSV file with a dynamically allocated name. Being a strong advocate of using the SSIS toolbox, I experimented with an alternative solution. We are going to construct THIS SOLUTION in today’s get together. Let’s get started.Getting Started
As our point of departure, we shall once again utilize our ‘SQLShackFinancial” database. We shall utilize data from the FASB table (see below). Our end goal is to obtain the following output: The astute reader will note that the csv file name contains the date and time on which the file was created. Opening Visual Studio we begin by creating a new Integration Services project. We select New and Project (see above). We select an Integration Services project and give our project a name. We click OK to create the project. We find ourselves on our Integration Services project workspace (see above). Our first task is to create a data connection to our SQLShackFinancial database.Creating the database connection
We begin by right clicking in the “Connection Manager” box and selecting a “New OLE DB Connection” (see above). The “Configure OLE DB Connection Manager” dialogue box is brought up. We select “New” (see above). The “Connection Manager” dialogue box is now brought up and we configure this box as shown above. Testing the connection, we find that we are ready to go. We now add a Data Flow Task to our work surface (see below). Double clicking on the “Data Flow Task” we are brought into the “Data Flow Task” designer (see below). We now add an “OLE DB Data Source” to our work surface (see below). Double clicking on the “OLE DB Source” brings up the “OLE DB Source Editor” dialogue box. We configure our connection manager to point to the FASB table (see above). The columns tab shows the data columns within the table (see above).Adding and configuring the destination csv file
We are now in a position to add the destination flat file which will contain the table data in a csv format. We drag a “Flat File Destination” control onto our work surface (see above) and join the “OLE DB” data source to the “Flat File” destination (see below). Double clicking the control brings up the “Flat File Destination” editor (see above). We click “New” to create a new connection. The “Flat File Format” dialogue box is brought into view (see above and to the left). We accept the “Delimited” radio button. Click OK. The “Flat File Connection Manager Editor” is then brought up. We are asked for a “Description” (which is optional) but more importantly we are asked for an output file name. We give our output file the name “FASB_” and set its type to csv (see above). We click “Open”. Clicking the “Columns” tab, we see that the fields from our table are visible. We click OK to exit this dialogue box and then click on the “Mappings” tab to configure the source to the destination (see below). We click OK to exist the “Flat File Destination Editor”. We now find ourselves back on our working surface. THE ISSUE is that in creating the flat csv file we have hard-wired the file name is this is NOT what we want.Creating a dynamic file name for our output file
We begin by right clicking on our output file connection and bringing up its “Properties” box (see above and to the bottom right). We scroll down to find the “Expressions” property (see above and to the bottom right). Clicking on the “Expressions” ellipsis the “Property Expresssion Editor” is brought up(see above). We select the “Connectionstring” Property from the “Property” dropdown list (see above) and click upon the “Expression” box. The “Expression Builder” dialogue box is brought into view (see above). In the “Expression” box, we enter the following code snippet. Adding a time component (to the string) enables us to produce multiple daily extracts. 123456789 "C:\\SQLShack\\FASB_" +Right("0" + (DT_STR,4,1252) DatePart("m",getdate()),2) +Right("0" + (DT_STR,4,1252) DatePart("d",getdate()),2) + (DT_STR,4,1252) DatePart("yyyy",getdate()) + "_"+ (DT_STR,2,1252) DatePart("hh",getdate()) +"_"+ (DT_STR,2,1252) DatePart("n",getdate()) + ".csv" The astute reader will note that when we “Evaluate” the expression (see above) that the fully qualified file name appears in the “Evaluated value” box. Further we must note the usage of two “\\” for every one that we wish to appear in the file “Evaluated” path and file name. FORTRAN and COBOL programmers will remember this one! We click OK to leave the “Expression Builder” and OK to leave the “Property Expressions Editor” (see above). We find ourselves back on our work surface (see above).Let us give our package a whirl
We click the “Debug” tab on the top ribbon and select “Start Debugging” (see above). The process completes successfully. Our extract file may be seen in the screen dump above. Note that the file name contains the run date and time of the process. Opening the comma delimited file in Notepad, we see the data in a comma delimited form.Conclusion
Oft times we find that we have external processes that require extracts of data from our tables. In some cases the final format must be in CSV format. In this “get together” we have constructed a quick and dirty process to pull data from a database table and to place the data into a flat csv file IN ADDITION to providing a mechanism to run the process throughout the day. Should you wish the code for this article, please feel free to contact either the SQLShack editor or me. In the interim, happy programming. Author Recent Posts Steve SimonSteve Simon is a SQL Server MVP and a senior BI Development Engineer with Atrion Networking. He has been involved with database design and analysis for over 29 years.Steve has presented papers at 8 PASS Summits and one at PASS Europe 2009 and 2010. He has recently presented a Master Data Services presentation at the PASS Amsterdam Rally.
Steve has presented 5 papers at the Information Builders' Summits. He is a PASS regional mentor.
View all posts by Steve Simon Latest posts by Steve Simon (see all) Reporting in SQL Server – Using calculated Expressions within reports - December 19, 2016 How to use Expressions within SQL Server Reporting Services to create efficient reports - December 9, 2016 How to use SQL Server Data Quality Services to ensure the correct aggregation of data - November 9, 2016