In this post I would like to show how anyone can easily generate files (CSV, XML, TXT and more) through the use of bcp utility on Microsoft SQL Server. This technique can be easily incorporated into an SSIS package as part of its workflow as we can build on this solution to further post-process the data. The bcp utility bulk copies data between an instance of Microsoft SQL Server and a data file in a user-specified format. The bcp utility can be used to import large numbers of new rows into SQL Server tables or to export data out of tables into data files. Except when used with the \u2018queryout\u2019 option, the utility requires no knowledge of Transact-SQL and can easily be incorporated into an SSIS package.<\/p>\n
As always, let\u2019s create a sample data set to work with. First, run these queries to create a dummy table and populate it with some test data.<\/p>\n
\r\nCREATE TABLE tbl_Test\r\n(\r\nMiddle_Name nvarchar (50) NULL,\r\nLast_Name nvarchar (50) NOT NULL,\r\nAddress1 nvarchar (100) NOT NULL,\r\nAddress2 nvarchar (100) NULL,\r\nCity nvarchar (50) NOT NULL,\r\nState nvarchar (50) NOT NULL\r\n)\r\n\r\nINSERT INTO tbl_Test\r\nVALUES ('Scott', NULL, 'Johnson', '1 Parade Drive', NULL, 'Sydney', 'NSW')\r\nINSERT INTO tbl_Test\r\nVALUES ('Mary', 'Jane', 'Frederik', '33 Spalding Court', NULL, 'Sydney' ,'NSW')\r\nINSERT INTO tbl_Test\r\nVALUES ('Jessie', 'Kane', 'Smith', '14 Melrose Place', 'Unit 4a', 'Melbourne', 'VIC')\r\nINSERT INTO tbl_Test\r\nVALUES ('Radash', 'Kumar', 'Mahid', '55 Sunshine Avenue', NULL, 'Cairns', 'QLD')\r\n<\/pre>\nOnce we have the data in place, let\u2019s assume that we would like to export it into a file (CSV is the format in this instance) onto the local drive or a network share. This is where the simplicity of bcp comes into play, although it can also be a bit limiting and cumbersome to work with e.g. having to put all attribute names on one line. As this tutorial deals with bcp executed from either Management Studio or BIDS, I will use some common SQL syntax and expressions to declare a couple of variables and finally execute SQL command to spit out the file.<\/p>\n
\r\n--DECLARE VARIABLE WHICH STORES THE OUTPUT FILE LOCATION\r\nDECLARE @Path varchar (100)\r\n\r\n--DECLARE A VARIABLE WHERE THE QUERY WILL BE STORED\r\nDECLARE @bcpCommand varchar (8000)\r\n\r\n--ASSIGN THE PATH TO THE VARIABLE. IN THIS CASE THE FILE WILL BE STORED ON THE LOCAL c:\\ DRIVE\r\nSET @Path = 'c:\\somefile.csv'\r\n\r\n--EXPORT THE FILE USING bcp (FIRST LINE - CREATE COLUMN HEADINGS, SECOND FILE - CREATE COLUMN DATA)\r\nSET @bcpCommand = \u2018bcp \u201cSELECT \u201cFirst_Name\u201d as H1, \u201cMiddle_Name\u201d as H2, \u201cLast_Name\u201d as H3, \u201cAddress1\u201d as H4, \u201cAddress2\u201d as H5, \u201cCity\u201d as H6, \u201cState\u201d as H7\u2019\r\nSET @bcpCommand = @bcpCommand + \u2018UNION ALL SELECT First_Name, Middle_Name, Last_Name, Address1, Address2, City, State FROM tbl_Test\u201d QUERYOUT\u2019 + @Path +\u2019c \u2013T \u2013t, -U\u201dlogin\u201d \u2013P\u201dpassword\u201d\u2019\r\n\r\n--SPAWNS A WINDOWNS COMMAND SHELL AND PASSES IN A STRING FOR EXECUTION\r\nEXEC master..xp_cmdshell @bcpCommand\r\n<\/pre>\nAlso, if further postprocessing is required on the newly output file e.g. FTP task, it is best if we include a \u2018WAITFOR DELAY\u2019 command to ensure that the file ends up in the folder specified. Otherwise, due to a short delay between the SQL execution and file creation the package may fail. <\/p>\n
One of the common situation which can take advantage of this process is creating a file with a name reflecting certain rules. Usually this involves formatting the file name to include the date when it was created e.g. yyyyMMdd_File_Name.csv. In such case we can easily adjust the above bcp command by adding a few variables and changing the SQL code to take them into account. To achieve this, we can add @FileName and @CurrentDate variables and alter the script to output the file with the current date included in its name as per below.<\/p>\n
\r\nDECLARE @Path varchar (100)\r\nDECLARE @bcpCommand varchar (8000)\r\nDECLARE @CurrentDate varchar (20)\r\nDECLARE @FileName varchar (50)\r\nSET @CurrentDate = (SELECT CONVERT (varchar (20), GetDate(),112) as [yyyyMMdd])\r\nSET @FileName = @CurrentDate + '_File_Name.csv'\r\nSET @PATH = REPLACE(\u2018c:\\somefile.csv\u2019, \u2018somefile.csv\u2019, @FileName) \r\nSET @bcpCommand = \u2018bcp \u201cSELECT \u201cFirst_Name\u201d as H1, \u201cMiddle_Name\u201d as H2, \u201cLast_Name\u201d as H3, \u201cAddress1\u201d as H4, \u201cAddress2\u201d as H5, \u201cCity\u201d as H6, \u201cState\u201d as H7\u2019\r\nSET @bcpCommand = @bcpCommand + \u2018UNION ALL SELECT First_Name, Middle_Name, Last_Name, Address1, Address2, City, State FROM tbl_Test\u201d QUERYOUT\u2019 + @Path +\u2019c \u2013T \u2013t, -U\u201dlogin\u201d \u2013P\u201dpassword\u201d\u2019\r\nEXEC master..xp_cmdshell @bcpCommand\r\n<\/pre>\nThe inclusion of the @FileName and @CurrentDate variables, coupled with a simple \u2018REPLACE\u2019 SQL statement will output 20120415_File_Name.csv file into our c:\\ drive. <\/p>\n
In the next few posts I will explain how to programmatically check if the file has been created. This could be nicely incorporated into this process when developing a larger or more complex solution.<\/p>\n","protected":false},"excerpt":{"rendered":"
In this post I would like to show how anyone can easily generate files (CSV, XML, TXT and more) through the use of bcp utility on Microsoft SQL Server. This technique can be easily incorporated into an SSIS package as part of its workflow as we can build on this solution to further post-process the […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5,50],"tags":[18,49,19,13],"class_list":["post-821","post","type-post","status-publish","format-standard","hentry","category-sql","category-ssis","tag-microsoft","tag-sql","tag-sql-server","tag-ssis"],"aioseo_notices":[],"jetpack_featured_media_url":"","_links":{"self":[{"href":"http:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/posts\/821","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/comments?post=821"}],"version-history":[{"count":12,"href":"http:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/posts\/821\/revisions"}],"predecessor-version":[{"id":844,"href":"http:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/posts\/821\/revisions\/844"}],"wp:attachment":[{"href":"http:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/media?parent=821"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/categories?post=821"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/tags?post=821"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}