Creating a DTS package to execute logic

[Former Member]

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

[Former Member · Accepted solution]

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 · Accepted solution]

kranthi

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

[Former Member · Accepted solution]

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 · Accepted solution]

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 · Accepted solution]

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.