In in doubt, use T-SQL to join the facts to the dimension table.

I tend to create the facts in a working table (a persistent table in a database...

I agree with Phil about having the same DB structure in all environments but I have worked at a company where the DB names has a suffix for the environment...

Yes you can do this in SSIS.

Within your package you will need a connection for each CSV file and one for your Excel workbook.

In a data flow, you will need...

#temp tables only exist for the duration of the connection that created them.

Also, I guess that you want to store the results on a different server from where the xp_cmdshell...

As Jeff said, SSIS might not be the best tool for this but you can achieve this using SSIS and without a script task.

1. Create a new SSIS Package

2....

Hi Simon,

I worked on a DW project that used exactly this strategy of loading the incremental rows into a partition and then switching it into the main table. We...

Thanks. Works a treat.

Jeremy

Thanks Luis.

The source for the calculations would be a user front end so that they can define new 'scenarios'. For example they could define a new scenario that is...

declare @T table (

Scenario char(1)

,Metric char(1)

,Value int)

insert @T

values ('1','A',100)

,('1','B',80)

,('2','C',100)

,('2','D',80)

,('3','C',100)

,('3','E',70)

,('3','F',40)

,('4','C',100)

,('4','G',70)

,('4','H',40)

select* from@t

The expected results are:

1. 20 (being A-B)

2. 100 (as C is larger than D)

3. 110 (as the...

Kimball seems to mentions two different approaches. One involves creating a fact dimension to describe the single fact (e.g. balance, credit interest, debit interest etc) on the fact table...

Thanks.

That was the decision I was coming to but great to have someone think the same way.

Jez

I've got that and the file name is fine.

Digging a bit deeper, I have found some warnings elsewhere in the package and I suspect that the package is hitting the...

Hi Jeff,

A file will always have the same number of columns. The import file (a text file) will be delimited by characters and there will always be the...

That's how it is in the import file. I am importing each line into a single nvarchar(max) column and running some t-sql code to split it into different columns.

Thanks. It had to be around somewhere & I just couldn't find it.

Jez