﻿Welcome to the WSI DB Export Utility

There are two files of interest:

1) The WSIDBExportUtility.exe.config file contains the main configuration settings for connecting to the SQL Server database you plan to get data from

Below is an example of where I am connecting to a SQL Server instance called GEORGE-DEV\SQL2019 and specifying a database called esm_import_neon_base.
In this case I am specifying automatic login using my Windows user (Integrated Authentication).  Optionally you can specify a SQL ID user/encrypted password here to connect.

    <!-- SQL Server - Connection Info -->
    <add key="instance_name" value="GEORGE-DEV\SQL2019" />
	<add key="database_name" value="esm_import_neon_base" />

    <!-- SQL Server - Either use Integarted Authentication OR SQL ID and Encrypted Password -->
    <add key="use_integrated_authentication" value="yes" />
    <add key="user" value="" />
    <add key="enc_password" value="" />

The next part of this config file is what ExportProfiles.xml are you using:
	<!-- Which Profiles To Export? Ex: "ALL" (all profiles) or CSV list of names to export -->
	<add key="export_xml_file_name" value="\ExportProfiles\ExportProfiles.xml"/>
	<add key="profiles_to_export" value="ALL" />

At some sites you may want to use multiple ExportProfiles to specify what to export and where.
By default all profiles are exported, but you can trace a single profile by simply specifying the profile name in the "profiles_to_export" parameter.

2) The ExportProfiles.xml file (can be called something else if you like) specifies either the queries or where to find the query (file path), and what to export it to.
We highly recommend SQLite DB as you won't need to worry about special characters messing up the export.
Ex: CSV files with commas, carrige return/line feed, etc.  OR TAB delimited CSV files with tabs in the data.  These are examples of why you should avoid this.

Here are some basic examples of what your XML file could look like:
Here's a simple SQLite DB export specify 2 SQL files which will create 2 tables in the SQLite DB file.

Note: The staging_path parameter should be used with SQLite databases if the final location is going to be placed on a network share/mapped drive.
The reason for this is SQLite connections only work on physical local drives.

Note: The table_name parameter must be unique...otherwise one profile may override the exported data from another.

Note: Not all XML elements needs to be present or supplied.
	- If you omit query for example, then you need to specify sql_file_path.
	- If you are specify a SQLite DB output, then you don't need to specify delimiter (or you can just leave it blank)

=======Example 1 - SQLite DB Output, Using File Path For Queries=======

<profiles
				staging_path="C:\staging\my_sqlite_database_export.db"
				output_path="\\file-server1\share1\final_sqlite_database_export.db"
				>
	<profile name="profile1">
		<table_name>export_table1</table_name>
		<sql_file_path>C:\SQLQueries\profile1.sql</sql_file_path>
	</profile>
	<profile name="profile2">
		<table_name>export_table2</table_name>
		<sql_file_path>C:\SQLQueries\profile2.sql</sql_file_path>
	</profile>
</profiles>


=======Example 2 - SQLite DB Output, Using Queries=======


<profiles
				staging_path="C:\staging\my_sqlite_database_export.db"
				output_path="\\file-server1\share1\final_sqlite_database_export.db"
				>
	<profile name="profile1">
		<table_name>export_table1</table_name>
		<parameters>
			<year>2024</year>
		</parameters>
		<query>
			SELECT *
			  FROM some_table_or_view
			 WHERE year = [year]
		</query>
	</profile>
	<profile name="profile2">
		<table_name>export_table2</table_name>
		<query>
			SELECT *
			  FROM another_table_or_view
			 WHERE year = 2023
		</query>
	</profile>
</profiles>

=======Example 3 - CSV File Output=======

<?xml version="1.0" encoding="utf-8" ?>
<profiles>
	<profile name="profile1">
		<output_path>C:\Exports\profile1.csv</output_path>
		<delimiter>,</delimiter>
		<query>
			SELECT *
			  FROM some_table_or_view
			 WHERE year = 2024
		</query>
	</profile>
	<profile name="profile2">
		<output_path>C:\Exports\profile2.csv</output_path>
		<delimiter>|</delimiter>
		<sql_file_path>C:\SQLQueries\profile2.sql</sql_file_path>
	</profile>
</profiles>