1

I have Multiple CSV files in Folder

Example : 

Member.CSv
Leader.CSv

I need to load them in to Data base tables .

I have worked on it using ForEachLoop Container ,Data FlowTask, Excel Source and OLEDB Destination

we can do if by using Expressions and Precedence Constraints but how can I do using Script task if I have more than 10 files ..I got Stuck with this one

mohan111
  • 8,633
  • 4
  • 28
  • 55
  • So you've managed to do it without scripts so far? Does each CSV (and table) have different columns? You might want to look into BIML which autogenerates packages for you – Nick.Mc Feb 02 '16 at 12:29
  • yes I have did without using Script task but if files keep on increasing then above process will become difficult for me.That's the reason I'm asking for Script – mohan111 Feb 02 '16 at 12:32
  • You can't dynamically built meta data (i.e. definitions of source columns and destinationsetc.). It sounds like you definitely need to investigate BIML, especially if you are just importing these and not doing anything too fancy. – Nick.Mc Feb 02 '16 at 12:35
  • yeah I'm just importing Files only but is there option in Script task as I have find this link http://www.techbrothersit.com/2014/10/ssis-how-to-load-files-in-different.html @Nick.McDermaid – mohan111 Feb 02 '16 at 12:59
  • Hi @mohan111, in that link it clearly mentioned that tables created first and then data loaded with script task. If you create table first then you can follow that process. what is your exact quesion `Can I create table using script task by reading file metadata and then load data into that table?` – user3583912 Feb 02 '16 at 13:12
  • tables are already created in database I need to load them into the Target table by using Script task like I need to pick the Csv file name according to that table in DB @user3583912 – mohan111 Feb 02 '16 at 13:15
  • Hi @Mohan, then just follow that link to create package it works. but make sure file names must be same name as table name. all the best – user3583912 Feb 02 '16 at 13:22
  • @user3583912 Yeah I already tried to follow that but the script is not working and it is throwing the same error as this http://stackoverflow.com/questions/28997381/runtime-error-exception-has-been-thrown-by-the-target-of-an-invocation-from-sc – mohan111 Feb 02 '16 at 13:25
  • The script you reference doesn't make use of any SSIS features. You may as well just create a console app in Visual Studio (@Jeswin also makes mention of this in your error link). Next time, tell us the _entire_ story first. I suggest you edit your question to contain this important information – Nick.Mc Feb 02 '16 at 22:32

1 Answers1

0

We have a similar issue, our solution is a mixture of the suggestions above.

  • We have a number of files types sent from our client on a daily basis.
  • These have a specific filename pattern (e.g. SalesTransaction20160218.csv, Product20160218.csv)
  • Each of these file types have a staging "landing" table of the structure you expect
  • We then have a .net script task that takes the filename pattern and loads that data into a landing table.
  • There are also various checks that are done within the csv parser - matching number of columns, some basic data validation, before loading into the landing table

We are not good enough .net programmers to be able to dynamically parse an unknown file structure, create SQL table and then load the data in. I expect it is feasible, after all, that is what the SSIS Import/Export Wizard does (with some manual intervention)

As an alternative to this (the process is quite delicate), we are experimenting with a HDFS data landing area, then it allows us to use analytic tools like R to parse the data within HDFS. After that utilising PIG to load the data into SQL.

Marcus D
  • 1,074
  • 1
  • 12
  • 27