Loading SAS Dataset in a table using SSIS

One of my client’s vendor is giving the SAS data. We need to load that data into repo table.

In this post, we will see how to load the SAS dataset into the repo table.

First of all, we need to download and install the SAS Providers for OLE DB. There are multiple versions make sure to download the correct version. We only need the select SAS Providers for OLE DB.

Download link:  https://support.sas.com/downloads/index.htm

Before downloading the provider we need to do the registration on this site.

Select the version of the provider and download it.

Once we download this e need to install this provider.

For the demo, I have downloaded some SAS data set from the below site.

http://www.principlesofeconometrics.com/sas.htm

Now let ‘s see how to lading the SAS dataset.

Open the SSDT.

Taking Data flow task.

Double click on the data flow task.

In data flow pan I am taking OLEDB source.

Here we need to create connection manager. Double click on the OLEDB Source.

Here we need to select the provider as SAS Local Data Provider.

Click ok.

                         

Here we need provide the file location where the SAS dataset is stored.

If that folder is password protected we need to provide the password. Otherwise, we need to keep it blank.

Click on the test connection.

Click ok.

Select the Data access mode as table or view.

                        

In the drop down we will get the all SAS dataset list which are stored on that folder. We need to select the SAS set file which we want to load.

Now select Column.

Click ok.

Now data is loaded in the source.

If we to do any transformation we can do it.

Here I am taking the multicast transformation and enabling the data view to see the records.

Now I am running this package.

We can use destination to load the data.