Moving Excel or other files using MS Integration Services (SSIS)

01-12-2018  0 Comment(s)

The Discerning Analyst always have, and will always need integration as part of his skill sets in his craft. To be able to move data automatically, by database trigger, scheduled task or scheduled SQL Jobs from whatever format excel, text file, xml etc etc to a destination database,shared folder or ftp location across the organization, is a must have skill set.

        On this guide you can learn some extra skills on about basic manipulation of an excel file using SSIS.

Requirements: Visual Studio 2010 or higher with SQL Data tools, Patience.

1. Open visual studio and create a new file ->> project

2. After naming the project and specifying the project location, go to business intelligence templates and choose integration services projects.

3. Control flow and other tabs will now open. Drag drop script task from the SSIS Toolbox to the control flow area.

4. Click the script task then click on the project variables encircled on the image below.

5. Add the variables same with the table below on each column on the variables pane.

Name Data Type Value Expression
VarDestinationFullPath String D:\09.Projects\07.Data-Jackal\Destination-Location\ @[User::VarDestinationPath]+@[User::VarFileName]
VarDestinationPath String D:\09.Projects\07.Data-Jackal\Destination-Location\  
VarFileName String    
VarFolderFullPath String D:\09.Projects\07.Data-Jackal\Original-Location\ @[User::VarFolderPath]+ @[User::VarFileName]
VarFolderPath String D:\09.Projects\07.Data-Jackal\Original-Location\

Comment Here


No Comments to Show