...
Create an Execute SQL Task, with SQL code to truncate the target table.
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.
Create a Data Flow Task, with a new source flat file reference and a Destination reference to the SQL table.
Create a Connection Manager the name should match the file to make it easier to distinguish in the Connection Manager view within Visual Studio.
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