May 23, 2011 at 4:11 am
I need to your suggestion in implementing on SSIS Package.
Problem: We have millions of data coming in single flat file for multiple categories like below:
ACC | 1 | new account |FirstName|LastName |address|
VEN|VenName|2,000
VEN|VenName|2,000
BUS|xcx|343|gfgfdg|ggdsg|
BUS|xcx|343|gfgfdg|ggdsg|
BUS|xcx|343|gfgfdg|ggdsg|
as shown,(1) we can identify the record by using record identifier(ACC,VEN,BUS) (2) data is pipe delimited but the number of columns will vary as per the category
Note: File locaiton need to be passed as variable
I need to write a SSIS package which will read the data from file and load to respective tables, viz. Account(ACC),Vendor(VEN),Business(BUS), etc.
My appraoch for the solution in SSIS is: (1) Create the master packge to check for the existence of the file in specified location (2)if file exist,use Script task to split the file into multiple files dynamically, i.e. all ACC records will goes to Accout.txt and so forth. (3) Develop the child packages for each category type(Accout,Busiess,Vendor) will read the data from the files created in step 2. (4) Call all the chile packages from parent package
We can do this operation in single package using Split transoforation, however, by considering the complexity of transformation involves further to load each category frecord and to avoid loading millions of data to memory pipleline I have chosen the above solution.
I need to your guidance/help in the ealuating above solution approach and help me to improve the desgin.
Thanks in advance
erajendar
May 23, 2011 at 10:56 am
(2)if file exist,use Script task to split the file into multiple files dynamically, i.e. all ACC records will goes to Accout.txt and so forth.
I would not use a Script Task for this. You can use the Conditional Split task and then push all data from each output to a Flat File Destination. It accomplishes the same result without using a Script Task, which I try to reserve for a last resort to solving a problem in SSIS.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply