Export SQL Server object Result set to different destinations (CSV/Excel/Text) Using BCP

Hi,

  Today i came across the topic : How to Export the SQL Result set into different output files like Excel/CSV/Text.

  we have different methodologies to select the SQL result set into different destinations. But for
  Now, i am showing how can we export the data from SQL Server to csv file using bcp (Bulk Copy Program).

Standard BCP format looks like:

 bcp [database_name.] schema.{table_name | view_name | "query"

    {in data_file | out data_file | queryout data_file | format nul}
                                                                                                         
    [-a packet_size]
    [-b batch_size]
    [-c]
    [-C { ACP | OEM | RAW | code_page } ]
    [-d database_name]
    [-e err_file]
    [-E]
    [-f format_file]
    [-F first_row]
    [-h"hint [,...n]"]
    [-i input_file]
    [-k]
    [-K application_intent]
    [-L last_row]
    [-m max_errors]
    [-n]
    [-N]
    [-o output_file]
    [-P password]
    [-q]
    [-r row_term]
    [-R]
    [-S [server_name[\instance_name]]
    [-t field_term]
    [-T]
    [-U login_id]
    [-v]
    [-V (80 | 90 | 100 | 110 | 120 | 130 ) ]
    [-w]
    [-x]

Now the work around the task:

Create one test table.

CREATE TABLE [dbo04].[ExcelTest](
    [ID] [int] NOT NULL,
    [Float] [float] NULL,
    [Datetime] [datetime] NULL,
    [Nvarchar] [nvarchar](255) NULL
)

Test Data:



INSERT [dbo04].[ExcelTest]
    ([ID], [Float], [Datetime], [Nvarchar])
VALUES
    (1, 123.4567, '20110617 01:00:00', N'Hello, SQL Server!')
    (1, NULL, NULL, N'Hello, Excel!')


Expected Results in the CSV file:

Id,Float,Datetime,Nvarchar
1,123.4567,2011-06-17 01:00:00,"Hello, SQL Server!"
2,,,"Hello, Excel!"

We can not directly run the BCP in command prompt to export result set from sql to file. if so, will not get proper required format data.

For this, first i have created on script to select the data as shown in below.

SET NOCOUNT ON     -- Used to not allow the number of rows selected
SELECT
[ID] AS [ID],
[Float] ,
convert(varchar(19), [DateTime], 120) as [DateTime],
Quotename([nvarchar],'"') as [Nvarchar]
FROM ExcelTest

Also, create one path for destination file store on your local machine and place the sql script file over there.

Run the below script under command prompt.

C:\vpeda\Test_Files\test>sqlcmd -S "CDTSSQL385D\MSSQL2K8R2" -d "Test_DB" -E -s "
|" -W -i test.sql | findstr /V /C:"-" /B>ExcelTest10.csv

Parameters:
-S  : Server with instance
-d  : Database Name
-E  : Trusted Connection
-s   : Delimiter specification (Column Seperator)
-W : Space removal
-i   : Input data script file (if you are not running in local drive then give full path of the file)
findstr /V /C: remove the special characters
-o or >  : Output file  (if you are not running in local drive then give full path of the file).


Reference: https://www.excel-sql-server.com/sql-server-export-to-excel-using-bcp-sqlcmd-csv.htm#SQL

Thanks!
Venky

SSIS - Flat File Connection Manager Behavior when all the columns in a data row don't have entries

I hope some of you might not have faced with this feature of Flat File Source Connection Manager in SSIS, where Flat File Source cannot handle the delimited data if the column values are restricted to only some of the columns in each row.

Consider you have data in the file as below:

0123^4567^01^20171401^XXX^V1^2345.89^xyzabcd

0123^4567^01^20171401^XXX^V1

0123^4567^01^20171401

0123^4567^01^20171401^XXX^V1^2345.89^xyzabcd

When we create Flat File Connection Manager in SSIS, use above file data as source.

With Column Delimiter as “^ “and Row Delimiter as “{CR}{LF} “.

Once the configuration has been made and when you see Preview it shows only 3 Rows and not all the 4 Rows.

This behavior is because of the fact that SSIS Column Delimiter gets first preference and then Row Delimiter.

Consider our File Data Now : 

0123^4567^01^20171401^XXX^V1^2345.89^xyzabcd

Totally there are 7 “^” s, So Connection Manager Expects 7 “^”s and the above row will be shown normally.

0123^4567^01^20171401^XXX^V1

In this row it finds only 5 “^”s, so it thinks the row is not yet over and continues till it finds two more “^” s. It will ignore the row delimiter as it is on its way to find two more “^”s.

0123^4567^01^20171401

Here it will append this row to previous row as it thinks previous row is incomplete and after two “^”s once it reaches 7^th “^”, It will append rest of the data in this row up to row delimiter to previous row.

0123^4567^01^20171401^XXX^V1^2345.89^xyzabcd

For this row, everything is normal hence it will be shown correctly as one full row.

So if you do the preview with this file you will see as below now,

0123^4567^01^20171401^XXX^V1^2345.89^xyzabcd

0123^4567^01^20171401^XXX^V10123^4567^01^20171401

0123^4567^01^20171401^XXX^V1^2345.89^xyzabcd

In this case, what needs to be done is :-

 SET AlwaysCheckForRowDelimiters Property of connection manager to true (If it's not set by default) 

and you will see all rows behaving normally when you do the preview.

By default, the flat file connection manager always checks for a row delimiter in unquoted data and starts a new row when a row delimiter is found. This enables connection manager to correctly parse files with rows that are missing column fields.

In some cases, disabling this feature may improve the package performance. You can disable this feature by setting the flat file connection manager property, AlwaysCheckForRowDelimiters, to False.

This is not present in documentation till SQL 2008 R2 SSIS.

NOTE : If the file data is as above and AlwaysCheckForRowDelimiters Property is set to False Then the output data you will be getting same as in preview for most of the rows and the data flown across multiple steps in SSIS flow will go wrong.

Download Files from Web using SSIS

Often we are required to load data from files (text/CSV/excel etc.). Sometimes these files are available as a download in a web server. In such cases the using a script task in SSIS comes in handy to download the files. Here is the code snippet to download the files from web in a Script Task in SSIS

Firstly, we need to create a couple of variables,

1.     Download URL path

2.     File Save Path

Once these are created, one can easily use configurations to make these variables configurable at run time. Now coming to code part where we are downloading the files,

Firstly we need to add assembly reference to System.Net namespace

using System.Net;

Write a function as below

private bool DownloadFile(string strFileName)
{

bool retVal = false;

bool fireAgain = true;

WebClient wc = new WebClient();

string strServerFilePath = "download_url from SSIS variable goes here"+strFileName;

string strLocalFilePath = "File Save Path from SSIS variable goes here"+strFileName;

wc.Credentials = new NetworkCredential("username", "password");

try

{

wc.DownloadFile(strServerFilePath, strLocalFilePath);

retVal = true;

}

catch (WebException ex)

{

fireAgain = false;

Dts.Events.FireInformation(0, "Downlad File", "Download failed: " + ex.Message, string.Empty, 0, ref fireAgain);

retVal = false;

}

return retVal;

}

Call this function from the Main function in the Script Task and verify if the function returns a true/false, and handle the load accordingly.