Dynamic Query

I use IIF in the dynamic query to dynamically change the Select, Group By, and Order By statements.

In the table grouping properties, i also use IIF to change the grouping Field.

There are no errors, the report processes OK, but the report is not grouped or shows any Field values for which i have to use dynamic query. What could be the problem? Can anybody help please.

Thanks

Can you post the dynamic query you are using, as well as the group expression? Also if you enable tracing on the database side, is the query being executed correct?|||

Thank you for answering to my problem. I did get over it after much trying. The dynamic query looks like this:

="SELECT SUM(BASE_UNIT) AS BASE_UNIT "
& IIF(Parameters!Type.Value = "Location", ", Location", IIF(Parameters!Type.Value = "Admit_Source", ", Admit_Source", ", Provider_Name")) &
" as grouping FROM TBL_EOM
WHERE (MONTH(ENTRY_DATETIME) =@.RepMonth) AND (YEAR(ENTRY_DATETIME) = @.RepYear)" &
IIF(Parameters!Pract.Value = "*** ALL ***", " ", " AND (NAME = @.Prac) ") &
" GROUP BY Provider_Name, NAME" & IIF(Parameters!Type.Value = "Location", ", Location", IIF(Parameters!Type.Value = "Admit_Source", ", Admit_Source", " "))

In layout view i have this strig for field, grouping and sorting:

IIF(Parameters!Type.Value <> "Provider", Fields!grouping.Value, " ")

Thank you

Dynamic Properties Task in DTS 2000, need to convert it to SSIS

I have a Dynamic propeties task in dts 2000 that process/executes a global variable.

The global variable basically executes a bat file.

How do i set this up in ssis. The migration failed to properly convert this task.

Please help.

Thank you.

To execute a .bat file you would use the Execute Process task.|||

I don't think I can use the process task because I have a global variable which gets set in a previous task. It is via this global variable that a bat file is called that copies a file from one location to another. The issue is that how do I execute the global variable. In dts sql 2000, the dynamice properties task is used. but in ssis that does not work. the process task does not allow you to execute/process aglobal variable....

hope I was able to explain better. Any help is appreciated.

Thanks

|||You'll have to use expressions on the Execute Process Task. You'll likely have to create two variables based off of your global variable first, though. That is, the execute process takes two arguments at a minimum, the executable (cmd.exe perhaps) and its arguments (your bat file). Then, just pass in the two new variables into the appropriate expressions (Executable & Arguments)|||

Thanks Phil,

I understand what you said, but not quite sure how to do it...

my global varaible is called gv_commandline

the value is '\\......\..\.... .bat \\..............\\......... .txt \\............\\.............\\.... .txt

(batch file) (1st arg) (2nd arg)

basically the batch file will copy a file from the source (ie the 1st arg) to the destination (ie 2nd arg)

For instance, how do I set the two new variables with values from my global variable.

How do I set the expressions

Thanks in advance.

Jinita

|||

Jain wrote:

Thanks Phil,

I understand what you said, but not quite sure how to do it...

my global varaible is called gv_commandline

the value is '\\......\..\.... .bat \\..............\\......... .txt \\............\\.............\\.... .txt

(batch file) (1st arg) (2nd arg)

basically the batch file will copy a file from the source (ie the 1st arg) to the destination (ie 2nd arg)

Thanks in advance.

Jinita

Actually, just try putting your global variable in the expression for Argument. Right click on the Execute Process Task and select properties. Find the expression parameter and click on the "...". Find "Arguments" and in that box, just drag your global variable to it. Then, click out of that and double click on the execute process task to configure it. For the executable, type in: c:\windows\system32\cmd.exe|||

Thanks Phil,

That was awesome. Looks like it worked. the task seems to work, I will test the complete package run just to make sure everything works fine.

Great. Thank you so much.

Have a great weekend :)

Jinita

|||

Hi Phil,

Apologise for reopening this issue, but I am having problems with the task. I have done what you suggested before, but when I try to execute that process task, it comes up with the cmd.exe window waiting for the command or argument. Shouldn't it take the argument and execute the whole thing. Please help.

Thank you.

|||

I am still running into the same problem. Why does the command prompt come up. I am expecting it to execute the process task since i have already provided it with an executable and arguments.

