December 21, 2011 at 7:24 am
Hello All,
I have an issue with the package design
I have totally 6 files(account, agent, billing_charge, component, payments, promo) . I have 18 tables for these in sql server. For each type of file I am having 3 tables. Like for account i am having account_raw, account_prod, account_hist and for agent i am having agent_raw,agent_prod,agent_hist and so on. I have all the source files from may 1st to dec 15th like for account I have from data_account_2011-05-01.csv to data_account_2011-12-15.csv and for agent from data_agent_2011-05-01.csv to data_mart_agent_2011-12-15.csv and so on for the other files. They are in different folders. I mean all the account files are in one folder and all agent files are in one folder and all billing_charge files in one folder and so on. I need to load all these files to the respective tables.
The loading should be like this. Lets say taking the first file of account data_account_2011-05-01.csv (may 1st file of the account). First the package should truncate the account_raw table and load this file, next truncate the account_prod table and load the data from account_raw table and then insert the records from account_prod to account_hist. I should follow the same process for rest of the files. As soon as all the files data_account_2011-05-01.csv,data_agent_2011-05-01.csv,data_billing_charge_2011-05-01.csv,data_component_2011-05-01.csv,data_payments_2011-05-01.csv,data_promo_2011-05-01.csv are load for may 1st in their respective tables the package should execute a stored procedure called usp_dashboard. This stored procedure will insert a record in a table which have some calculations which are dependent on these (account_prod,agent_prod,billing_charge_prod ) tables. I have this stored procedure ready. I have stored procedures for inserting the data from raw table to prod and from prod to history table for each file.As soon as the data loading for all the tables are done for may 2nd again the stored procedure usp_dashboard should execute for may 2nd data. This process should repeat till dec 15th for all the files.
Now I have a question regarding the design. Rightnow my design is like this
1) For each loop container
2) Inside for each loop I have included 3 execute sql tasks and a data flow task.
1)execute sql task : truncates the account_raw table
2)execute sql task : executes the stored procedure usp_load_account_prod(truncates and loads the account_prod table from account_raw)
3)execute sql task : executes the sp usp_load_account_hist (loads the account_hist table from account_prod)
4) Data flow task : which will extract the account file and load into the destination table account_raw.
Do I need to follow the same procedure for other files?...Where should I include to execute the stored procedure usp_dashboard after loading all the files for that particular date.
Please help me with this. I really need help with this design. Any help or suggestions would greatly help. Please let me know if have any questions or furthur clarifications.
Thanks a lot. Thanks for your time.
December 21, 2011 at 11:38 pm
You'll need a seperate dataflow task for each file.
If I understand your requirements good enough, I believe this can be an appropriate design:
* Get the first date and the last date (06-01 till 12-15) and use these as boundaries for a FOR loop container. Inside this for loop, you'll have 6 sequence containers. Each of those sequence container will treat a different type of file.
* Each sequence container has a dataflow task. Inside the dataflow task there is a flat file source that will read the corresponding flat file. Configure the flat file connection manager with an expression that contains the date of the file you want to read. Something like "\\myFolder\data_account_" + @datevariable + ".csv"
* After the dataflow task, you have your 3 stored procedures that load the tables for that flat file.
* I believe your usp_dashboard should be launched for each date. Put the Execute SQL Task with this usp after the 6 sequence containers and connect them with this task using Success precedence constraints. Make sure they use the AND logic (double click on an arrow).
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply