SQL

  • 15 Jan

    SQL output trailing delimiter at end of row

    Trailing delimiter at end of each row

    Q: We are using Limagito File Mover, SQL as source, database is MSSQL.

    SQL statement (Source)

    select

      person_last_name as last_name

      , person_first_name as first_name

    from
    my_table

    Common Options

    Limagito File Mover SQL Common options

    Issue

    The value for the delimiter is appended at the end of each row. Is there a way to prevent this from happening? The output file is being automatically imported into a system, and that system does not like that extra delimiter at the end of the file. To explain what I’m talking about, the output to the file is currently like this:

    last_name,first_name,

    Doe,John,

    Smith,Mary,

    What we would like to see is this:

    last_name,first_name

    Doe,John

    Smith,Mary

    Thank you for any assistance you can give.

    A: In version v2021.1.15.0 we added some extra options:

    • SQL Common options (default Enabled):
      • Trailing Line Break: Determines whether to add a line Break after the last line or not.
      • Trailing Line Delimiter: When enabled the value for the delimiter is appended at the end of each row

    Please disable ‘Trailing Line Delimiter’. This will remove the delimiter at the end of each row.

    Limagito File Mover SQL Common options

    If you need any info about this option, please let us know.

    Best regards,

    Limagito Team

    By Limagito SQL , ,
  • 05 Dec

    SQL Batch Move turn off SQL datatype guessing

    SQL Batch Move turn off SQL datatype guessing

    We received the following information from a user:

    Q: We’ve noticed an issue with the SQL Import (= SQL as Destination) – it appears that Limagito is attempting to guess the datatype, when this doesn’t match it fails to import – is there a way we can disable the datatype guessing?

    Briefly the issues encountered:

    1) In this example the import failed even though the source data was only 1 char long and table datatype nchar(1) – but that character was an N, what Limagito wanted to do was convert this to the word FALSE, I could see this after changing the table datatype to nchar(5). So it had assumed a boolean (conversely putting a Y in the field transformed it to TRUE)
    6/11/2021 3:11:52 PM Batch Move Exception: [FireDAC][Phys][ODBC]-345. Data too large for variable [TapShift]. Max len = [1], actual len = [5] Hint: set the TFDParam.Size to a greater value

    2) In this example the import failed as Limagito guessed this was a datetime field, it’s not – the table is setup to hold an nvarchar(10)
    1/12/2021 1:56:18 PM Batch Move Exception: [FireDAC][Comp][DM]-607. Bad text value [B2523:1] format for mapping item [FurnPrep]. ‘B2523:1’ is not a valid time

    A: Some background information first. In version 2019.8.17.0 we added a Batch Move option which will take the data from the source (csv) file and will insert this data into the Database (= SQL as Destination).

    In version v2021.12.5.0 we added a ‘GuessFormat Override DataType by Field Names’ option. This option gives you the possibility to override the DataTypes we guessed. To help you we also added some extra information in the (RunTime) log which shows you the guessed DataTypes.

    Limagito File Mover GuessFormat log

    Where can you find this option? Open your Destination Setup. Select an existing or open a new SQL as Destination.

    Limagito File Mover SQL as Destination

    ‘Batch Move of Data’ should be selected.

    Limagito File Mover SQL Batch Move

    Select ‘Batch Move’ Tab > ‘Text’ Tab. Here you will find the ‘GuessFormat Type Override by Field Names’ option. You can enter the field name(s) for the data types you want to override. When adding multiple field names, you need to use a , (comma) to seperate them (important).

    Enter Field Names which we should override. We are using CommaText to get or set these field names. The single comma-delimited string format is also known as the system data format (SDF). For SDF format, Field Names are separated by commas, and optionally enclosed in double quotes. Double quote marks that are part of the string are repeated to distinguish them from the quotes that surround the string. Commas that are not contained within double quote marks are delimiters. Two commas next to each other will indicate an empty string and will be ignored.

    Suppose we want to override the following Field names:

    Field1
    Fie,ld2
    Field 3
    Field”4

    Result as CommaText should be:

    Field1,”Fie,ld2″,Field 3, “Field””4”

    In the following screenshot we override the guessed DataType to STRING for the Field Names STRING1 – STRING 2 – STRING,3 – String4 found in the csv source file.

    Limagito File Mover Batch Move Setup

    #FileTransfer

    If you need any info about this ‘how-to turn off SQL datatype guessing’ question, please let us know.

    Best regards,

    Limagito Team

    By Limagito SQL
  • 24 May

    SSL Security Error on SQL-Source for MSSQL in Limagito File Mover

    User: It seems that we started to receive a connection error by SQL-source which should connect to a Microsoft SQL Server recently.

    This is the error message:

    TFDPhysMSSQLConnection DB SQL Exception: [FireDAC][Phys][ODBC][Microsoft][ODBC SQL Server Driver][DBNETLIB]SSL Security error

    Open Database Exception [FireDAC][Phys][ODBC][Microsoft][ODBC SQL Server Driver][DBNETLIB]SSL Security error

    I googled a little and found out that it might have something to do with the fact that SQL Server now uses TLS 1.2 as standard

    A: We adviced the user to switch from MSSQL to ODBC as ‘Database Vendor’

    Limagito File Mover SSL Security Error ODBC

    .. and asked them to check the following link:

    ssl-security-error-for-microsoft-sql-driver

    Limagito File Mover SSL Security Error

     

    We received the following feedback:

    • Installed recent ODBC Driver for MSSQL 17 to the Server where Limagito File Mover is running.
    • Created System-DSN for specific Database with that newest ODBC-Driver.
    • Switched settings in Limagito Rules to ODBC.
    • >> Seems to work!

    #FileTransfer

    If you need any info about this error, please let us know.

    Best regards,

    Limagito Team

1 2 3 4