Loading Files
Loading Legacy Files into a SQL Table
Depending on your server configurations, there are several methods for loading a legacy file into a table.
If your SQL instance has the bulk copy program (BCP) enabled, you can use a utility in Generate to load the file directly into a table.
If your SQL instances does not have BCP enabled, you can use the Generate utility to create an empty table and then import the file using whatever method you prefer.
Generate expects 100 characters for every field. Errors will occur if SEA data's character counts go over 100 characters.
Create an Empty SQL Table for the Legacy File
Execute the following SQL statement in SSMS to create an EMPTY table for the file specification:
@DatabaseName
Is the name of the database where your table will be created. This can be the Generate database.
@SchemaName
Is the schema within the database where the table will be created. If the database is Generate, states often create a schema named as their state code (i.e., “VT”, “MT”, “IL”, etc.). This provides a separate location for these comparison files that do not interfere with other Generate tables.
@SubmissionYear
The year of the report
@ReportCode
The report code
@ReportLevel
The report level (SEA, LEA, SCH)
@Label
Identifier to distinguish that this is a legacy file. Typically, “Legacy” is used.
@ShowSQL
Used for debugging purposes if the utility fails to execute. Set this to 1 to view the dynamic SQL that is produced in this utility, otherwise set it to 0.
@CreatedTableName
An output parameter that will be returned from the procedure to show you the name of the table that is created
Loading a Legacy File When BCP is Enabled
If BCP is enabled on your server, you can execute the following SQL command to load the EDFacts file into the table created in the previous step:
@TargetTableName
The name of the table where the file should be loaded. This would be the table created in the previous step.
@SourceFilePathName
The fully qualified path to the file to be loaded.
@FileType
Can be “TAB” or “CSV”, depending on the format of the file being imported.
@ReportCode
The report code
@ReportLevel
The report level (SEA, LEA, SCH)
@Label
Some identifier to distinguish that this is a legacy file. Typically, “Legacy” is used.
@ShowSQL
Used for debugging purposes if the utility fails to execute. Set this to 1 to view the dynamic SQL that is produced in this utility, otherwise set it to 0.
You will need to remove the header row from the file prior to loading it into SQL Server.
Loading a Legacy File When BCP is NOT Enabled
If BPC is not enabled on your server, you will need to import the file into the table created earlier using whatever utility you have available. This could be the SQL Import tool or some other utility. Make sure to remove or ignore the header row when importing the file.
Loading Generate Files into a Table
The submission file comparison process was originally developed to require that the Generate submission files be created and then imported back into a SQL table to do the comparison to the Legacy files.
The process has been updated for several file specifications to not require those files to be loaded back into a SQL table – instead, the file submission process will use data directly from the Generate Reports tables to populate a comparison table.
As of Generate v11.4, the following files do NOT require that a Generate file be imported into a table to do a file comparison to a Legacy file:
DIRECTORY - FS029, FS039
CHILD COUNT - FS002, FS089
EXITING – FS009
DISCIPLINE – FS005, FS006, FS007, FS088, FS143, FS144
Generate v12 will include:
ASSESSMENT – FS175, FS178, FS179, FS185, FS188, FS189
For all other file specifications, follow the same steps as loading a Legacy file into a SQL table (previous section of this document), but switch the @Label parameter from “Legacy” to “Generate” and load the Generate-produced file into that table.
Last updated