Using SSIS to trigger a cube build

[mrmo]

Final step,

I have managed to get the cube to build and deploy from within Transformer.

What I have now done is copied the method from an existing SSIS package with the thought that if it works for something else it'll work for my process.

BUT, I have attached the LOG files for the last two runs, one works one doesn't, the first is manual the second automated. The second is failing because of a password as I read it. But I can't see where to put it. I have set the SQL job to run from the system account, the same as the working packages.

Within the SSIS execute process task, Executable is C:\Program Files (x86)\ibm\cognos\c10\bin\cogtr.exe and arguments is -n2 -s -g "f:\BI\Models\FootfallV3Cube.pyj"

Again the same?

Could someone point me in the right direction please.



Transformer(XX.XX.XX.XX)  Tue Nov 17 16:26:01 2015

LogFileDirectory=f:\bi\logs\
ModelSaveDirectory=f:\bi\models\
DataSourceDirectory=F:\Cognos\Data\
CubeSaveDirectory=e:\bi\cube build\
DataWorkDirectory=F:\Cognos\temp\
ModelWorkDirectory=F:\Cognos\temp\
MaxTransactionNum=500000
Product locale: English (en)
Run locale: English (United Kingdom) (en-gb)
Codepage: windows-1252

Transformer  Tue Nov 17 16:26:02 2015
Transformer(XX.XX.XX.XX)  Tue Nov 17 16:26:02 2015

LogFileDirectory=f:\bi\logs\
ModelSaveDirectory=f:\bi\models\
DataSourceDirectory=F:\Cognos\Data\
CubeSaveDirectory=e:\bi\cube build\
DataWorkDirectory=F:\Cognos\temp\
ModelWorkDirectory=F:\Cognos\temp\
MaxTransactionNum=500000
Product locale: English (en)
Run locale: English (United Kingdom) (en-gb)
Codepage: windows-1252