Please advice.

Thanks in advance.

Dynamic Properties Task in DTS 2000, need to convert it to SSIS

I have a Dynamic propeties task in dts 2000 that process/executes a global variable.

The global variable basically executes a bat file.

How do i set this up in ssis. The migration failed to properly convert this task.

Please help.

Thank you.

To execute a .bat file you would use the Execute Process task.|||

I don't think I can use the process task because I have a global variable which gets set in a previous task. It is via this global variable that a bat file is called that copies a file from one location to another. The issue is that how do I execute the global variable. In dts sql 2000, the dynamice properties task is used. but in ssis that does not work. the process task does not allow you to execute/process aglobal variable....

hope I was able to explain better. Any help is appreciated.

Thanks

|||You'll have to use expressions on the Execute Process Task. You'll likely have to create two variables based off of your global variable first, though. That is, the execute process takes two arguments at a minimum, the executable (cmd.exe perhaps) and its arguments (your bat file). Then, just pass in the two new variables into the appropriate expressions (Executable & Arguments)|||

Thanks Phil,

I understand what you said, but not quite sure how to do it...

my global varaible is called gv_commandline

the value is '\\......\..\.... .bat \\..............\\......... .txt \\............\\.............\\.... .txt

(batch file) (1st arg) (2nd arg)

basically the batch file will copy a file from the source (ie the 1st arg) to the destination (ie 2nd arg)

For instance, how do I set the two new variables with values from my global variable.

How do I set the expressions

Thanks in advance.

Jinita

|||

Jain wrote:

Thanks Phil,

I understand what you said, but not quite sure how to do it...

my global varaible is called gv_commandline

the value is '\\......\..\.... .bat \\..............\\......... .txt \\............\\.............\\.... .txt

(batch file) (1st arg) (2nd arg)

basically the batch file will copy a file from the source (ie the 1st arg) to the destination (ie 2nd arg)

Thanks in advance.

Jinita

Actually, just try putting your global variable in the expression for Argument. Right click on the Execute Process Task and select properties. Find the expression parameter and click on the "...". Find "Arguments" and in that box, just drag your global variable to it. Then, click out of that and double click on the execute process task to configure it. For the executable, type in: c:\windows\system32\cmd.exe|||

Thanks Phil,

That was awesome. Looks like it worked. the task seems to work, I will test the complete package run just to make sure everything works fine.

Great. Thank you so much.

Have a great weekend :)

Jinita

|||

Hi Phil,

Apologise for reopening this issue, but I am having problems with the task. I have done what you suggested before, but when I try to execute that process task, it comes up with the cmd.exe window waiting for the command or argument. Shouldn't it take the argument and execute the whole thing. Please help.

Thank you.

|||

I am still running into the same problem. Why does the command prompt come up. I am expecting it to execute the process task since i have already provided it with an executable and arguments.

Please advice.

Thanks in advance.

Dynamic Properties Task in DTS 2000, need to convert it to SSIS

I have a Dynamic propeties task in dts 2000 that process/executes a global variable.

The global variable basically executes a bat file.

How do i set this up in ssis. The migration failed to properly convert this task.

Please help.

Thank you.

To execute a .bat file you would use the Execute Process task.|||

I don't think I can use the process task because I have a global variable which gets set in a previous task. It is via this global variable that a bat file is called that copies a file from one location to another. The issue is that how do I execute the global variable. In dts sql 2000, the dynamice properties task is used. but in ssis that does not work. the process task does not allow you to execute/process aglobal variable....

hope I was able to explain better. Any help is appreciated.

Thanks

|||You'll have to use expressions on the Execute Process Task. You'll likely have to create two variables based off of your global variable first, though. That is, the execute process takes two arguments at a minimum, the executable (cmd.exe perhaps) and its arguments (your bat file). Then, just pass in the two new variables into the appropriate expressions (Executable & Arguments)|||

Thanks Phil,

I understand what you said, but not quite sure how to do it...

my global varaible is called gv_commandline

the value is '\\......\..\.... .bat \\..............\\......... .txt \\............\\.............\\.... .txt

(batch file) (1st arg) (2nd arg)

basically the batch file will copy a file from the source (ie the 1st arg) to the destination (ie 2nd arg)

