Working with Databases

The examples which follow illustrate different uses of retrieving and passing data from a database perspective.

 

Database Jobs

 

Copy Database Records

 

In this example, we’re going to copy selected records from one database to another.

 

We’ll name the job “CopyDB2DB” and place it in a newly created folder named “DatabaseJobs” within “CaseStudy4”.

 

Here is an overview of the Job Steps we’ll be using:

 

 

We connect to both the old and new databases (Connect_). We drop and create a new table in the new database. For each row we insert that row into the new database. We then disconnect from the databases.

 

Let’s take a closer look at some of the Job Steps.

 

1. Note that some of the Job Step names have been renamed to be more readable as to what is being done. The only requirement when renaming a Job Step is that the new name be unique and obey the proper character restrictions for labels. So Connect_OLDDB is easier to understand than Connect or Connect_2.

2. The CONNECT_OLDDB job step contains a “Connection String” of “Provider=SQLOLEDB.1;Initial

   Catalog=${dbdatabase};Data Source=${dbserver};Integrated Security=SSPI;Persist Security Info=False;”. Note the use of the active variables. This step on success will pass back a connection handle that can be used by subsequent database steps.

   

3. The CONNECT_NEWDB is very similar except the “Initial Catalog” parameter is the name of the new database (in our case AB-UserGuideNew).

4. The DropsNewTable job step looks like this:

   

   Note the Execution Variable usage for the connection handle (which was returned by the Connect job step). Next, by using a variable “DbTable” this job step can be reused by other workflows.

5. The CreatesNewTable job step looks like this:

   

   

6. ZeroRecordCount step is a simple ActiveBatch Expression which creates and initializes CountOfRecords=0.

7. ForEachRow is an iteration step.

   

   We execute a SQL statement to select all data from the existing CustomerTable. That resultant set is placed in the execution variable of “Row” for each iteration of the set. The “InsertRow” step is within the loop and will be executed for each row.

   

   Above is the SQL statement for the Insert operation. Note that execution variable substitution is now performed (%{ }). In this case Row contains the structure of the database. Generally speaking the greater the use of variables the more generic and reusable a job step and then a job, becomes.

   The remaining step within the iteration is self-explanatory. AddRecordCount contains a “CountOfRecords=CountOfRecords+1;” to add up each time we iterate.

8. The “Log” step, which is outside the iteration is executed when the loop is done. This step will display the number of records (rows) we’ve processed.

9. The “Disconnect” job steps perform final cleanup and disconnect from their respective databases.

 

Loading a Database

 

The following example is a slight variation on a theme from the previous example. In this example, we will connect to a new database, drop/create our customer table (in case we want to run this job multiple times) and then load data from a text file into the database. All of the job steps we use will be SQL Server based instead of Generic Database steps.

 

 

The ReadTextFromFile step reads each line from a text file where the line is a comma-separated list of fields corresponding to the database fields.

 

Note the use of the Split function to parse each field for insertion into the database.

 

SSIS Package

 

This example job demonstrates the running of an SSIS package with several FileSystem steps. Create a job named “SSISCatalog” with Job Type “Jobs Library”.

 

Two (2) variables will be needed.

 

InputFile should contain the proper file specification of an input file.

 

OutputFile should contain the proper file specification for an output file.

 

 

1. The initial IFfile step determines if the output file already exists and if so, deletes it.

 

2. The SSIS package job step. This particular SSIS package will invoke an SSIS Catalog. The server, package and environments are specified. The parameters tab would contain, at a minimum, the Input and Output File parameters.

 

 

3. Once the SSIS job step completes, we access the Output file that should be newly created. We use the GetFileInfo job step and in the Log step we log the last date/time the file was written to.

    

 

4. The last step consists of a ReadTextFromFile step. Note that with no delimiter specified, the entire file will be read and assigned to the variable “Object”, which is then written out to the Job’s log file.