Tue 17 Nov 2015 16:28:14   4   00000000   Start cube update.
Tue 17 Nov 2015 16:28:14   4   00000000   Initializing categories.
Tue 17 Nov 2015 16:28:14   4   00000000   Timing, INITIALIZING CATEGORIES,00:00:00
Tue 17 Nov 2015 16:28:14   4   00000000   Start processing data source 'Footfallv3~1'.
Tue 17 Nov 2015 16:28:14   4   00000000   Reading source data.
Tue 17 Nov 2015 16:28:15   4   00000000   Timing, OPEN DATA SOURCE,00:00:01
Tue 17 Nov 2015 16:28:45   4   00000000   End processing 645293 records from data source 'Footfallv3~1'.
Tue 17 Nov 2015 16:28:45   4   00000000   Timing, READ DATA SOURCE,00:00:31
Tue 17 Nov 2015 16:28:45   4   00000000   Marking categories used.
Tue 17 Nov 2015 16:28:45   4   00000000   Timing, MARKING CATEGORIES USED,00:00:00
Tue 17 Nov 2015 16:28:45   4   00000000   Updating category status.
Tue 17 Nov 2015 16:28:45   4   00000000   Processing the work file.
Tue 17 Nov 2015 16:28:46   4   000002D9   Processing cube 'Footfallv32' at location e:\bi\cube build\footfallv3cube_1.mdc
Tue 17 Nov 2015 16:28:46   4   00000000   Timing, UPDATE CATEGORY AND PROCESS WORK FILE,00:00:01
Tue 17 Nov 2015 16:28:46   4   000002D9   Start metadata update of cube 'Footfallv32'.
Tue 17 Nov 2015 16:28:46   4   00000000   Marking categories needed.
Tue 17 Nov 2015 16:28:46   4   00000000   Updating the PowerCube metadata.
Tue 17 Nov 2015 16:28:46   4   00000000   Updating the PowerCube with currency data.
Tue 17 Nov 2015 16:28:46   4   000002D9   End metadata update of cube 'Footfallv32'. 1055 categories were added to the cube.
Tue 17 Nov 2015 16:28:46   4   00000000   Timing, METADATA,00:00:00
Tue 17 Nov 2015 16:28:46   4   000002D9   Start update of cube 'Footfallv32'.
Tue 17 Nov 2015 16:28:46   4   000002D9   --- Performing Pass 0 with 645293 rows and 1054 categories remaining.
Tue 17 Nov 2015 16:28:46   4   000002D9   Selected dimension 2 for next pass of partitioning.
Tue 17 Nov 2015 16:28:46   4   00000000   Sorting the work file.
Tue 17 Nov 2015 16:28:46   4   00000000   Counting category hits.
Tue 17 Nov 2015 16:28:46   4   000002D9   End sorting 645293 records.
Tue 17 Nov 2015 16:28:46   4   000002D9   Start Count and Consolidation with 645293 rows and 1054 categories remaining.
Tue 17 Nov 2015 16:28:46   4   000002D9   End Count and Consolidation with 645293 rows and 1054 categories remaining.
Tue 17 Nov 2015 16:28:46   4   000002D9   Start Write leaving 1054 categories remaining.
Tue 17 Nov 2015 16:28:46   4   00000000   Updating the PowerCube data.
Tue 17 Nov 2015 16:28:46   4   00000000   Updating the PowerCube data.
Tue 17 Nov 2015 16:28:47   4   00000000   Performing DataBase Commit at record number 500000.
Tue 17 Nov 2015 16:28:47   4   00000000   Performing DataBase Commit at record number 645294.
Tue 17 Nov 2015 16:28:47   4   000002D9   End Write leaving 1054 categories remaining..
Tue 17 Nov 2015 16:28:47   4   00000000   Timing, CUBE UPDATE,00:00:01
Tue 17 Nov 2015 16:28:47   4   000002D9   --- Performing Pass 1 with 645293 rows and 1054 categories remaining.
Tue 17 Nov 2015 16:28:47   4   000002D9   Selected dimension 1 for next pass of partitioning.
Tue 17 Nov 2015 16:28:47   4   00000000   Counting category hits.
Tue 17 Nov 2015 16:28:47   4   000002D9   End sorting 645293 records.
Tue 17 Nov 2015 16:28:47   4   000002D9   Start Count and Consolidation with 645293 rows and 1054 categories remaining.
Tue 17 Nov 2015 16:28:47   4   000002D9   End Count and Consolidation with 2718 rows and 1054 categories remaining.
Tue 17 Nov 2015 16:28:47   4   000002D9   Start Write leaving 1054 categories remaining.
Tue 17 Nov 2015 16:28:47   4   00000000   Updating the PowerCube data.
Tue 17 Nov 2015 16:28:47   4   00000000   Updating the PowerCube data.
Tue 17 Nov 2015 16:28:47   4   00000000   Performing DataBase Commit at record number 2719.
Tue 17 Nov 2015 16:28:47   4   000002D9   End Write leaving 1054 categories remaining..
Tue 17 Nov 2015 16:28:47   4   00000000   Timing, CUBE UPDATE,00:00:00
Tue 17 Nov 2015 16:28:47   4   00000000   Committing PowerCube(s).
Tue 17 Nov 2015 16:28:47   4   00000000   Timing, CUBE COMMIT,00:00:00
Tue 17 Nov 2015 16:28:48   4   00000000   End cube update.
Tue 17 Nov 2015 16:28:48   4   00000000   Timing, TOTAL TIME (CREATE CUBE),00:00:34
Tue 17 Nov 2015 16:28:50   4   00000000   Creating directory 'f:\bi\cube_live\footfall\footfallv3cube_1'.
Tue 17 Nov 2015 16:28:50   4   00000000   Copying 'e:\bi\cube build\footfallv3cube_1.mdc' to 'f:\bi\cube_live\footfall\footfallv3cube_1\footfallv3cube_1.mdc'.
Tue 17 Nov 2015 16:28:50   4   00000000   Renaming 'f:\bi\cube_live\footfall\footfallv3cube_1' to 'f:\bi\cube_live\footfall\footfallv3cube_1__20151117162850'.
Tue 17 Nov 2015 16:28:50   4   00000000   Creating stub 'f:\bi\cube_live\footfall\footfallv3cube_1.mdc'.
Tue 17 Nov 2015 16:28:50   4   00000000   Creating version file 'f:\bi\cube_live\footfall\footfallv3cube_1__20151117162850.ver'.
Tue 17 Nov 2015 16:28:50   4   00000000   (TR3818) PowerCube 'Footfallv32' has been deployed to version 'footfallv3cube_1__20151117162850'.
Tue 17 Nov 2015 16:28:50   4   00000000   
Tue 17 Nov 2015 16:28:52   4   00000000   (TR3819) PowerCube(s) have been deployed successfully. [->OK]
Transformer  Tue Nov 17 16:29:25 2015
Transformer(XX.XX.XX.XX)  Wed Nov 18 02:01:24 2015

