Exporting column records from .CSV to multiple table in SQL Server using SSIS

0
votes

I have a requirement to create a batch solution using SSIS package.

I am new to SSIS hence exploring this to implement my scenario.

Scenario:

  1. An external system will dump data into .CSV file on weekly basis on specific directory on server.
  2. An SSIS package will be scheduled to run on weekly basis to read that .CSV file path on server and write those data in multiple table in SQL Server.

Task: The source .CSV file with set of columns belong to different tables in SQL Server. Those column and data in that row should be mapped to specific columns in different tables.

My thought to ask community -

  1. What alternatives SSIS provides which allows me to segregate each column in .CSV file and map them to different table in SQL Server?
  2. In .CSV I have eleven columns. As per SQL table structure, those eleven columns are distributed into four separate tables.

I appreciate any productive advise to implement the solution.

UPDATE: Below things so far I setup/tried

  1. I created new SSI package successfully. In Data flow so far as a source I setup the variables to read path and file name.
  2. I setup OLE DB datasource as destination and establish connection to server.
  3. I believe I have reached by completing setup with .CSV as a source to read columns, but I am still exploring what would be ideal destination or how OLE DB would help to consume records in different tables.
  4. I went through Multicast and Import Columns options. I understand that the Multicast is mainly used to share or output data to different destinations.
1
sql-servercsvssis
Start out slowly, by creating an SSIS package to read the file first. Will the csv filename be the same each week? Do you know the column names before hand? You can direct output to multiple tables and only copy the columns to each table. What have you tried on your own? – SS_DBA
@WEI_DBA, Yes. Filename will remain same each and every week. Yes, I know the column names beforehand. I have updated my question with details of what I tried so far. – Binoy
Its not clear how a single file is loaded into multiple tables. If you just need four columns in one table and three in another then there is nothing complicated here - just map the columns through. You don't need any additional transformations. – Nick.McDermaid
Use a multicast and pass the source to as many OLEDB destinations(1 for each table) as you need and only map the appropriate columns in each destination. – Pratheek
What was wrong with the multicast solution you tried? What error did you get? – Tab Alleman

1 Answers

0
votes

you can create four OLE DB destinations and using multicast you can route your data. so basically multicast will pass all the 11 tables to all four destinations and there you can map only required column with your tables. Let me know if you have any confusion.