How to download File from SharePoint and Add Datetime to it in SSIS Package by using Script Task - SSIS Tutorial / SSIS Interview Questions

Scenario:

You are working as ETL/ SSIS Developer. You need to write an SSIS Package that should download the file from SharePoint site. Every time you run the SSIS Package it should download the file and add date_time to it.

Solution:

We will be using HTTP Connection and Script Task to download the file from SharePoint.

Step1 :

Create your SSIS Package by using BIDS or SSDT ( SQL Server Data Tools).

Step 2: 

Create Three variables of string type

FolderPath : 

Provide the folder in which you want to download the file.

FileName : 

Provide the destination file name with extension such as myfile.xlsx or mytestfile.doc

FileFullPath : 

This is the variable we will be using to write expressions on it. Click on the expressions tab and then write the below expressions.

How to download file from SharePoint and Add Datetime to it in SSIS Package

Once you click on the Expression Button as shown above, paste the below expressions.

@[User::FolderPath]+ Replace( @[User::FileName],".", "_"+Replace(Replace(Replace(SUBSTRING((DT_WSTR,50)(GETDATE()),1,19),"-","")," ","_"),":","")+".")

Write expressions on variable in SSIS Package to add Date_Time to it 

Step 2:
To create the HTTP Connection to SharePoint File. Go to Connection Managers Pane and then right click and then choose New Connection.

How to create HTTP Connection Manager to SharePoint to download File in SSIS Package

How to download file from SharePoint by using SSIS Package by using Script Task with HTTP Connection

How to create Connection Manager to SharePoint in SSIS Package

To get the Server URL ( Path to file on SharePoint). You can go to SharePoint and then go to File and Right Click, Choose Properties and then you will see Address (URL) that is the Server URL you need to use in Connection above. Also provide the user name and password which has permission on the SharePoint File. Test the connection and it should be successful.

A new connection should be created once you click OK. You can rename if you like,I am going to leave this as it is.

Step 4: 
Bring the Script task in Control Flow Pane and open, you need to add the variable.

How to use Script Task to download File from SharePoint in SSIS Package