LogFileDirectory=f:\bi\logs\
ModelSaveDirectory=f:\bi\models\
DataSourceDirectory=F:\Cognos\Data\
CubeSaveDirectory=e:\bi\cube build\
DataWorkDirectory=F:\Cognos\temp\
ModelWorkDirectory=F:\Cognos\temp\
MaxTransactionNum=500000
Product locale: English (en)
Run locale: English (United Kingdom) (en-gb)
Codepage: windows-1252

Transformer  Wed Nov 18 02:01:24 2015
Transformer(XX.XX.XX.XX)  Wed Nov 18 02:01:24 2015

LogFileDirectory=f:\bi\logs\
ModelSaveDirectory=f:\bi\models\
DataSourceDirectory=F:\Cognos\Data\
CubeSaveDirectory=e:\bi\cube build\
DataWorkDirectory=F:\Cognos\temp\
ModelWorkDirectory=F:\Cognos\temp\
MaxTransactionNum=500000
Product locale: English (en)
Run locale: English (United Kingdom) (en-gb)
Codepage: windows-1252

Wed 18 Nov 2015 02:01:25   0   00000000   (TR3703) The user ID or password is either missing or invalid. Please re-enter the credentials.
Wed 18 Nov 2015 02:01:25   2   00000000   (TR0116) Transformer can't open the file Footfallv3~1.   [->OK]
Wed 18 Nov 2015 02:01:25   4   00000000   Creating directory 'f:\bi\cube_live\footfall\footfallv3cube_1'.
Wed 18 Nov 2015 02:01:25   4   00000000   Copying 'e:\bi\cube build\footfallv3cube_1.mdc' to 'f:\bi\cube_live\footfall\footfallv3cube_1\footfallv3cube_1.mdc'.
Wed 18 Nov 2015 02:01:25   4   00000000   Renaming 'f:\bi\cube_live\footfall\footfallv3cube_1' to 'f:\bi\cube_live\footfall\footfallv3cube_1__20151118020125'.
Wed 18 Nov 2015 02:01:25   4   00000000   Deleting 'f:\bi\cube_live\footfall\footfallv3cube_1__20151117162850.ver'.
Wed 18 Nov 2015 02:01:25   4   00000000   Creating version file 'f:\bi\cube_live\footfall\footfallv3cube_1__20151118020125.ver'.
Wed 18 Nov 2015 02:01:25   4   00000000   (TR3818) PowerCube 'Footfallv32' has been deployed to version 'footfallv3cube_1__20151118020125'.
Wed 18 Nov 2015 02:01:25   4   00000000   
Transformer  Wed Nov 18 02:01:25 2015

[mrmo]

just a further note, when I say manually I am logged into Transformer, if I try and run the job from BIDS I get the same failed log entry. So I am guessing the issue is within the SSIS part of the process rather than when I have created a job to run it.

[mrmo]

Next attempt, ran the cogtr.exe from cmd with the arguments as per above and still not running, so what am I doing wrong??

[bdbits]

