Tilde (~) Delimited File Read in SSIS
I'm trying to load a Tilde (~) delimited .DAT to SQL Server DB using SSIS. When I use a flat file source to read the file, I don't see the option of a ~ delimiter. I'm pasting a row from my file below:
7318~97836: LRX PAIN MONTHLY DX~001~ALL OTHER NSAIDs~1043676~001~1043676~001~OSR~401~01~ORALS,SOL,TAB/CAP RE~156720~50MG~ANSAID~100 0170-07
In here, I need to get the data between the columns separated by a ~ i.e. Column 1 should have '7318', Column 2 should have '97836: LRX PAIN MONTHLY DX'.
Can someone help me with this? Can this be done using a Flat File Source or do I need to use a Script Task?
Sure you can, you just need to configure the "Column delimiter" property in the "Flat File Connection Manager Editor". There are some predetermined choices there, but you can click and type any separator you want:
After that you can click "refresh" and then "OK".
- Blank xml namespace in child nodes in SQL Server
- SQL Server migrate new data into old schema
- Newline in SQL Server
- Combine First and Last Names of people based on criteria
- "The transaction log for database is full due to 'LOG_BACKUP'" in a shared host
- Copy a ODBC-linked SQL-Table to an Access table with VBA
- How to execute a stored procedure inside a select query
- JPA entity ID field with a SQL Server 2022 Sequence
- Error 'Conversion failed when converting from a character string to uniqueidentifier.' when using UNION ALL
- migrate data from MS SQL to PostgreSQL?
- Sqoop Import HBase - SQL Database
- How important is the order of columns in indexes?
- Connect to SQL Server running on Windows host from a WSL 2/Ubuntu sqlcmd
- SQL Server, to normalize this much or not?
- Strange behavior of symfony doctrine during phpunit test - field becomes null in database
- How do I make it to where my login will accept either an email or username in the username field
- Adding column to select statement in SQL causes timeout
- SQL Server: Clustered index on datetime, ASC or DESC
- SQL Pivot on Conditional Count
- SQL Server indexes - ascending or descending, what difference does it make?
- Query to concatenate row data in each group
- Combine 2 SQL Statements
- Converting SQL server timestamp to an epoch timestamp
- How does SQL Server allow multiple of the same alias in the same SELECT statement?
- Change Date format from yyyy-mm-dd to mm/dd/yyyy Derived Column
- How do you send email with TSQL using a Agent Operator instead of an email address?
- Spark sending LIMIT to SQL Server on display function
- How can I manage Azure SQL user/login/credentials
- Can't add members to SSAS role through Visual Studio
- How to use Charindex for one or the other character