cancel
Showing results for 
Search instead for 
Did you mean: 

Creating a DTS package to execute logic

Former Member
0 Kudos

Does anyone have detailed instructions on how to create a DTS package (SQL 2000 and 2005) to excute logic?

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

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

Former Member
0 Kudos

kranthi

I'm trying to create a dts package in SQL2000.

Former Member
0 Kudos

Hi,

I think you are trying to modify script directly in the advanced tab of modify package. I am not sure if it works.

Open the dts and in the tasks you find a task named evmodifyscript.You need to copy the script in advanced tab, past it here n modify it.

Give it a try,

Kranthi

Former Member
0 Kudos

Kranthi

Thanks. But I can't see where and how you can do this. Could you provide step by step approach, using BPC terminology, that should be taken? Screen-shots would really beneficial, but I'm sure that they can be attached in the forum. Any further help would be greatly appreciated.

Former Member
0 Kudos

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.

Answers (4)

Answers (4)

Former Member
0 Kudos

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.

Former Member
0 Kudos

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

Former Member
0 Kudos

Did you rename your new package using sql server development tool, visual studio?

Former Member
0 Kudos

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

Former Member
0 Kudos

The Save As... function in SQL 2000 renames data manager packages including any internal package references to the old name. If you did not use this method to name your new version of the copied package, I'd suggest do that and try the execution again.

Former Member
0 Kudos

Thanks everyone for your assistance, I have managed to resolve the problem

SEdelstein
Product and Topic Expert
Product and Topic Expert
0 Kudos

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

Former Member
0 Kudos

Hi,

experience leurns me that it is better to save the logicfile as .LGF to avoid problems

Best regards,

Benjamin

Former Member
0 Kudos

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

Former Member
0 Kudos

Thanks Joost...I'll try it.

Former Member
0 Kudos

Joost

I've followed your steps, but when I try to save, I get the message:

'This version of DTS package cannot be saved directly to the package.

Please copy a dynamic script and paste to EvDTSModify task script'

I don't know what this means, and how to resolve it. Any ideas?