Add logging to SSIS packages

Add the following code to the OnPostExecute, OnPreExecute, OnTaskFailed and onWarning events.

Use an expression for the SQLstatementSource

Add the following code to the OnError event: (difference is the errorCode value).

Use an expression for the SQLstatementSource

 

Create this table:

 

Use this query to build a log report

Importing SAS dataset into MSSQL using SSIS

Step 1.

Download and install the SAS OLE/DB Connector, here. http://support.sas.com/techsup/. Apparantly installing just a OLE/DB client still takes about 700MB’s these days. (I used the Client only setting)

Step 2.

In your SSIS package create a new OLE DB Connection Manager.
type: Native OLE DB\SAS Local Data Provider 9.3
In the Server name or file name textbox fill in the Path to the folder than contains the sas7bdat files. (or use advanced connection properties and fill in the Datasource ).
Enter V7 in the SAS file format (advanced properties) or something like this depending on your version

Step 3.

The files in theĀ folder specified in step 2 with sas7bdat extension are your tables. Create a new data flow task and place a OLE DB Source in here, when you click on name of table or view, you will see the filename.

Step 4.

Create an OLE DB Destination for MSSQL and map all columns. All datatypes should be retained.

Step 5.

Connect Source with destination.

Step 6.

Go with the flow. Execute package.

Step 7.

We discovered that date fieldsĀ are supplied as a float that represents the offset versus Jan-01-1960.