May 30, 2020

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.

To get in depth knowledge on Microsoft business intelligence, enrich your skills onmsbi online training professionals

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.

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.Get more skills from Msbi online course

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.

I hope you reach a conclusion about Datasets and data sources in MSBI. You can learn more through Msbi online training Hyderabad.