Set datasource dynamicaly

[erwink]

Hi

I have 10 location with each one a database.

I've written some ETL Jobs to read the data out of this databases and write into a single DWH.

With my today's knowledge I would have to define 10 fact build with the only difference the Data Source.
Is there a way to work with a Jobstream and pass the Connection as parameter to the fact build so Ican have only one fact build

I'm using MSSQL on both side

Thank you
erwin

[MFGF]

Hi

I have 10 location with each one a database.

I've written some ETL Jobs to read the data out of this databases and write into a single DWH.

With my today's knowledge I would have to define 10 fact build with the only difference the Data Source.
Is there a way to work with a Jobstream and pass the Connection as parameter to the fact build so Ican have only one fact build

I'm using MSSQL on both side

Thank you
erwin

Hi,

I don't see why this shouldn't be possible. You'd need a connection that uses a variable name in place of the database name (and possibly another variable name in the Server Name field), then you could use this data source in your build. From the Jobstream you could then set the variable name(s) to appropriate values using a procedure node and call the fact build from a fact build node.

Cheers!

MF.

[erwink]

Hi
Well I thought about something like that .. and did it actually that way with my XML source.

But I don't see the was to do it using MSSQL

In DM I need to define the database outside of the query (see attached)

I would probably need a variable connection

Any Idea there?

btw: my connections are all defined as Cognos Data Source

[MFGF]

Hi
Well I thought about something like that .. and did it actually that way with my XML source.

But I don't see the was to do it using MSSQL

In DM I need to define the database outside of the query (see attached)

I would probably need a variable connection

Any Idea there?

btw: my connections are all defined as Cognos Data Source

Hi,

This is how I did it:



You can see the two variables - one for database server and one for database name.

Cheers!

MF.

[erwink]

Cool !
I'll test it and let you know

...but .. yes there is always a but

I use Cognos Data Source because on this way I can have two Connections for the same Data Source. One Test one Productive

I'll try if I can use a variable for the Data Source Name

Thank you

[erwink]

 :'( doesn't work. I probably miss something

I created a Job and defined a variable DataSourceName as CHAR(50) and assigned the value 'RMTSQLZUG'
I ran the job to see that the variable is assigned

[VARIABLE   - 14:11:43] . TRACE_VALUES = 'PROGRESS'
[VARIABLE   - 14:11:43] . DataSourceName = 'RMTSQLZUG'
[PROGRESS   - 14:11:43] JobStream 'ConnectDynamaicaly'; starting
[INTERNAL   - 14:11:43] Start Node 1 'Start'; Idle -> Succeeded
[PROGRESS   - 14:11:43] Done - 0 00:00:02 elapsed
jobstream -- completed (22-Sep-2015 14:11:43)

I created a cognos data source connection with Name  as {$DataSourceName}
I modified a fact build to use this new source
I added the fact build in the job
I ran the Job

[PROGRESS   - 14:09:37] JobStream 'ConnectDynamaicaly' - run id 19, audit id 435
(pid 4372)

DM-PRS-1001 Error parsing Database Alias, failed to replace special items; varia
ble 'DataSourceName' could not be found

[PROGRESS   - 14:09:37] JobStream 'ConnectDynamaicaly' Failed
jobstream -- failed (22-Sep-2015 14:09:37)

I tested by removing the fact build in the job. Same error. It seems that the error occurs as soon as I have this variable in the connection definition

Is there something I missed? I did the same test using SQLServer Data Source as on your example. And finally also using $DBNAME

attached the pkg

Thank you