For instance, how do I set the two new variables with values from my global variable.

How do I set the expressions

Thanks in advance.

Jinita

|||

Jain wrote:

Thanks Phil,

I understand what you said, but not quite sure how to do it...

my global varaible is called gv_commandline

the value is '\\......\..\.... .bat \\..............\\......... .txt \\............\\.............\\.... .txt

(batch file) (1st arg) (2nd arg)

basically the batch file will copy a file from the source (ie the 1st arg) to the destination (ie 2nd arg)

Thanks in advance.

Jinita

Actually, just try putting your global variable in the expression for Argument. Right click on the Execute Process Task and select properties. Find the expression parameter and click on the "...". Find "Arguments" and in that box, just drag your global variable to it. Then, click out of that and double click on the execute process task to configure it. For the executable, type in: c:\windows\system32\cmd.exe|||

Thanks Phil,

That was awesome. Looks like it worked. the task seems to work, I will test the complete package run just to make sure everything works fine.

Great. Thank you so much.

Have a great weekend :)

Jinita

|||

Hi Phil,

Apologise for reopening this issue, but I am having problems with the task. I have done what you suggested before, but when I try to execute that process task, it comes up with the cmd.exe window waiting for the command or argument. Shouldn't it take the argument and execute the whole thing. Please help.

Thank you.

|||

I am still running into the same problem. Why does the command prompt come up. I am expecting it to execute the process task since i have already provided it with an executable and arguments.

Please advice.

Thanks in advance.

sql

Dynamic Properties task Config file

I'm using a INI file with my Dynamic Properties task for the data sources for my connections in the package. Say the package goes from Q/A to production and the servers change. Now assuming the ini file is changed with the correct (new) server names etc. Where do we specify the location of the INI file?

This is really frustrating. Please helpHowdy,

Assuming you have a dynamic package properties task, open steps then DTSStep_DTSDynamic..... and double click on Property Name section called "Description". You can choose the Source to be "INI File" and the location is determined by the "...." button on the right hand side.

Cheers

SG|||Originally posted by sqlguy7777
Howdy,

Assuming you have a dynamic package properties task, open steps then DTSStep_DTSDynamic..... and double click on Property Name section called "Description". You can choose the Source to be "INI File" and the location is determined by the "...." button on the right hand side.

Cheers

SG

Yeah i figured that but once i move it to a different server i dont want to go through all the packages and changes the config file location. so i was wondering if it could be done through another way where promoting the package to another server w/o actually designing the packages all over again.

Thanks.|||Howdy

Well you can read all the config values from a table instaed of an ini file. So, you could just copy a table across into a database and use that as a config "module" that can be moved around.

Beyond that, I'd suggest its not straight forward.

Cheers,

SG|||I have tired that as well but once you move the DTS package to a different server the connections in the package still point to the original server. Also, for the DT properties in the package, how to specify the source connection to use for Queries in the package?

Thanks,

V|||Howdy

the problem is that you need to define local logons for the server, so each time you copy the package to a new server, you will need to manually alter the local server logon. No way around this I know of, unless all your servers, userIDs & passwords are the same.

A DT needs 2 connections : ( asuming you are copying data around on one server ) :

1 the source connection - server name & source database
2 the destination conn. - server name & destination database

To create a DT - click the soucre, then hold the control key & clikc the destination. Then while the two conenctions highlighted, do right click & choose Transform Data Task.

Cheers

SG

Dynamic Logging Properties in MS SQL DTS packages

Since I can't seem tofind the Microsoft SQL 2000 forum, I will post this here:

I currently have logging enable on several of my packages.

However, we are still in development of our packages and are reaching upwards

of 100 and logging will eventually need to be active on all of them. In

production, there will still be a development server and a production server,

both with different server names and user id/pwd.

I am looking for a way to dynamically change the logon information for the

logging so that we do not have to have someone go through and manually change

the options. I have tried using Dynamic Properties Task, but this only works on

the 2nd run of the package.

--

As a second question: can anyone explain to me why the errordescription field

in sysdtssteplog is cut short?

Here's the DTS forum:
http://groups.google.com/group/microsoft.public.sqlserver.dts/topics?lnk=srg