on 09-05-2008 12:01 PM
Does anyone have detailed instructions on how to create a DTS package (SQL 2000 and 2005) to excute logic?
Hi,
are you trying to create a dts or ssis to run the logic?
If it is SSIS, open the dtsx package using business development studio n click ssis on menu n go to variables n on tool bar select add variable and name it as MODIFYSCRIPT(case sensitive) of string data type n save the dtsx back to the datamanager files. n using data manager manage package options, assign the dtsx to package n go for modify package n advanced tab. This is where you type the evmodify script for ssis.
Hope This Helps,
kranthi
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
If you are using sql2000, then the Advanced button in the BPC> eData> Organize is likely why you are getting the error you got.
For sql2000, DTS packages, you will need to first copy the existing BPC Default Logic Package ( a DTS version not a DTSX). Then in SQL you need to open the new package to edit. If you need to know how to modify a SQL 2000 DTS package, I have not personally ever modified one, but I've reviewed some and there's some good free information on the web that should help.
Once opened in the SQL2000 DTS editor, there'll be an evDTSModifyPkg Task. The BPC Data Manager User guide has some more detailed information about the Task, but basically if you open the task for Editing (usually a double-click or mouse menu Edit option), there should be a Script value available for editing. Inside the Text of the Script there'll be a reference to a logic file (ie: default.lgf). That reference needs to be changed to the specific logicfile you want to execute. Change and save all the way out, saving the DTS package.
If that's the only difference between your package and the default package then you should be done. In BPC >eData >Organize package, just make sure there's a BPC package added that points to your modified DTS package.
The only aside I'd like to add is that renaming packages using the File System rename method is not always complete. At least in my experience with SSIS packages, you really should use the Package editor (ie: Visual Studio) to rename (File > Save As) the package. There are some internal package references to the package name and a File System (ie: Windows Explorer) rename does not update those references. The internal package name references are used when a package executes to create some logging information.
Now, if you're talking about a SQL2005, SSIS package, then you need to go thru the steps Joost outlined and use the Advanced tab because the evDTSModifyPkg Task was not converted like other BPC DTS task, instead if was replaced with the use of a package variable named MODIFYSCRIPT and the Advanced tab editor to modify it. Which the instructional guide referenced earlier in this post should server as a guide.
The easiest and fastest way to create either a DTS or an SSIS package for running logic is to copy one of the existing simple packages that come with the software, such as ICELIM or FXTRANS. Once you copy the package, you will need to modify the name of the logic file requested. In SQL 2005, the name is in the MODIFYSCRIPT section, inside managing the DM packages. In DTS, I think the process is to modify the TASK of the DTS package that has the file name, such as FXTRANS.LGL, just switch it to your new logic file.
Just remember, that these are simple packages and if you require additional steps, those would be added independently of the copied package.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Petar
Thanks; but I get an error.
I made a copy of the IC Eliminations DTS, using SQL Server Enterprise Manger I changed the logic file reference in the task:
TASK(Execute formulas,LOGICFILE,%APPPATH%\..\AdminApp\%APP%\Refuse.lgx)
and
TASK(ASSIGN PARAMETERS,...LOGICFILE,%APPPATH%\..\AdminApp\%APP%\Refuse.lgx...
Saved the file.
I then add the package using data manager, but when I click on the file with a magnifying glass icon, I get the message:
An error occurred when adding a new package. This DTS file does not contain the requested Refuse Calculations package. Valid package names in this DTS file are: IC Eliminations, IC Eliminations
I'm starting to get quite annoyed with this problem! Like you said, it should just be a case of copying and changing the logic reference.
Any ideas?
Parminder
Sara
In Enterprise Manager...highlighted Data Transformation Services...right clicked...seleced Open Package...opened the copied xxx.dts file...clicked on the assign Parameters...changed the lgx reference...clicked Disply Pkg...selected Task...changed the lgx reference...saved the file.
Parminder
Hello-
A good instructional guide can be found on the CPM Wiki:
[https://www.sdn.sap.com/irj/sdn/wiki?path=/display/bpx/corporatePerformanceManagement%28CPM%29How-to+Guides&]
Look for the document title "How to Build SSIS Packages for BPC 5.0";. Its a little dated but a worthwhile read.
Regards,
Sheldon
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi,
experience leurns me that it is better to save the logicfile as .LGF to avoid problems
Best regards,
Benjamin
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Here is the short explanation how you can do it:
-Copy the default logic package
-Add it to you package list via eData - modify package list
-Modify the package via eData - modify package list, right click on the package - edit - click the second button next to the name of the package - choose the advanced button.
-Specify LOGICMODE to 1 and in the LOGICFILE parameters you specify the LGF/LGX file you want to run, so for exampe "LOGICFILE,%appath%\..\adminapp\%APP%\logicfile.lgx"
-Save the package and it should work
-Joost
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.