/
Miami CJS Source Tables Import Package

Miami CJS Source Tables Import Package

Owned by Juan Brea (Deactivated)
Last updated: Mar 21, 2023
3 min read

This Documentation details the configuration steps for adding new tables to the Source Tables Importer deployed to Miami.

 

Objects

 

Variable

The first thing to note is the Package string variable FolderPath, this contains the path where all the files will be contained. This value can be updated to point to the files accordingly to the phase of the project, Pass 1, pass 2, etc. This variable is later referenced by the connection managers to dynamically find the files.

FolderPath variable containing the path with the location for the source files to be imported.

 

A general overview of the complete control flow, showcasing the tasks and connection managers.

 

The first table in the package CJS5101-USER, showing the two tasks.

 

Tasks

The Control flow tab where all the tables are imported contains 2 steps for each table; an Execute SQL task and a Data Flow task.

 

  • Execute SQL Task: Truncates the target table prior to importing the file contents.

  • Data Flow Task: Contains the column mappings between the file and the target table. References the file-specific Connection manager.

    • Flat File Connection Manager: Contains the location of the target file to be imported. When right-clicked

 

Initial configuration for the CJS5101-USER Flat File Connection Manager.

 

Connection Manager

ConnectionString Expression is parametrized to reference the FolderPath Variable + the text file name.

 

 

Adding a new table

To add a new table, the process is as follows:

  1. Create an Execute SQL Task, with SQL code to truncate the target table.

    1. If the targe table doesn’t already exist in the Source DB, I’d advise Importing the file using the SQL Import-Export Wizard first, this will create the table with the data types with minimal manual input.

  2. Create a Data Flow Task, with a new source flat file reference and a Destination reference to the SQL table.

    1. Create a Connection Manager the name should match the file to make it easier to distinguish in the Connection Manager view within Visual Studio.

  3. Right click on the created Connection Manager > Properties > Expression > Add the variable @[User::FolderPath] + “CJSXXXX-FileName.TXT“. This will allow the file location to be flexible.

 

 

Initial commit location:

\TFS\Conversion\MiamiDade\MiamiDadeCJISSourceImport

 

Related content