Post reply

Automate Import Process

  • November 14, 2021 at 7:07 am

    #3952846

    Hi everyone

    I am going to be regularly importing files into SQL Server and appending them to an existing table.  I just processed 10 plus imports and its not really super good use of my time.  I have to select each source/destination/field mapping etc each time.  Is there a way to have SQL Server remember the steps so next time I have to import the same type of file I can select one button and let SQL Server do the rest?

    Thank you

  • November 14, 2021 at 9:10 am

    #3952864

    Look at writing it is SSIS instead of using the import wizard.

    Using things like foreach loops etc.

    As a starting point, if you use the wizard and at the end it asks if you want to save the package.  Doing this will generate a DTSX file, which you can open in visual studio along with having the right extensions loaded to further modify it to add loops etc.

     

    https://visualstudio.microsoft.com/vs/community/

    https://marketplace.visualstudio.com/items?itemName=SSIS.SqlServerIntegrationServicesProjects

    https://www.sqlservercentral.com/stairways/stairway-to-integration-services

    https://docs.microsoft.com/en-us/sql/integration-services/control-flow/foreach-loop-container?view=sql-server-ver15

  • November 14, 2021 at 9:14 am

    #3952865

    Alternative to SSIS is powershell and If you have them as CSV files using modules like DBATools and Import-DbaCsv, you can easily write loops in PowerShell to loop in a directory and pass each file to the same command to import it multiple times.

  • November 15, 2021 at 6:03 am

    This was removed by the editor as SPAM

  • November 15, 2021 at 7:12 pm

    #3953284

    If your CSV has the same name every time, you can push it to a specific folder and run your saved SSIS package based on Ant's first reply. Or rename the file to the name expected in the SSIS package before running. I have some automated packages and use DOS Batch files to move & rename the CSV files to the expected name and location.

  • November 15, 2021 at 7:51 pm

    #3953297

    Thank you everyone for the replies.  I will take a look and implement.

  • November 16, 2021 at 6:29 am

    #3953447

    You're posting in an SQL Server 2019 forum.  BULK INSERT and BCP have both had "CSV" capabilities since SQL Server 2017.  If the files and the tables are consistent, there's no reason why this can't be 100% automated using T-SQL including sending you an email in the morning letting you know how many files were processed and what their status is as well as emails that occur WHEN a file fails to load along with the reason why it failed.  We'd need a bit more information about the files.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • November 16, 2021 at 4:14 pm

    #3953656

    Here is the plan:

    -log into SFTP server and download files to local directory

    -unzip files to specified folder

    -import files into SQL Server table (it will always be the same table)

    -if import is successful then copy files to a different folder to archive purposes

    -if import is failed then copy files to a different folder to review

    -log all actions

    -instruct another program to begin downloading data from service provider and once downloaded then repeat above steps

    Can all of this be done in SQL Server?

  • November 16, 2021 at 4:32 pm

    #3953664

    water490 wrote:

    Here is the plan:

    -log into SFTP server and download files to local directory

    -unzip files to specified folder

    -import files into SQL Server table (it will always be the same table)

    -if import is successful then copy files to a different folder to archive purposes

    -if import is failed then copy files to a different folder to review

    -log all actions

    -instruct another program to begin downloading data from service provider and once downloaded then repeat above steps

    Can all of this be done in SQL Server?

    While I am pretty sure that Jeff will answer 'yes' to this question, I'd suggest that you consider using SSIS which (in my opinion) handles tasks outside of the DB engine (SFTP and file operations, for example) more naturally than they can be handled using straight T-SQL.

    SSIS can do all of your required tasks natively, except for the SFTP part, which will require additional software (WINSCP, which SSIS can call, should suffice).

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • November 16, 2021 at 4:44 pm

    #3953668

    There are a few 3rd party "add-ins" products for SFTP.

    We bought one at my last place. Don't remember the name, but it required annual support fee.

    • This reply was modified 3 years ago by  homebrew01.

  • November 17, 2021 at 3:07 am

    #3953845

    water490 wrote:

    Here is the plan:

    -log into SFTP server and download files to local directory

    -unzip files to specified folder

    -import files into SQL Server table (it will always be the same table)

    -if import is successful then copy files to a different folder to archive purposes

    -if import is failed then copy files to a different folder to review

    -log all actions

    -instruct another program to begin downloading data from service provider and once downloaded then repeat above steps

    Can all of this be done in SQL Server?

    Phil was correct.  My answer is "Yes".  What are you using for the SFTP downloads and does it have a CLI (Command Line Interface).

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • November 17, 2021 at 5:09 am

    #3953926

    I am using Filezilla.  Yes it can be operated via command line

  • November 17, 2021 at 7:12 pm

    #3954177

    water490 wrote:

    I am using Filezilla.  Yes it can be operated via command line

    Perfect.  The next question is, which is a showstopper for a whole lot of people (even though it shouldn't be), can you use xp_CmdShell?

     

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • November 17, 2021 at 8:20 pm

    #3954204

    i don't know what that is so i have not used it

  • November 18, 2021 at 3:31 am

    #3954282

    That's OK.  It's the tool that will allow you to do things like run the CLI of a program from SQL Server.  Such a thing can also be done using a "CMD Task" from a job or even SSIS but I find that xp_CmdShell is the most direct and usually the easiest method.

    The only thing now is... can you create 2 or 3 CLI calls to Filezilla that work for you and then change the login, password, and even the site URL to something benign but still maintaining the correct form and post that and we'll get started.

    It would also be pre-emptively helpful if you could attach the first 5 lines from one of the files and the code to build the target table for it.  Of course, if the file has any PII or other sensitive information in it, don't send it.  Just include the header line from the file so we can make sure what the spelling, order, and delimiter of the columns (fields, whatever) in the file are.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 15 posts - 1 through 15 (of 17 total)

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