Try using the -l option to pass credentials to the job. Also, I have found it sometimes helps to add the login to the sign-ons window in the default view.

Also remember to consider the credentials that will be used to run the scheduled task or SSIS job. It is likely not - and should not be - your logon being used. You should test with those credentials once you know you have the proper command line built.

[cognostechie]

Next attempt, ran the cogtr.exe from cmd with the arguments as per above and still not running, so what am I doing wrong??

What happens when you run it from within Transformer? Does it prompt you to provide sign-on info? If so, that's why it is working from
within Transformer and not working from outside Transformer. There is a sign-on window in Transformer where you can embed the sign-on.
Once you do that then try running it from cmd. If it works then it should work from SSIS too because all SSIS should do is to fire up the script at a certain time after your ETL load finishes. You can also create a script with the commands , save it as a .bat file and test it from cmd. Once it starts running then
call that script from SSIS.
 

[mrmo]

Cognostechie. that makes sense, but where is this window where i can embed the credentials?

bdbits, from reading the manual, the login credentials APPEAR to be plane text??? a service account is being used to run the jobs.

[bdbits]

cognostechie and I were talking the same thing, it is a window in the default Transformer view. (I think you invoke that under the Edit menu, no access to verify at the moment.)

And yes, if you use them on the command line, they will be plain text. This might be unacceptable, I know, so try adding the credentials to the model first to see if that fixes your problem. If not try adding them to the command line, and if it works then we will know there is a permissions issue.

[cognostechie]

As bdbits said, the default view of Transformer should show that window and if it does not, then from the 'View' menu, select 'Signons'.

[mrmo]

Thanks, will have a look in the morning and see if that fixes the issue.

[mrmo]

Something happened, first run I got this as the result.


LogFileDirectory=f:\bi\logs\
ModelSaveDirectory=f:\bi\models\
DataSourceDirectory=F:\Cognos\Data\
CubeSaveDirectory=e:\bi\cube build\
DataWorkDirectory=F:\Cognos\temp\
ModelWorkDirectory=F:\Cognos\temp\
MaxTransactionNum=500000
Product locale: English (en)
Run locale: English (United Kingdom) (en-gb)
Codepage: windows-1252

Thu 19 Nov 2015 09:28:40   4   00000000   Start cube update.
Thu 19 Nov 2015 09:28:40   4   00000000   Initializing categories.
Thu 19 Nov 2015 09:28:40   4   00000000   Timing, INITIALIZING CATEGORIES,00:00:00
Thu 19 Nov 2015 09:28:40   4   00000000   Start processing data source 'Footfallv3~1'.
Thu 19 Nov 2015 09:28:40   4   00000000   Reading source data.
Thu 19 Nov 2015 09:28:42   2   00000000   QE-DEF-0030 Expression parsing error.
Thu 19 Nov 2015 09:28:42   2   00000000   QE-DEF-0359 The query contains a reference to at least one object '[Output].[Output].[Hour]' that does not exist.
Thu 19 Nov 2015 09:28:42   4   00000000   End processing 0 records from data source 'Footfallv3~1'.
Thu 19 Nov 2015 09:28:42   4   00000000   Timing, READ DATA SOURCE,00:00:02
Thu 19 Nov 2015 09:28:42   4   00000000   End cube update.
Thu 19 Nov 2015 09:28:42   4   00000000   Timing, TOTAL TIME (CREATE CUBE),00:00:02
Thu 19 Nov 2015 09:28:42   2   00000000   (TR0168) Transformer could not open the data source [Footfallv3~1].  [->OK]
Thu 19 Nov 2015 09:28:42   4   00000000   Creating directory 'f:\bi\cube_live\footfall\footfallv3cube_1'.
Thu 19 Nov 2015 09:28:42   4   00000000   Copying 'e:\bi\cube build\footfallv3cube_1.mdc' to 'f:\bi\cube_live\footfall\footfallv3cube_1\footfallv3cube_1.mdc'.
Thu 19 Nov 2015 09:28:42   4   00000000   Renaming 'f:\bi\cube_live\footfall\footfallv3cube_1' to 'f:\bi\cube_live\footfall\footfallv3cube_1__20151119092842'.
Thu 19 Nov 2015 09:28:42   4   00000000   Deleting 'f:\bi\cube_live\footfall\footfallv3cube_1__20151119083052.ver'.
Thu 19 Nov 2015 09:28:42   4   00000000   Creating version file 'f:\bi\cube_live\footfall\footfallv3cube_1__20151119092842.ver'.
Thu 19 Nov 2015 09:28:42   4   00000000   (TR3818) PowerCube 'Footfallv32' has been deployed to version 'footfallv3cube_1__20151119092842'.
Thu 19 Nov 2015 09:28:42   4   00000000   
Transformer  Thu Nov 19 09:28:43 2015
Transformer(XX.XX.XX.XX)  Thu Nov 19 09:32:55 2015


and second run I got

Transformer  Thu Nov 19 09:34:35 2015
Transformer(XX.XX.XX.XX)  Thu Nov 19 09:34:35 2015

LogFileDirectory=f:\bi\logs\
ModelSaveDirectory=f:\bi\models\
DataSourceDirectory=F:\Cognos\Data\
CubeSaveDirectory=e:\bi\cube build\
DataWorkDirectory=F:\Cognos\temp\
ModelWorkDirectory=F:\Cognos\temp\
MaxTransactionNum=500000
Product locale: English (en)
Run locale: English (United Kingdom) (en-gb)
Codepage: windows-1252

Thu 19 Nov 2015 09:34:35   4   00000000   Start cube update.
Thu 19 Nov 2015 09:34:35   3   000002D9   (TR0787) The locking of cube 'e:\bi\cube build\footfallv3cube_1.mdc' failed. It is probably in use by another application. Select Yes to retry, No to create the cube using a temporary name or Cancel. [->Cancel]
Thu 19 Nov 2015 09:34:36   4   00000000   End cube update.
Thu 19 Nov 2015 09:34:36   4   00000000   Timing, TOTAL TIME (CREATE CUBE),00:00:01
Thu 19 Nov 2015 09:34:36   4   00000000   (TR0102) The process or action was cancelled at your request. [->OK]
Thu 19 Nov 2015 09:34:36   4   00000000   Creating directory 'f:\bi\cube_live\footfall\footfallv3cube_1'.
Thu 19 Nov 2015 09:34:36   4   00000000   Copying 'e:\bi\cube build\footfallv3cube_1.mdc' to 'f:\bi\cube_live\footfall\footfallv3cube_1\footfallv3cube_1.mdc'.
Thu 19 Nov 2015 09:34:36   4   00000000   Renaming 'f:\bi\cube_live\footfall\footfallv3cube_1' to 'f:\bi\cube_live\footfall\footfallv3cube_1__20151119093436'.
Thu 19 Nov 2015 09:34:36   4   00000000   Deleting 'f:\bi\cube_live\footfall\footfallv3cube_1__20151119093257.ver'.
Thu 19 Nov 2015 09:34:36   4   00000000   Creating version file 'f:\bi\cube_live\footfall\footfallv3cube_1__20151119093436.ver'.
Thu 19 Nov 2015 09:34:36   4   00000000   (TR3818) PowerCube 'Footfallv32' has been deployed to version 'footfallv3cube_1__20151119093436'.
Thu 19 Nov 2015 09:34:36   4   00000000   
Transformer  Thu Nov 19 09:34:36 2015


Can anyone offer some guidance as too what may be going wrong now?

[mrmo]

Well I have got it working now, I am not quite sure what was wrong as the item it was telling me didn't exist certainly looked like it existed in the Framework Manager package?? But when I deleted it, I got the cube to build and deploy from the command line, and then got it to build and deploy from the SSIS package.
That leaves one last step, to see if the JOB runs ok tomorrow morning.

At least I feel like I have achieved something today!