Configuration Import-Export Jobs
- Juan Brea (Deactivated)
- Nancy Ford
When re-exporting a file, make sure the FilePath is empty otherwise the data will get appended to the existing file resulting in duplicate data.
Make sure your account and server can reach the FilePath configured before deploying the packages
This article details the deployment and use of the 2 jobs used to export and import configuration tables into Benchmark Databases.
The Commit location on TFS is
$/Conversion/Integration_Pipeline/Import-Export/Import-Export
This contains two folders;
Admin Export: Contains the Export solution for exporting configuration tables into .csv Sheets
Admin Import: Contains the Import solution for Importing the .csv sheets into the target Database
Dependency
There is only one table for both jobs, which is PTG_ExportConfig, currently committed to
$/Conversion/Integration_Pipeline/Import-Export/Import-Export/Admin Export/Populate PTG_ExportConfig.sql
This table has the following columns:
ExportConfigID: Identity Column
FilePath: Used to indicate the target folder structure where the files will be generated to.
FileName: The name of the file. Must match the table it’s targeting and contain the format.
Active: Used to indicate whether the row is to be included in the next package execution.
Notes:
You’ll need to ensure that the SQL Account and network allow access to the specified FilePath.
Although the Export job supports exporting to multiple locations, the Import job only currently accepts one location per package. So to prevent any issues, it’s advised to use the same location until we can extend both packages to support multiple locations.
Deployment
After creating and populating the PTG_ExportConfig table, we’ll need to deploy the Export package (DTSX). Best practice would be to deploy the complete solution to the target environment and Deploying through Visual Studio, this would allow us to test the connection and package before deployment.
Otherwise, you may deploy directly to SSIS using the SSMS menu and importing the .dtsx packages. Whether we import as Separate projects or under a single project, we’ll end up with two .dtsx packages; one for Export and one for Import.
Exporting files
When re-exporting a file, make sure the FilePath is empty otherwise the data will get appended to the existing file resulting in duplicate data.
Once we have PTG_ExportConfig populated and the jobs have been deployed, we can start using the tools to move the configuration sheets.
Take the following set up:
RIght click the package and select Execute…
2. If this is the first time executing the package, we’ll need to set up the connection settings such as InitialCatalog, SQL UserName and SQL Password and click OK
2a. If this was deployed through Visual Studio, then the package will retain these parameters from the test, otherwise if the package was copied directly, they’ll have to be populated at this time.
3. You’ll receive a prompt asking if you’d like to see the Overview Report. This is not a necessary step, so you may click No.
3a. If you need to troubleshoot the package execution or you’d like to see the logs, you may always check the reports by right clicking the ExportPackage.dtsx and going to Reports>Standard Reports>All Executions
4. Check your exported files.
This is the end of the Export package.
Importing .CSV Sheets
Up next we’ll import the previously exported sheets.
Checking these tables are empty - even if they weren’t, they’d get truncated and re-populated.
2. The ImportPackage similarly to the export one is configurable. If this is the first time executing the package, we’ll need to set up the connection settings such as InitialCatalog, SQL UserName and SQL Password and click OK
2a. We can always change the TargetDatabase if we’re trying to import the configuration into a different database.
3. Check your target tables
Other use cases
Exporting all tables
The Export job needs to be expanded to move existing files to an Archive folder to prevent appending data when re-exporting tables if the file already exists.
The Import job needs to be expanded to support one FilePath per table rather than one per package so we can support Importing from multiple locations
Both packages will need to support checking if the FilePath is reachable by the Account running the package.
Add audit logs
How to add Export support for a table
The PTG_ExportConfig table only controls which table from the supported ones will get exported by the next execution. The supported list is the values in the Insert statement included in the table definition.
The Export job has 3 main containers:
Get Active list of Files from PTG_ExportConfig: Queries PTG_ExportConfig for Active rows and gets the list of files into an array.
Loop through list, create files: Loops through array, creates empty .csv files. Because SSIS is finnicky about exporting data if the target file doesn’t already exist.
Load data into files: Because the container won’t know which tables are active for each execution, we need to check if a target file exists for each one before we dump data into it.
Most of the magic happens in the third container, if we wanted to add support for a new table, we’ll have to add a Script task to check if a file exists. If we were creating one for tblStatute:
Create a Boolean variable to hold the result for checking if the file exists. In this case, we’ll name this one tblStatuteExists
Create a Script Task, with the ReadOnlyVariables User::FileName, User::FolderPath and the ReadWriteVariables User::tblStatuteExists
1a. User::FolderPath and User::FileName are passed by the first container, where we read the folder path from the table and pass it to the package.
1b. User::FileName isn’t really used in the Script logic, so we could leave it out with no impact but I’ve left it in for consistency’s sake with the other task and because we might use it later.
1c. User::tblStatuteExists is the variable we’ll write the result of the check into, and what we’ll use in the control flow to determine if we should write the data into a file or not.
2. Click on Edit Script…
3. Line 96: Add a line with a Filepath variable containing a reference to the User::FolderPath and a hardcoded name to the file, in this case ‘tblStatute.csv’
3a. Lines 98 to 110 I’ve added a Kill process command to close the connection to the created file, otherwise the script task will remain open and the next task of loading the data into the file will fail due to the file being locked.
4. Next we can add a regular data flow task with a regular OLE DB source and Flat File Destination
5. We need to add a precedence constraint on the tblStatute task. right click the connector between the script and the data flow task and click Edit…
5a. Here we’ll use the ReadWriteVariable tblStatuteExists we’ve populated in the previous script task. This will only run the Data Flow task if a file was created, which should have only happened if the Active column was set to 1.
6. Right click the connection for the Flat file used in your data flow target and click on Properties
6a. On the right panel, you’ll see the property “Expressions”, click the ellipsis …
6b. Find the connection string property and click the ellipsis again to bring up the Expression editor
6c. Here we’ll drag (or type) into the Expression editor the project variable @[User::FolderPath] and add + “tblStatute.csv” this will concatenate the FolderPath we’re reading from the configuration table to the hard-coded name ‘tblStatute.csv’ name.
7. Lastly, we’ll need to add our newly supported table to the PTG_ExportConfig list of values that we can control, so the job knows to export it next time it runs.
How to add Import support for a table
The Export job has 3 main containers:
Get Active list of Files from PTG_ExportConfig: Queries PTG_ExportConfig with DISTINCT FilePath, this is why this job only supports one location. If we have multiple locations in that table, it will break the job!
Sequence Container: It checks if a file exists, if it does then Drop Constraints for that table and Truncate it.
Sequence Container: It checks if a file exists, if it does then Import the file into the SQL table.
Exec Readd: Adds dropped constraints.
*Containers 2 and 3 had to be separated to prevent locks from Dropping Constraints.
The process to add support for importing tables here will be the same as the Export file, except there will be 2 script tasks per table; One for checking if file exists prior to truncating the target table and another one for checking if the file exists prior to running the Data Flow task to Import the data.