Standard Export packages

Overview 
Edit section

The standard export (3.0) is a very simple export: it takes the data from the table Xport in the warehouse and writes everything in that table exactly as it exists in that table to a comma-separated-value flat file in a folder on the file system used by the 422x ftp.  This export is done with a SSIS package.

Packages for clients 
Edit section

Current 
Edit section

One package is used per client; this package hardcodes the name of the warehouse database and the name and folder of the flat file.  (It may be possible to change this in future versions.)  All of the packages are located in \\422rpt001\SSISPackages under the name [Client]Xport.dtsx.

Future 
Edit section

If there are no issues with calling the same package at once from multiple SQL Server Agent jobs, it will be possible to create only one package and to call it with the following parameters:

/CONN "Warehouse_database;Data Source=[Server];Initial Catalog=[Client]_Warehouse;Integrated Security=True;Application Name=StandardXport;"

/CONN "CSV_destination;\\422biz001\ftp\[Client]\[Client]_export.csv" 

Generically, this parameter is in the form /CONN "[connection manager name];[connection string]" and is only functional in recent versions of SQL Server. 

Development 
Edit section

The package is defined in the Warehouse export project located on 422rpt001: 

C:\Users\c422admin\Documents\Visual Studio 2008\Projects\Warehouse Export\Warehouse export.sln

New or recreated package 
Edit section

To create a package when no changes to the Xport table have been made, follow these steps:

  1. Open the Business Intelligence Development Studio using an account which has login access to the warehouse database and file permissions on the ftp folders (such as 422x\c422admin).
  2. Open the Warehouse export project.
  3. There is one package in this project called StandardXport.dtsx, and it has one control flow (Data Flow Task) and two connection managers (CSV destination and Warehouse source).
  4. Repoint the source by double-clicking on the Warehouse source connection manager to open it, then selecting the desired server name and database name. Click OK to accept the source.
  5. Repoint the destination by double-clicking on the CSV destination connection manager to open it, then entering the desired file path and name.  Standard naming conventions here are \\422biz001\ftp\[Client]\[Client]_export.csv. This file will not exist while developing, so it cannot be browsed for; the spelling must be exact; the folder can be verified after entering by clicking on the Browse button, although no file will be found. Click OK to accept the destination.
  6. Save and build the project.
  7. Click within the control flow or data flow panes to be sure that the focus is in the package object, then select Save Copy of StandardXport.dtsx As... from the File menu. (Note that this is different from saving the project or solution files as a new file, and this menu option is only available when the focus is in the package object.)
  8. In the save form, select File System for the package location, then click Browse... to select a path. The dialog box should open to C:\SSISPackages (browse to this folder if it does not). If you are recreating an existing package, select it from the list of files in this folder and confirm the replacement in the pop-up; if you are creating a new package, enter the name [Client]Xport.dtsx for the file name.
  9. Under protection level, select Do not save sensitive data.
  10. Click OK to save. If you are overwriting an existing package, you will be prompted again to confirm.

Structure 
Edit section

The structure of the standard export package is as follows:

One "Data Flow Task":

This task contains one "ADO NET Source" data flow source.

This data flow source uses the Warehouse source connection manager.

This data flow source uses the data access mode "Table or view" and selects the table "dbo"."Xport".

All other elements of this data flow source are to default.

This task contains one "Flat File Destination" data flow destination.

This flat file destination uses the CSV destination connection manager.

All other elements of this data flow destination are to default.

This task contains one data flow path from the data flow source to the data flow destination.

 All elements of this data flow path are to default.

One ADO.NET connection manager called "Warehouse source":

This connection manager uses the .Net Provider "SqlClient Data Provider".

This connection manager uses the server 422SQL001 or 422SQL002, as set for a particular client.

This connection manager uses Windows Authentication for its login.

This connection manager connects to a database named [Client]_Warehouse, as set for a particular client.

All other elements of this connection manager are to default.

One Flat File connection manager called "CSV destination": 

This connection manager uses the file name \\422biz001\ftp\[Client]\[Client]_export.csv, as set for a particular client.

This connection manager uses the format Delimited with the text qualifier " and the option selected for column names in the first data row.

All other elements of this connection manager are to default.
Comments