TM1 Turbo Integrator is quite limited when it comes to loading data from different sources and transforming and cleaning the data before it can be loaded to cubes. Therefore you might have a dedicated data repository for your TM1 solution for example a data warehouse. Sometimes you need to refresh the DW data before it can be loaded to your TM1 solution. Then you face the question: how to manually execute data warehouse refresh ETL jobs? Of course you can always use SQL Server Management Studio and run the jobs there but if there is a need for the end users to manually refresh the data during working hours it would be nice to provide them a single-platform solution do accomplish this.
So lets face it: maybe you already have some user interface for the end-users to execute some TM1 solution’s internal TI-processes. It could be via TM1 Perspectives web sheets on TM1Web, straight from TM1 Architect etc. Wouldn’t it be nice to have the ability to run external DW loading processes from the same place? Here is how to accomplish it.
Set up the logins and jobs in SQL Server
First of all you need to ensure that the user account that will be used when from TI process connects SQL Server has all the needed permissions in the SQL Server instance. At least the three following things must be ensured:
- Login has SQLAgentUserRole checked in the msdb database. Then the login will have permissions on only local jobs and job schedules that it owns
- Login has the required permissions to access the source files etc. That can be solved by defining a proxy account and I have described it in my previous blog post.
- The job must be owned by the same user you will use when connecting from TM1. This is because we don’t want to throw admin permissions around and we want the user to have only rights to one or couple of jobs not all of them
Set up TI process to run the job: zSQLJob_StartJob
To accomplish we need two different TI processes: one to actually execute the job and one to determine whether the job execution has ended or not. Let’s start with the main process zSQLJob_StartJob. It has one parameter p_sql_job_name which will contain the name of the job that will be executed. The process itself is quite straightforward and here is the complete code:
What it does is basically just opens the ODBC connection to the msdb database of the SQL Server instance and then executes the job. The magic here is that the process won’t finish until the job on SQL Server has finished. To have this functionality we need to poll the msdb database every 5 seconds and find out if the job has finished or not.
Set up TI process to determine the status of the running job: zSQLJob_CheckJobStatus
To find out whether the job has finished or not we just query the job status directly from the msdb database. To do that we use the following SQL query and put it in the “Data Source” tab like this:
WHEN EXISTS (
select job.Name, job.job_ID, job.Originating_Server,
activity.run_requested_Date, datediff(minute, activity.run_requested_Date, getdate()) as Elapsed
from msdb.dbo.sysjobs_view job
inner join msdb.dbo.sysjobactivity activity
on (job.job_id = activity.job_id)
where run_Requested_date is not null and stop_execution_date is null
and job.name = 'JOHIDW_Update_Publishing' ) THEN 1 ELSE 0 END AS JOB_RUNNING_STATE
The only magic with this process is to save the single result value to the global variable vnJOBSTATE_global that we defined in the previous master process. If the value is 1 it means that the job is still running and the master process keeps polling the msdb database every 5 seconds. When the job finishes the value turns to 0 and the master process can finish as well.
There may be times when you need to execute SQL Server jobs to make sure your data to be loaded to TM1 is up-to-date. With this example you can do a nice and easy integration between TM1 and SQL Server and allow end-users to execute simple SQL Server jobs if needed.
1 Comment | tags: execute, ibm cognos tm1, integration services, job, jobs, sql server, sql server agent, ssis, tm1 | posted in Integration Services (SSIS), SQL Server, TM1
Processing source files can be tricky at some times. At development phase you could have some local copies of the source files lying on the development server but when it comes the time of production use the source files could be anywhere. If you’re lucky, some 3rd party tool copies the files directly to your SSIS server but some times you need to access the file(s) from a shared folder somewhere in the network by yourself. When this is the case the next thing you need to worry about is authentication. See, some organizations have a very strict authentication policy and you may be given a specific domain user account to use when accessing the files over the network. How to handle this kind of situations when you have production SSIS instance running and you need multiple different user accounts to fetch files over the network?
The problem: SQL Server Agent Service account has insufficient privileges
The problem is that by default the SSIS packages ran scheduled by the SQL Server agent are executed under the SQL Server Agent Service Account. It may be a local user account (it shouldn’t, but well, you know…) or it just lacks the required permissions that you must have to fetch the source files from remote shared folder. The classic (~ bad) solution is to give your SQL Server Agent Service Account the (full control) permissions and privileges over the entire network to fool around there and there. But this is just bad policy and can lead to severe problems when things go wrong (user account accidentally deletes important files etc.). Usually you’re just given a specific user account to use to fetch the source files. Now what? How to configure SSIS package execution to use your specific user account rather than the default Agent Service account? Here’s how.
The solution: SQL Server stored credentials and proxies
First you need to create new credentials entry under Security > Credentials > New credential. Just type in the specific (domain) username and password and save.
Then we’ll create a new SSIS Execution Proxy under SQL Server Agent > Proxies > SSIS Package Execution > New proxy. There you will need to give the proxy a describing name and then map it to the credentials we’ve just created before. Then you need to map it to subsystem(s) by checking “SQL Server Integration Services Package“.
Finally we’re ready to set up the SQL Server Agent Job that will exceute the package that processes the source file(s). When you’re editing the job step properties just choose the newly created proxy from “Run as”-combobox and you’re ready to go! Now the SSIS package is exceuted under the credentials of your specific domain account and the permissions shouldn’t be an issue anymore.
1 Comment | tags: credential proxy, credentials, integration services, job, proxies, proxy, sql agent, sql server, sql server agent job, sql server agent service account, sql server job, ssis, stored, stored credentials | posted in Integration Services (SSIS)
Once and a while you need to “post process” your source files for example moving them into archive directory etc. In such directory you might then end up having dozens of files so you need to rename them also. The most common way to achieve a robust and clear formula of renaming files is to add a timestamp inside the filename.
In SSIS (SQL Server Integration Services) there is no sophisticated way (read: nice string/date function) to do this but leveraging expressions and string manipulating functions you can have a nice one like the one I’ve used:
(DT_WSTR, 4) (DATEPART( "yyyy", @[System::StartTime] )) +
RIGHT("0" + (DT_WSTR, 2) (DATEPART( "mm", @[System::StartTime] )), 2) +
RIGHT("0" + (DT_WSTR, 2) (DATEPART( "dd", @[System::StartTime] )), 2) +
RIGHT("0" + (DT_WSTR, 2) (DATEPART( "Hh", @[System::StartTime] )), 2) +
RIGHT("0" + (DT_WSTR, 2) (DATEPART( "mi", @[System::StartTime] )), 2) +
RIGHT("0" + (DT_WSTR, 2) (DATEPART( "ss", @[System::StartTime] )), 2)
Now, just add this expression as a pre- or postfix to your processed file and you end up with a nice and clean result like: sourcefile_20130412_082024.csv
Leave a comment | tags: filename, integration services, sql server, ssis, timestamp | posted in Integration Services (SSIS)