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

Android - Basic Steps (Service)

Service Run in the main thread of the hosting application Android can kill the Service if need the resources Purpose Supporting inter-application method execution Performing background processing Start a Service Call Context.startService(Intent intent)\ To call from a Fragment use getActivity().getApplicationContext().startService( intentService); Executing the service After call startService(...)  In the Service is executed the method onStartCommand(...) If the method returns the constant START_NOT_STICKY then Android will not restart the service automatically if the the process is killedp Foreground To execute the service foreground call the method startForeground() Use this if the user is aware of the process Bind to a Service Call the method Context.bindService( Intent service ServiceConnection con int flags ) Send Toast from the Service On the method onStartCommand receive the message   ...
Publicar un comentario
Leer más

Android - Basic Steps (Location & Maps)

Location Is composed by Latitude Longitude Time-stamp Accuracy Altitude Speed Bearing LocationProvider Types: Network  Wifi access points Cell phone towers GPS Passive Piggyback on the readings requested by other application Permissions Network  android.permission.ACCESS_COARSE_LOCATION android.permission.ACCESS_FINE_LOCATION GPS android.permission.ACCESS_FINE_LOCATION Passive Provider android.permission.ACCESS_FINE_LOCATION LocationManager System service for accessing location data getSystemService( Context.LOCATION_SERVICE ) Functions Determine the last known user location Register for location update Register to receive intents when the device nears or move away from a given geographic area LocationListener Defines callbacks methods that are called when Location or LocationProvider status change. Methods onLocationChanged(...) onProviderDisabled(...) onProviderEnabled(...) onStatusChan...
Publicar un comentario
Leer más

IIS - Permisions

IIS Permissions To enable the Active Directory connection in the IIS, follow the next steps: Go to IIS Go to Application Pool Select your App Pool Select Advanced Settings in the right side In the section Process Model Select in Identity value the property " NetworkService " You don´t need to restart your application
Publicar un comentario
Leer más