September 5, 2008 at 1:24 am
I would like to use a oledb connection with a sql command so that is possible to making joins from tables that belong to different databases.
For instance instead of using lookups for building my fact in the datawarehouse.
SELECT FactTransactie.Bedrag, (other database).DimRekening.Rekening
FROM (other database). DimRekening INNER JOIN
FactTransactie ON (other database).DimRekening.Rekening_ID = FactTransactie.RekeningNummer_ID
I would so much like to be the (other database) to be dynamically.
Anybody has a clue.
I was looking on the internet but it seems that the only dynamically thing in the statement is the where clause
:hehe:
September 5, 2008 at 1:27 am
Hey Jacobus,
You could for example use a variable as a SQL source, and then populate the variable in a scripting task.
Sterkte man!
~PD
September 5, 2008 at 1:38 am
You mean building the complete SQL statement in a variable and then use this as the source?
It is a big sql statement how long is the string type that is used in the package variables?
Sjaak
:hehe:
September 5, 2008 at 1:51 am
Small price to pay for dynamics....
😀
OR (sjoe maar hindsight is a beautiful thing...)
How about specifying a connection manager per server and db? Now, at runtime you can specify the connection. Less flexible, and does mean that you would be bound to a really fixed SQL statement per table/event that you are doing (in other words, you can only port server and database, but not table).
Not quite sure what your end goal is though...
You also know that SSIS is metadata driven right? In other words, forget about the whole notion of what some DTS guys did with dynamic source, destination and transformations. Hasnt been done, and I have googled to death on that one.
September 5, 2008 at 2:57 am
You are not sure what the end goal is. Well i tell you
Actually it is very simple. I am building a fact table from several dimensions. Say about 10 dimensions.
It takes a long time thru the lookups per dimension. This is fixed now and goes very smootly.
But now i am having more trouble because we are also have to deal with late deriving dimensions. So i have to create a dummy dimension for every dimension that comes from the fact but is not a dimension yet.
This is very complex and i would like to use a join query over different tables. The problem is that this is in development and it needs to be transferred into the production environment. I don't like to change al the database names and would like it very much if the database would be dynamically.
:hehe:
September 5, 2008 at 4:13 am
If I'm not mistaken there is a limit on expression (4000 characters). So if your query is shorter you could build it dynamic using expression. Other solution is to use Script Task. I've just loaded 1 000 000+ charaters into a string variable. It works :-).
Expression example to build dynamic string:
"SELECT FactTransactie.Bedrag, " + @[User::OtherDatabase1] + ".DimRekening.Rekening " +
"FROM " + @[User::OtherDatabase2] + ".DimRekening INNER JOIN " +
"FactTransactie ON " + @[User::OtherDatabase3] + ".DimRekening.Rekening_ID = FactTransactie.RekeningNummer_ID"
September 5, 2008 at 4:48 am
Sounds like you would need package configurations, which will allow you to manage your connection manager externally from the SSIS package.
Therefore, once you deploy to production, you change the package configuration, and not the actual package.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply