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.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply