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 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

Dynamic File location for DTS transfer

Does anyone know if this is possible:
I have an FTP server that will be receiving files. The directoryand file structure will be a folder with a client name (can be calledanything) and it will have files in it (these files will have the samefilenames as all the other directories. So I will have folderJimmyDoe with files a.txt, b.txt, c.txt and I will have JonnyDue withfiles a.txt, b.txt, and c.txt.
Now I'm trying to figure out a way to get that dynamic file location toa DTS package so I can import all the data from the text file into aSQL server. The way the SQL server will be set up is that eachFolder from the FTP site will be a separate Database and each file will1:1 with a table with the same name..
My biggest issue is figuring out a way to tell the DTS package the filelocation to pull all those files and then importing them to the properdatabase.
I'm not limiting the solution to DTS packages so if .NET can beincorporated to make it easier then so be it. But keep in mind Ican have up to 200 folders with 12 - 20 text files ranging fromhundreds of rows of data to many thousands of rows. And thepackage needs to be ran twice a day so time/performance is anissue.
To recap: Need DTS package that uses Dynamic file source and transfers data to Dynamic database destination.
(And I'll write slow VB.NET code to handle this before I create/manage 200+ DTS packages as a solution)
Any help at all is greatly appreciated.
How are you executing the package? If you execute dynamically, such as through the dtsrun utility or through SQLDMO code, you should be able to pass a value into a global variable. Then use the dynamic properties task to change the default global variable value to your new value.|||I've used DTS Run before so that's the only way I know how to dothat. How would I use the dynamic properties task to change thedefault global variable value? Can you give me a snippet,pseuodcode or something of how that would work?
Never heard of SQLDMO, what is that?
|||

netflash99 wrote:

Never heard of SQLDMO, what is that?

SQLDMO(SQL Server Data Management Object) Microsoft property it creates everything you do with Enterprise Manager manually through code but it uses System tables from the master to do its work so your code will be orphaned in SQL Server 2005 where those tables are really Microsoft Property you because cannot use them. DTS Run is good practice. Try the url below on using Global Variable. Hope this helps.

http://www.sqldts.com

dynamic export to excel from web

hi there,

this is my first time really using DTS packages and am trying to export some data to an excel file through a jsp page. This isn't my main problem tho...

the main problem is that i've coded the activeX script to dynamically name the file, as well as add the column headings in the excel file. The problem arises when I go to the data transform task and in the Destination tab, I can't select the worksheet that my code apparently creates.

Naturally it won't run because it will return me an error saying that the destination doesn't exist.

Any thoughts?

Thanks in advance.Are you using SQL 2000? If so, the easiest way would be to use the dynamic properites task, and set the destination of the "transform data task" to a global variable which is your sheet name. Then each time you run the package, the desination is set the the sheet or table name you have created.

Hope this helps.|||That's great help! Thanks!

My next questions are:
1. Is it possible to pop-up a Save As dialog box when you run the DTS? I tried the msoFileDialogSaveAs but of course it doesn't work because it's not an Office app.

2. Is there a way to set global variables in jsp? I've seen code for asp, but haven't found any sample codes.

Thanks again.

Originally posted by SHICKS
Are you using SQL 2000? If so, the easiest way would be to use the dynamic properites task, and set the destination of the "transform data task" to a global variable which is your sheet name. Then each time you run the package, the desination is set the the sheet or table name you have created.

Hope this helps.|||I have a question for you. Does it have to be an excel file? Can it be a Comma Seperated File, which can be viewed in excel. If it can, I would not even use DTS, I would just create a stored proc, and execute it in java and then write the records to a a .csv comma seperated file. Then you can create a link to the file location for download.

I don't know much about java, and how to interface DTS with it. I can only offer suggestions.|||I don't know if JSP supports it, but we use ADO to save a stream of data as XML. Then using an XSL-T transform, we set the file up just about any way the user wants it (comm-delimited, fixed width, different delimiters, etc).

hmscott

Originally posted by SHICKS
I have a question for you. Does it have to be an excel file? Can it be a Comma Seperated File, which can be viewed in excel. If it can, I would not even use DTS, I would just create a stored proc, and execute it in java and then write the records to a a .csv comma seperated file. Then you can create a link to the file location for download.

I don't know much about java, and how to interface DTS with it. I can only offer suggestions.|||Shicks: The problem with creating in a csv file is that if you have a large number field and you try to open the file in Excel, it won't retain the format of the number. I've had this happen on many occasion.

Here's my current situation:
I've now been able to create a dts package that exports data to an excel file. For security reasons, I saved the package as a .dts file on our server to be called by the webserver when run.

The question is.. can I set the global variables of the package by referencing the file itself rather than the package on the server? Also, can this be done in java?

Dynamic Data Source in DTS

I want to build a DTS package to transfer data between two
SQL 2000 servers. I like to set up the data source as a
variable. In another word, I want to pass in the data
source (source SQL server 2000) as a parameter to the DTS
package so that I can reuse this package somewhere else.
Is it possible? How?
Thanks in advance,
LixinIn short, you could use global variables to specify the data source. When
you invoke the package using DTSRUN, you could specify values for these
global variables. See SQLDTS.com for examples.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
What hardware is your SQL Server running on?
http://vyaskn.tripod.com/poll.htm
"Lixin Fan" <lixin2003@.hotmail.com> wrote in message
news:1d88601c388eb$05101ce0$a601280a@.phx.gbl...
I want to build a DTS package to transfer data between two
SQL 2000 servers. I like to set up the data source as a
variable. In another word, I want to pass in the data
source (source SQL server 2000) as a parameter to the DTS
package so that I can reuse this package somewhere else.
Is it possible? How?
Thanks in advance,
Lixin|||Vyas,
Thanks a lot.
I went to SQLDTS.com. However, I didn't find the example I
really need.
What I want to kow is how to set up the server name in
source server of DTS as global viriable so that I can pass
in the server name as parameter through DTSRUN? Can you
give more instructions about how to do it in EM?
Lixin
>--Original Message--
>In short, you could use global variables to specify the
data source. When
>you invoke the package using DTSRUN, you could specify
values for these
>global variables. See SQLDTS.com for examples.
>--
>HTH,
>Vyas, MVP (SQL Server)
>http://vyaskn.tripod.com/
>What hardware is your SQL Server running on?
>http://vyaskn.tripod.com/poll.htm
>
>"Lixin Fan" <lixin2003@.hotmail.com> wrote in message
>news:1d88601c388eb$05101ce0$a601280a@.phx.gbl...
>I want to build a DTS package to transfer data between two
>SQL 2000 servers. I like to set up the data source as a
>variable. In another word, I want to pass in the data
>source (source SQL server 2000) as a parameter to the DTS
>package so that I can reuse this package somewhere else.
>Is it possible? How?
>Thanks in advance,
>Lixin
>
>.
>|||OK
Let's say I have a connection to SQL Server and I call it MySQLServer. The
Global Variable that holds the name of the server is "MyConnVar". In an
Active Script task I would do.
dim PKG
dim Conn
set PKG = DTSGlobalVariables.Parent
set Conn = PKG.Connections("MySQLServer")
Conn.DataSource = DTSGlobalVariables("MyConnVar").Value
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Lixin Fan" <lixin2003@.hotmail.com> wrote in message
news:082501c38921$6977f0a0$a401280a@.phx.gbl...
> Vyas,
> Thanks a lot.
> I went to SQLDTS.com. However, I didn't find the example I
> really need.
> What I want to kow is how to set up the server name in
> source server of DTS as global viriable so that I can pass
> in the server name as parameter through DTSRUN? Can you
> give more instructions about how to do it in EM?
> Lixin
> >--Original Message--
> >In short, you could use global variables to specify the
> data source. When
> >you invoke the package using DTSRUN, you could specify
> values for these
> >global variables. See SQLDTS.com for examples.
> >
> >--
> >HTH,
> >Vyas, MVP (SQL Server)
> >http://vyaskn.tripod.com/
> >What hardware is your SQL Server running on?
> >http://vyaskn.tripod.com/poll.htm
> >
> >
> >"Lixin Fan" <lixin2003@.hotmail.com> wrote in message
> >news:1d88601c388eb$05101ce0$a601280a@.phx.gbl...
> >I want to build a DTS package to transfer data between two
> >SQL 2000 servers. I like to set up the data source as a
> >variable. In another word, I want to pass in the data
> >source (source SQL server 2000) as a parameter to the DTS
> >package so that I can reuse this package somewhere else.
> >Is it possible? How?
> >
> >Thanks in advance,
> >Lixin
> >
> >
> >.
> >

dynamic archiving

I need to set up a stored procedure or DTS that can export data to a text file - based on parametres passed when it's called. We have a scheduling server, where all our logging is stored and procedures are called. The data I need to export can be on diffierent servers in different databases.

I started with the following:

DECLARE
-- @.TableName varchar(128),
-- @.Filename varchar(200),
-- @.date_field varchar(20),
@.SQL varchar(2000)
-- SET @.TableName =

SET @.SQL = ''''+ 'bcp "SELECT * FROM '+@.TableName+ ' WHERE ' + @.Date_Field+
' > GETDATE()"'+ ' queryout "c:\test.txt" -c -q -U"sa" -P"" '+ ''''
print @.SQL

-- set @.SQL = 'EXEC master.dbo.xp_cmdshell ' + @.SQL
-- EXEC (@.SQL)

However, I'm not sure how to work in the dynamic server names. A variation of the above works fine locally on my machine - but I'm thinking I may need to use a DTS to get the servers in there...if so, does bcp even work through DTS? WHat about the variables? Does anyone have a better way of doing this?

for server name, you specify -S. Does this not work?

|||

Well, der. Thanks, I forgot about that.

I have come up with another problem, however:

I'm running BCP through a stored procedure so that the exports can be dynamic - however, I can't get the proc to fail if the bcp command fails. It returns an out put indicating failure, but won't actually fail the proc - I can't seem to use raiserror.

Is there a way to take the output from queryout and do something with it?

|||

I think I figured this out.

Still testing, however, it looks like adding "NO_OUTPUT" forces SQL server to interpret the results as either failure or success - if you don't specify this, it seems to consider the execution of xp_cmdshell rather than the result of the execution. (i.e., I would get the output results returned indicating the specifics of the error, but the value of @.@.ERROR within the proc would not change)

I could put the output results into a file, but that would complicate the process as I would then need to evaluate the output before proceeding (which would be a pain with a text file).

If anyone knows how to get the output into a table on execution, especially a temp table, that would be nice to know...

|||This might be fixed in SQL 2005, where if command inside xp_cmdshell fails, then the batch fails, but not sure, you should test it out. I'm assuming you're on SQL 2000.|||

We don't have 2005. Still on 2000.

This is terribly frustrating. :(

Any further suggestions would be greatly appreciated, as this still seems to be wonky.

|||

did you try checking the return value from xp_cmdshell?

i.e.

declare @.ret int

exec @.ret = xp_cmdshell 'bcp xxxxx'

if @.ret <> 0 raiserror ('Error!', 16,1)

go

|||

This worked great! it forces an error when run from a DTS.

In addition, I forgot that most system stuff can be saved in a temp table - i.e.,

create table ##output

(output varchar(255))

Insert ##output

(output)

exec xp_cmdshell @.SQL

So this was great! thanks so much for your help.