September 19, 2014 at 4:21 pm
I am Trying to Load a Table with data from every Month even if there is no data for that month in source table. I think i need to use a lookup table and the Lookup Transformation in SSIS.
I have a Source Table with the following Columns:
Trial_Balance_ID,FISCALYEAR,ACCTPERIOD,Calendar_Month,actindx,ENTITY,NATURALACCT,DEPARTMENT,LINEOFBUSINESS,PAYOR,CONTRACT,STATUS,ACCOUNTDESCRIPTION,CATEGORY,POSTINGTYPE,ACTIVITYDEBIT,ACTIVITYCREDIT,NETAMOUNT
For Every DISTINCT actindx i need to have a Row for Every Calendar_Month in the destination Table. I created a lookup Table called CalendarMonth_Lookup.
I want my SSIS package to use the lookup table that includes every Calendar_Month for 2014. How do I use the Lookup Transform that if a row exists for a specific actindx and Calendar_Month to copy that exact row. If it does not Exist I want it to copy all the data from the ENTITY,NATURALACCT,DEPARTMENT, LINEOFBUSINESS, PAYOR, CONTRACT, STATUS, and input $0 into each of the three columns of ACTIVITYDEBIT, ACTIVITYCREDIT, and NETAMOUNT.
Can someone help me with setting this up? I have not used the Lookup transform to do this type of thing ever. Is this something that should be done in a Query with a case statement IF , THEN instead?
Thanks for any and all help!
September 20, 2014 at 2:48 am
I'll be honest, your post is a little tough to decipher 🙂
It seems that you have source data in a table which may or may not have entries for every month in 2014. But if there are no entries for a particular month, you still want to generate a row of zeros.
As your source data is in a table, I would be tempted to do this:
1) Create some sort of calendar table, if you don't already have one.
2) Write a T-SQL query which uses the above table, in conjunction with your source, to generate the required results.
3) Use the above query in your dataflow source.
Job done.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply