Backup job aanmaken in SQL agent

Make a new job and add the following T-SQL step:

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.

 

 

 

 

 

Generate a date dimension in T-SQL

with date_gen as(

select cast(‘2005-01-01’ as datetime) d

union all

select d + 1

from date_gen

where d + 1 < ‘2079-01-01’

)

select d date_id, CONVERT(varchar, d,107) date_description,

YEAR(d) *100 + MONTH(d) date_year_month, YEAR(d) date_year,

MONTH(d) date_month, datename(month, d) date_month_name

from date_gen

OPTION (MAXRECURSION 0)

Performance monitoring SQL Server (EN)