Step 5:
Click on Edit Script Button and then paste below script under public void Main() {

   string FilePath = Dts.Variables["User::FileFullPath"].Value.ToString();
   object obj = Dts.Connections["HTTP Connection Manager"].AcquireConnection(null);
   HttpClientConnection HTTPConnection = new HttpClientConnection(obj);
   HTTPConnection.DownloadFile(FilePath, true);

Save the script and close the Editor windows. Run your SSIS Package, It should download the file and add date_time to file name. Every time you will run the SSIS Package , the new file will be downloaded and date_time will be added to it.

I executed the SSIS Package couple of times and you can see that the file is downloaded and date_time is added to it.

How to download files from SharePoint and add Date_Time on each execution in SSIS Package

How to download file from SharePoint by using SSIS Package - SSIS Tutorial / SSIS Interview questions

Scenario:

You are working as SSIS Developer and you need to download the file from SharePoint on daily basis, if the file exists in the folder you want to overwrite with new file. The file name that you need to download from the SharePoint stays the same.

Solution:

We will be using the Script Task and HTTP connection Manager in SSIS Package to download the file and write to folder in SSIS Package.

Step 1: 

Create a new SSIS Package by using BIDS or SSDT ( SQL Server Data Tools).

Step 2:

Create a variable called FilePath where you would like to save the file on local folder.

How to download File from SharePoint and Save to Local Folder in SSIS Package by using Script Task

Step 3:
Create the HTTP Connection to SharePoint File. Go to Connection Managers Pane and then right click and then choose New Connection.

How to create HTTP Connection Manager to SharePoint to download File in SSIS Package

How to download file from SharePoint by using SSIS Package by using Script Task with HTTP Connection

How to create Connection Manager to SharePoint in SSIS Package

To get the Server URL ( Path to file on SharePoint). You can go to SharePoint and then go to File and Right Click, Choose Properties and then you will see Address (URL) that is the Server URL you need to use in Connection above. Also provide the user name and password which has permission on the SharePoint File. Test the connection and it should be successful.

A new connection should be created once you click OK. You can rename if you like,I am going to leave this as it is.

Step 4: 
Bring the Script task in Control Flow Pane and open, you need to add the variable.

How to download file from SharePoint to Local Folder by using Script Task in SSIS Package

Click on Edit Script Button and then paste below script under public void Main() {

   string FilePath = Dts.Variables["User::FilePath"].Value.ToString();
   object obj = Dts.Connections["HTTP Connection Manager"].AcquireConnection(null);
   HttpClientConnection HTTPConnection = new HttpClientConnection(obj);
   HTTPConnection.DownloadFile(FilePath, true);  

Save the script and close the Editor windows. Run your SSIS Package, it should download the file. Every time you will execute the SSIS Package, it will overwrite the existing file from SharePoint file.

How to Run Stored Procedure Against Multiple Databases in SQL Server - SQL Server / TSQL Scripts

Scenario:

You are working as SQL Server developer. The company you are working create a new database for each of the client with same objects such as tables/Stored Procedures. You have one stored procedure with the same name let's say dbo.LoadCustomer present in all the databases. You need to execute that Stored Procedure from multiple databases. The script can be used one time or you can also run by using SQL Server agent if has to run on schedule.

Solution:

We can use Cursor to loop through list of the databases in which our Stored Procedure is present and we want to execute. Below is sample script. Please change the @SPName and filter the list of database as per your requirements.

--Use Cursor to Loop through Databases
DECLARE @DatabaseName AS varchar(500)
--Provide the name of SP that you want to run
DECLARE @SPName AS varchar(128) = 'dbo.loadCustomer'

DECLARE DBCursor CURSOR FOR
--Filter the list of the database in which Stored Procedure exists
SELECT
  NAME
FROM sys.databases
WHERE database_id > 4

OPEN DBCursor

FETCH NEXT
FROM DBCursor
INTO @DatabaseName

WHILE @@FETCH_STATUS = 0
BEGIN
  DECLARE @DBName AS nvarchar(500);

  SET @DBName = QUOTENAME(N'' + @DatabaseName + '');

  --USE Dynamic SQL To change the Database Name and 
  --Execute Stored Procedure from that Database
  EXEC (
  N'USE ' + @DBName + N'; EXEC(''' + @SPName + '
'');'
  );

  FETCH NEXT
  FROM DBCursor
  INTO @DatabaseName
END

CLOSE DBCursor

DEALLOCATE DBCursor

How to see Created Date for Files and Folders in Windows Explorer

When we often Windows Explorer we see different properties and one of them we see for file and folder is Modified Date.

Sometime we need to know when the file or folder was created and we would like to see Date Created. To add that Right Click right next to Size or any other property as shown and then click on Created On as shown below.

How to show Created Date for file and folders in Windows Explorer

The Date Created should be added to list now.

How to see Created Date for Folders and Files in windows explorer

How to delete all files except current date files in SSIS Package - SSIS Tutorial

Scenario:

You are working as ETL Developer or an SSIS developer, you need to write an SSIS Package that should be able to delete all the old files from a folder and just leave the current day files.

How to Delete all files from a folder except Files created today in SSIS Package by using Script Task

Solution:

We can get the Date Created for the file and if it is created today we will not delete , otherwise we will delete the file.

Step 1: Create an SSIS Package by using SSDT

Step 2: Create a variable called SourceFolder and provide the path for folder in which your files exists.

How to drop all old files from folder just leaving for current day files in SSIS Package by using Script Task

Step 3: 
Bring the script Task to the Control Flow pane and add SourceFolder variable to it as shown below.

How to drop old files from a folder by using SSIS Script Task except Current Day files

Step 4 : 
Click on Edit Script and then use the page the below script.

Click Edit Button and it will open Script Task Editor.

Under #region Namespaces, I have added below code

using System.IO;

Go to public void Main() and Paste below Code right under // TODO: Add your code here

           var directory = new DirectoryInfo(Dts.Variables["User::SourceFolder"].Value.ToString());
            FileInfo[] files = directory.GetFiles();
            
            foreach (FileInfo file in files)
            {
                if (file.CreationTime < DateTime.Today)
                {
                    //MessageBox.Show(file.Name);
                   file.Delete();
                }
            }

Step 5:
Save the script and then close the Editor window. Your SSIS Package is ready. Execute and it should delete all the old files from a folder and leave only current day files. Noticed, my SSIS Package has deleted all the files and I am left with only current day files in my folder.

Delete all files except current day files in SSIS Package by using Script Task