Not sure if I need Lookup here?

  • 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!

  • 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