FastWrappers-TSQL/xp_RunFastBCP_secure.sql at master · aetperf/FastWrappers-TSQL · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117

-- Sample usage of the xp_RunFastBCP_secure stored procedure
-- EXEC dbo.xp_RunFastBCP_secure
--     @fastBCPDir = 'D:\FastBCP\latest',
--     @connectionType = 'mssql',
--     @sourceserver = 'localhost',
--     @sourceuser = 'FastUser',
--     @sourcepasswordSecure = 'wi1/VHz9s+fp45186iLYYQ==',
--     @sourcedatabase = 'tpch_test',
--     @sourcetable = 'nation',
--     @outputFile = 'nation.csv',
--     @outputDirectory = 'D:\temp\fastbcpoutput\nation',
--     @delimiter = '|',
--     @usequotes = 1,
--     @dateformat = 'yyyy-MM-dd HH24:mm:ss',
--     @encoding = 'utf-8',
--     @method = 'None',
--     @runid = 'test_FastBCP_export_nation'


-- EXEC dbo.xp_RunFastBCP_secure
--     @fastBCPDir = 'D:\FastBCP\latest',
--     @connectionType = 'mssql',
--     @sourceserver = 'localhost',
--     @sourceuser = 'FastUser',
--     @sourcepasswordSecure = 'wi1/VHz9s+fp45186iLYYQ==',
--     @sourcedatabase = 'tpch_test',
--     @query = 'SELECT top 1000 * FROM orders',
--     @outputFile = 'orders_output.csv',
--     @outputDirectory = 'D:\temp\fastbcpoutput\',
--     @delimiter = '|',
--     @usequotes = 1,
--     @dateformat = 'yyyy-MM-dd HH24:mm:ss',
--     @encoding = 'utf-8',
--     @method = 'None',
--     @runid = 'test_FastBCP_export_orders'

-- EXEC dbo.xp_RunFastBCP_secure
--     @fastBCPDir = 'D:\FastBCP\latest',
--     @connectionType = 'mssql',
--     @sourceserver = 'localhost',
--     @sourceuser = 'FastUser',
--     @sourcepasswordSecure = 'wi1/VHz9s+fp45186iLYYQ==',
--     @sourcedatabase = 'tpch_test',
--     @query = 'SELECT top 1000 * FROM orders',
--     @outputFile = 'orders_output.csv',
--     @outputDirectory = 'D:\temp\fastbcpoutput\orders\physloc',
--     @delimiter = '|',
--     @usequotes = 1,
--     @dateformat = 'yyyy-MM-dd HH24:mm:ss',
--     @encoding = 'utf-8',
--     @method = 'Physloc',
--	   @degree = 8,
--     @runid = 'test_FastBCP_export_orders_physloc'

-- EXEC dbo.xp_RunFastBCP_secure
--     @fastBCPDir = 'D:\FastBCP\latest',
--     @connectionType = 'mssql',
--     @sourceserver = 'localhost',
--     @sourceuser = 'FastUser',
--     @sourcepasswordSecure = 'wi1/VHz9s+fp45186iLYYQ==',
--     @sourcedatabase = 'tpch_test',
--     @query = 'select * from (SELECT *, year(o_orderdate)*100+month(o_orderdate) as o_ordermonth FROM orders where o_orderdate >= ''19980101'') src',
--     @outputFile = 'orders_output.parquet',
--     @outputDirectory = 'D:\temp\fastbcpoutput\orders',
--     @delimiter = '|',
--     @usequotes = 1,
--     @dateformat = 'yyyy-MM-dd HH24:mm:ss',
--     @encoding = 'utf-8',
--     @method = 'DataDriven',
--     @distributeKeyColumn = 'o_ordermonth',
--     @runid = 'test_FastBCP_export_orders',
--     @debug=1


CREATE PROCEDURE [dbo].[xp_RunFastBCP_secure]
@fastBCPDir [nvarchar](1000),
@connectionType [nvarchar](30),
@sourceconnectStringSecure [nvarchar](4000) = NULL,
@sourcedsn [nvarchar](255) = NULL,
@sourceprovider [nvarchar](1000) = NULL,
@sourceserver [nvarchar](255),
@sourceuser [nvarchar](1000) = NULL,
@sourcepasswordSecure [nvarchar](255) = NULL,
@isTrusted [bit] = 0,
@sourcedatabase [nvarchar](1000),
@inputFile [nvarchar](1000) = NULL,
@query [nvarchar](4000) = NULL,
@sourceschema [nvarchar](255) = NULL,
@sourcetable [nvarchar](255) = NULL,
@outputFile [nvarchar](1000) = NULL,
@outputDirectory [nvarchar](2000) = NULL,
@delimiter [nvarchar](10) = NULL,
@usequotes [bit] = 0,
@dateformat [nvarchar](25) = NULL,
@encoding [nvarchar](50) = NULL,
@decimalSeparator [nvarchar](1) = NULL,
@degree [int] = -2,
@method [nvarchar](50) = 'None',
@distributeKeyColumn [nvarchar](1000) = NULL,
@datadrivenquery [nvarchar](4000) = NULL,
@mergeDistributedFile [bit] = 0,
@timestamped [bit] = 0,
@noheader [bit] = 0,
@boolformat [nvarchar](10) = NULL,
@runid [nvarchar](255) = NULL,
@settingsfile [nvarchar](4000) = NULL,
@cloudprofile [nvarchar](2000) = NULL,
@license nvarchar(4000) = NULL,
@loglevel nvarchar(50) = 'Information',
@debug [bit] = 0
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [FastWrappers_TSQL].[FastWrapper.FastBCPCLR].[RunFastBCP_Secure]
GO