cancel
Showing results for 
Search instead for 
Did you mean: 

BPC MS Add Messages to DataManager Log from SSIS (DTSX)

GFV
Active Contributor
0 Kudos

Hi guys,

I'm not able to find any solution to these requirements, in BPC 10 MS.

We would like to:

  1. stop DM Package execution based on a custom DTSX (depending on some circumstances)
  2. return messages to DM Package Log.

There are some similar threads, but no one with a valid solution:

Did anybody of you found a proper solution?

The only (partial) solution we found is how to stop execution, using a Script with:


Dts.TaskResult = (int)ScriptResults.Failure;

But we are still unable to return messages to DM Package Log (second requirement).

Many thanks in advance

GFV

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi Gianfranco,

I'm using BPC MS 7.5, and this is the approach I'm currently using to address your requirements as I understand them:

1. In several of my packages, I add a constraint option in the workflow between two tasks.  If a specified condition is not met, then execution of the remaining tasks is stopped.  Note - in some cases, I only want to skip a subset of tasks.  In this case, I create a container containing those tasks, and put the constraint option at the beginning.  If triggered, the tasks within the container will not execute, but the remaining tasks in the package will execute.

2. To return messages to the Package Log, I use the @LogTable.  In my particular requirement, I am updating a dimension table, and want to report the new members which are being added.  To accomplish this, I use the Logic Task to call a script logic package.  The script logic is a one-liner which calls a stored procedure:

*spLOG_NEW_MEMBERS('%LOGTABLE%')

The logic call within the stored procedure to actually write to the log file is similar to this:

SET @SQL = 'INSERT INTO ' + @LOGTABLE + ' <whatever text you want>'


EXECUTE SP_EXECUTESQL @SQL

In the package, I also send an email, and generally have 2 send tasks:  one if the package ran successfully, and a second if there is a failure.  In each case, you can pass variables to the email to provide any additional details, or error message.

I hope this helps,

Ray

GFV
Active Contributor
0 Kudos

Dear Ray,

first of all thanks for your reply. Then coming to your answers:

1. Yes it is clear, we will add the script task to generate failure DTS failure

2. We tried adding a "BPC Task Logic" that invokes a Script Logic and it seems to work.

What is unclear to me, in you example concerning "new member" logging is:


- How do you send Memebr ID's to the stored procedure?

Are you using a second parameter / stored procedure?


- Should it work without a "BPC Task Logic"?

It is a bit cumbersome having a task that invokes a script logic that calls a stored procedure to post a message ....


- If the message contains the string *ERROR* the DM package runs into Error. Is there a key word to generate a warning?


Thanks for cooperation


GFV




Former Member
0 Kudos

Hi Gianfranco,

We don't send the member ID's to the stored procedure.  The first part of the package creates a staging table (it closely resembles the mbr table of the dimension we're updating) and loads all members and any associated property values (such as Parent ID) that are available from the source systems.  The stored procedure then can retrieve any member ID from the staging table that are not in the current dimension mbr table --- these are the new members to be added.  Additionally, it does a quick comparison between the property values in the staging table and the corresponding property values in the current mbr table, and can generate a message on total number of rows that will be updated.

I agree that it's cumbersome using the script logic that calls a stored procedure...   It appears that the log filename is generated with each run (essentially a TEMPFILE).  If I knew the BPC parameter associated with the log filename, then it would be fairly easy to add that as a global variable in the SSIS package and access the file directly from the package.  When I look through the generated INFO statements in the packages, I don't see anything resembling a file name pointing to the log table.  So, the script logic approach is the best alternative for now that I know of.

Ray

GFV
Active Contributor
0 Kudos

Hi Ray,

as per my comprehension (and you seem to confirm this) using a stored procedure within a script logic, invoked by a BPC Task Logic is the only (cumbersome) solution.

I was hoping to manage messages to log in a more flexible way, from DTSX.

Thanks a lot for cooperation!

GFV

Answers (0)