How to convert a database data in predefined format and generate output in text format

user_18167015 0
Viewed 39

I am having a SQL database having the attributes:

  • First_Name
  • Last_Name
  • Sex
  • Address

For reporting to the authority I have a fixed format like

For the First name: FN(no of characters in the first name)First_Name

  • For example the first name Daniel should be like: FN05Daniel
  • For Bose, it should be: LN04Bose

Is there any way using SSIS the same can be accomplished and the output can be generated in text format?

If not SSIS please suggest any probable mechanism to get the result. I am just learning to code so please don't mind if it's a silly question.

etl ssis sql-server sql
2 Answers

I don't get what you mean but this is how i use sqlite3

class Database():
    def __init__(self, path = ""):
        try:
            if path == "":
                with open("Database.db", "r") as Database:
                    pass
                self.path = "Database.db"
            else:
                with open(path + "/Database.db", "r") as Database:
                    pass
                self.path = path + "/Database.db"
        except:
            if path == "":
                with open("Database.db", "x") as Database:
                    pass
                self.path = "Database.db"
            else:
                if not os.path.exists(path):
                    os.mkdir(path)
                with open(path + "/Database.db", "x") as Database:
                    pass
                self.path = path + "/Database.db"
        self.Database = sqlite3.connect(self.path)
        self.Cursor = self.Database.cursor()
    def createSheet(self, name):
        self.Database.execute(
          f'''CREATE TABLE IF NOT EXISTS {name}(
          ID INT PRIMARY KEY
          );
          '''
        )
    def setVar(self, Sheet, Var, Value):
        try:
          self.Cursor.execute(
            f'''ALTER TABLE {Sheet} ADD COLUMN {Var} TEXT'''
          )
        except:
          pass
        self.Cursor.execute(
          f'''INSERT OR IGNORE INTO {Sheet} ({Var}) VALUES ("{Value}")'''
        )
        self.Cursor.execute(
          f'''UPDATE {Sheet} SET {Var} = "{Value}"'''
        )
        self.Database.commit()
    def readVar(self, Sheet, Var, Convert = str):
        DataGet = self.Database.execute(f'SELECT {Var} from {Sheet}').fetchone()
        return Convert(DataGet[0])

So imagine sqlite3 as Excel

The createSheet function actually creates a sheet in Excel (in sqlite3 is table)

The addVar function creates a column (which is the variable name, this variable is empty, which mean it doesn't have value) if not exists then it's will insert (put a value into the variable) or update (same as insert but it's update, insert function only work when you add an empty variable), value in sqlite3 is row

The readVar function read the value base on the variable name

enter image description here

enter image description here

user_161523370

Using SQL:

You can simply use the following SQL command to format your data:

SELECT 
    'FN' + CAST(LEN([First_Name] as varchar(3)) + [First_name] as [First_Name],
    'LN' + CAST(LEN([Last_Name] as varchar(3)) + [Last_name] as [First_Name],
FROM
    [MyTable]

To export data to a text file, you can refer to the following answer:

Using SSIS:

You should create an SSIS package with one Data Flow task. The Data Flow task should contain:

  1. OLE DB Source reading from the SQL table
  2. Derived Column to add the FN and LN prefixes:
"FN" + RIGHT("0" + (DT_WSTR,10)LEN([First_Name]),2) + [First_Name]
"LN" + RIGHT("0" + (DT_WSTR,10)LEN([Last_Name]),2) + [Last_Name]
  1. Flat File Destination to export data to a text file.
user_70312300
Related