Ir al contenido principal

BI - Load an Excel File to DB



The purpose is to upload an Excel file into a Database without duplicate information.
  • Install SQL Server Data Tools for Visual Studio 2012
  • Create a New Project 
    • Type: Integration Services Project
  • Create New Package
    • Menu: Project -> New SSIS Package 
  • Inside the Package
    • In the tab Control Flow , drag a "Data Flow Task"
  • Inside the Data Flow tab, drag the following Items, as the image
    • Excel Source
    • OLE DB Source
    • Sort elements (x2)
    • Mege Join
    • Conditional Split
    • Data Conversion
    • OLE DB Destination


Excel Source

  • Double click in the element
  • In Connection Manager, Select "New" and then choose the file path


  • Select the Excel tab 

  • In Columns, select all the items











OLE DB Source


  • Double click in the element
  • In Connection Manager, Select "New" and then choose the Server Name and Table
  • Click Ok
  • Select the table where you are comparing the information (is the same table where you are going to store the new data)

  • In Columns, select all the items
  • Click OK

SORT

  • Double click in the left Sort
  • Select the key that you want to use to compare











  • Do the same for the right Sort, selecting the same key to compare

MERGE JOIN

  • Double click in the Merge Join
  • Select in Join Type: Left outer join
  • Select all the left colums and only the key in the right table
  • Change the Output Alias for the right key
  • Click OK

CONDITIONAL SPLIT

  • Double click in the Conditional Split
  • Open the Colums Menu at the left and drag and drop the right key to the down part
  • Change the conditional to ISNULL([NewCodigo])
  • Connect to DATA Conversion with the line "New Rows"

DATA CONVERSION

  • Double click in the element
  • Change the Data Type as in the image, especially in "Codigo"

OLE DB DESTINATION

  • Select the connection manager
  • Select the table to store the new rows
  • Save the project

TEST

  • Execute the Package 
  • Do a select query to review the new data added


Comentarios

Publicar un comentario

Entradas populares de este blog

C# Using tabs

To use tabs in C# use the TabContainer element from AjaxControlToolkit Include AjaxControlToolkit  Include in the Web.config file, inside the tag <system.web> the following code  <pages>       <controls>         <add tagPrefix="ajaxCTK" namespace="AjaxControlToolkit" assembly="AjaxControlToolkit"/>       </controls>     </pages>   Include TabContainer element First  include TabContainer element that is the section where all the tabs will be displayed. <ajaxCTK:TabContainer ID="TabContainerUpdate" runat="server"                 Height="800"                 CssClass="ajax__tab_style"> </ajaxCTK:TabContainer> Second per each tab include the following code corresponding to each ...
Publicar un comentario
Leer más

Rails - Basic Steps III

pValidations Validations are a type of ActiveRecord Validations are defined in our models Implement Validations Go to   root_app/app/models Open files  *.rb for each model Mandatory field validates_presence_of   :field Ex:   validates_presence_of    :title Classes The basic syntax is class MyClass        @global_variable                def my_method              @method_variable        end end Create an instance myInstance = MyClass.new Invoke a mehod mc.my_method class() method returns the type of the object In Ruby, last character of method define the behavior If ends with a question -> return a boolean value If ends with an exclamation -> change the state of the object Getter / Setter method def global_variable       return @global_variable end ...
Publicar un comentario
Leer más

Python create package

Create a root folder Create a sub-folder "example_pkg" that contains the funtionallity packaging_tutorial/ example_pkg/ __init__.py In the root folder create the following structure  packaging_tutorial/ example_pkg/ __init__.py tests/ setup.py LICENSE README.md in the setup.py contains the configuration of the packages your package is found by find_packages() import setuptools with open ( "README.md" , "r" ) as fh : long_description = fh . read () setuptools . setup ( name = "example-pkg-YOUR-USERNAME-HERE" , # Replace with your own username version = "0.0.1" , author = "Example Author" , author_email = "author@example.com" , description = "A small example package" , long_description = long_description , long_description_content_type = "text/markdown" , url = "https://github.com/pypa/sam...
Publicar un comentario
Leer más