You can now run this data flow task to populate your Time Cache Transform.ģ.

Which represents that this can be used in the Lookup later.

NOTE : From above you can see because we created the Index Position of 1 for the Time, it has got the magnifying glass. In our example we want to do the lookup on the Time, so next to Time we will put the Index Position of 1 If we went into the Advanced Editor for the ADO NET Source, we can verify that the Time is the Data type of “ database time with precision” Then double click to go into the Time Cache Transform data flow task.į. Then drag in a data flow task, which we renamed to Time Cache TransformĬ. Next will be setting up the Date Cache Transform.ī. You can now run this data flow task to populate your Date Cache Transform.Ģ. NOTE : From above you can see because we created the Index Position of 1 for the FullDate, it has got the magnifying glass. Now click on the Mappings in the left hand side Then click Ok to go back to the Cache Transformation Editor. NOTE : You can have more than one lookup, and each additional Index Position will increment in number. In our example we want to do the lookup on the FullDate, so next to FullDate we will put the Index Position of 1 This is where you will configure which column will be used for the actual lookup. NOTE II : You must ensure that the account running the SSIS job has access to the folder location and file on the server. NOTE : The reason that we did this is so that if you execute this package from another location it will still find the location.ģ. Then we configured the File name with a UNCĢ. Then we also selected to Use file cache.ġ. This will then open the Cache Connection Manager Editor Now when it opens you will see that there is no Connection Manager. Then double click your Cache Transform to go into the Properties Then drag the blue constraint to your Cache Transform. Now in the SSIS Toolbox, click under Other Transformations and drag in the Cache Transform If we went into the Advanced Editor for the ADO NET Source, we can verify that the FullDate is the Data type of “ Database Date” NOTE : The reason that we converted our FullDateAlternateKey to Date, is because when we do our lookup later, we want it to have JUST the Date Data Typeį. We then configured it with the following as shown below NOTE : The reason we use the ADO NET Source, is because it is better at getting the correct Data Types for our Cache Transform.Į. Now in the SSIS Toolbox, drag in the ADO NET Source Then double click to go into the Date Cache Transform data flow task.ĭ. Then drag in a data flow task, which we renamed to Date Cache TransformĬ. Go into your SSIS and create a new SSIS Package.ī. We will first start with setting up the Date Cache Transform.Ī. This is because in the later steps you will see how you just select your file.ġ. Unless your Date table changes every time you load your data. NOTE II : You will only have to create the Date and Time Cache Transform once. NOTE : We inserted a time table into the AdventureWorksDW2012 database, which has every minute for a day which will return 1440 rows. I used the AdventureWorksDW2012 Data File and AdventureWorks Multidimensional Models SQL Server 2012Ĭreating the Cache Transform for Date and Time.We will be using SSDT and SQL Server 2014.Then we are going to insert data into our Fact table from the following Adventure Works table below using the Lookup Transformation.We are first going to create our Cache Transform for our Date and Time.This enabled the query to be returned a lot quicker. So my solution to the problem was to use the Cache Transform and then the Loopkup Transform in the SSIS Data flow task. If I excluded this the data would be returned a lot quicker. What happened is that I found that when doing inserts on larger data sets, it would take a long time to complete the date and time joins.

Ssis 029