Post reply

How to load AML files SSIS or any other option

  • September 10, 2024 at 1:34 am

    #4455959

    Team,

    Please suggest or if you have an example please share the link for loading AML (anti moneylaundering)files into SQL Server. Meaning file having multiple recs in the .txt/.cvs file. Loaded to multiple tables .. Basically it is AML files if anyone who has done that do suggest

    Thanks in advance

     

  • September 10, 2024 at 5:48 am

    #4455990

    Where's your question?

    Are you asking if there's a standard format for "AML" files? Are they XML? Is there an example of one somewhere?

  • September 10, 2024 at 8:23 am

    #4456031

    You appear to be suggesting that there is a standard format for AML files ... which is fine, but the number of people here who know both how to import text files and the ins and outs of AML files is likely to be vanishingly small.

    Therefore, please supply an (anonymised) sample of an AML file, along with how you wish to import it (transformations, target tables etc), to help.

    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

  • September 11, 2024 at 3:49 am

    #4456328

    JSB_89 wrote:

    Team,

    Please suggest or if you have an example please share the link for loading AML (anti moneylaundering)files into SQL Server. Meaning file having multiple recs in the .txt/.cvs file. Loaded to multiple tables .. Basically it is AML files if anyone who has done that do suggest

    Thanks in advance

    There are a whole bunch of ways to import "CSV" files.   One of the fastest methods is to use the BULK INSERT command.  For SQL Server 2017 and above, it actually has a dedicated "CSV" setting you can use.  Here's a search for BULK INSERT...

    https://duckduckgo.com/?q=bulk+insert+sql+server&t=newext&atb=v278-1&ia=web

    The first link will be the MS documentation and the others are articles and 'tubes on the subject.

    If you need special formatting not automatically done by the "CSV" setting or you're using SQL Server 2016 or earlier, you should also lookup about the BCP Format File, which can be used by BULK INSERT to describe the file you're importing.

    Here's a starter on that... I generally always use the "non-XML format" on those.

    https://learn.microsoft.com/en-us/sql/relational-databases/import-export/format-files-for-importing-or-exporting-data-sql-server

    You can also use SSIS (Phil Parkin on this thread will be the person to ask on that) but you'll probably need to cough up the example he requested.

     

    --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)

  • September 11, 2024 at 5:22 am

    #4456342

    Looking for an example .. to load AML files ... not XML here

  • September 11, 2024 at 5:23 am

    #4456344

    @Phil--will try to get

  • September 11, 2024 at 5:29 am

    #4456346

    @jeff - got it ... will try to get the format of file..

  • September 11, 2024 at 5:29 am

    #4456345

    @jeff - got it ... will try to get the format of file..

Viewing 8 posts - 1 through 7 (of 7 total)

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