Post reply

SSIS Implementation

  • May 9, 2020 at 11:53 am

    Go to Answer

    #3750166

    Hi

    We would like to implement SSIS Jobs in our new environment for the Data Loading. I am little new to the Environment/capacity planning. So I would need suggestions on how I can implement it.

    I am aware of the Package creation. Our task here is to load data from CSV/Excel files into Database tables.

    Scenario:

    Currently the environment has SQL Server 2016 (Standard Edition)

    For creating the SSIS Packages, I know we would need either SSDT or Visual Studio to build and deploy them.

    Question 1: What are the add-ons to Server. It just has SQL Server 2016(Standard Edition) without Sql Server Integration services.

    Question 2: What version or Editions I need that on server( SSDT/Visual Studio)

    Question 3: Can we execute the Package Job without Sql Server Integration services installed?

    Question 4: What would be the pros and cons of having the SSDT /VS installed within the server

    Question 5: Could you suggest the links that would help me sort this.

    Question 6: Understanding the SSIS components and implementation looks comfortable. But would like to advance in Script tasks.

    Sorry If these are repeats.

    Thanks in Advance for your responses.

     

  • May 9, 2020 at 2:00 pm

    Answer

    #3750176

    SSDT is for the client machine to develop the packages - Not to be installed on the server

    Note that to load CSV/Excel files you do not need SSDT - that can be done with either T-SQL (bulk insert/openrowset/openquery) or through powershell or any other application (C# for example) capable of using a OLEDB/ODBC connection and data objects.

    On the server - you need to have Integration Services installed in order to be able to execute the packages on the server (on SSISDB/Filesystem through a SQL Server Agent Job or Windows scheduled job (or other similar))

    Depending on the edition (Standard/Enterprise there will be a few items that will not be available on standard - see https://docs.microsoft.com/en-us/sql/sql-server/editions-and-components-of-sql-server-2017?view=sql-server-ver15

  • May 9, 2020 at 2:19 pm

    #3750183

    Thank you for clarification.

    Additional I have below doubts

    1. Currently I am loading the csv files using T-SQL, But for some records there are disproportionate delimiters.

      Eg: In a Comma separated file consider the value of Address(Column). The data is  "19980 , Awalon rock meadows,Bothell Everett Hwy". Using T-SQL while loading its not populating correctly since there are 2 commas in a column value.

    2. If some of the values are double quotes enclosed and some of them do not. How do we load them. Kindly suggest

  • May 9, 2020 at 9:44 pm

    #3750226

    Echoing Frederico's comment, you should not be installing SSDT on the server.

    As you can see from this link, you'll need Enterprise or Standard Edition to run Integration Services. (Or Developer Edition, for, erm, development.)

    Understanding how to write useful code in script tasks requires two clear skills

    1. A clear idea of what you are trying to achieve
    2. The ability to write C#

    Once you have these, there are loads of examples available online.

    Some of the easiest, simplest and most useful relate to file handling. The File System Task can do a lot, but if you have several files to copy/rename/delete, the config required to make all that happen can be cumbersome, with the resulting solution feeling inelegant. In contrast, C# is superb at these things and it usually requires less than 10 lines of code to do what is required.

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply