How can I process each row in result set to access properties on another package o

Take a look at the DynamicProperties task. This will allow you to set DTS
properties based query that returns a scalar value. You'll need to specify
a separate query for each property.
Hope this helps.
Dan Guzman
SQL Server MVP
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:O4WyV0zSEHA.3332@.tk2msftngp13.phx.gbl...
> (SQL Server 2000, SP3a)
> Hello all!
> I have a DTS package that I'm working with, in which I have a query that I
want to invoke
> on a target SQL Server that will return a handful of rows. For each row,
I want to set
> some package properties (on another object in the package). What would be
the best
> approach to this? I thought that I might use the "Transform Data Task",
even though I
> don't really have a "Destination", per se (that is, I want to process each
"Source" record
> via an ActiveX script).
> However, when I try and do this, I seem to be getting an error when I
execute that
> "Transform Data Task" step (something akin to "Execution Cancelled by
User").
> Is there some other way that I should approach this?
> Regards,
> John Peterson
>Thanks, Dan -- but I can't seem to get my head around your suggestion. Basi
cally, what I
want is to be able to specify a Source Query that would return a bunch of ro
ws. Then, for
each row, I want to invoke some ActiveX snippet withOUT doing anything to a
"Destination".
I don't see that it's too easy with DTS...
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:u8ri%23s0SEHA.3608@.TK2MSFTNGP11.phx.gbl...
> Take a look at the DynamicProperties task. This will allow you to set DTS
> properties based query that returns a scalar value. You'll need to specif
y
> a separate query for each property.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:O4WyV0zSEHA.3332@.tk2msftngp13.phx.gbl...
> want to invoke
> I want to set
> the best
> even though I
> "Source" record
> execute that
> User").
>|||In article <OEs7OZ4SEHA.3852@.TK2MSFTNGP10.phx.gbl>, "John Peterson" <j0hnp@.comcast.net> wrot
e:
>Thanks, Dan -- but I can't seem to get my head around your suggestion.
> Basically, what I
>want is to be able to specify a Source Query that would return a bunch of r
ows.
> Then, for
>each row, I want to invoke some ActiveX snippet withOUT doing anything to a
> "Destination".
>I don't see that it's too easy with DTS...
>
Just do it in a VBScript task.
Open a recordset.
Loop thru it and do whatever you want during each loop.|||> Thanks, Dan -- but I can't seem to get my head around your suggestion.
Basically, what I
> want is to be able to specify a Source Query that would return a bunch of
rows. Then, for
> each row, I want to invoke some ActiveX snippet withOUT doing anything to
a "Destination".
> I don't see that it's too easy with DTS...
Sorry, but I don't understand what you mean by <withOUT doing anything to a
"Destination">. Please elaborate.
If you want to assign many properties from a single query, below is an
example of the ActiveX script technique suggested by b_43@.hotmail.com.
CREATE TABLE DTSPackageProperties
(
PackageName varchar(255) NOT NULL,
ObjectName varchar(255) NOT NULL,
PropertyName varchar(255) NOT NULL,
PropertyValue varchar(255) NOT NULL,
)
ALTER TABLE DTSPackageProperties
ADD CONSTRAINT PK_DTSPackageProperties
PRIMARY KEY(PackageName, ObjectName, PropertyName)
INSERT INTO DTSPackageProperties
VALUES('MyPackage', 'MySource', 'DataSource',
'C:\InputFiles\MyInputFile.txt')
INSERT INTO DTSPackageProperties
VALUES('MyPackage', 'MyDestination', 'DataSource',
'C:\OutputFiles\MyOutputFile.txt')
Function Main()
Dim conn, rs, sqlQuery
Set conn = CreateObject("ADODB.Connection")
conn.Open "Provider=SQLOLEDB;" & _
"Data Source=MyServer;" & _
"Integrated Security=SSPI;" & _
"Initial Catalog=MyDatabase"
sqlQuery = "SELECT ObjectName, PropertyValue"
sqlQuery = sqlQuery + " FROM DTSPackageProperties"
sqlQuery = sqlQuery + " WHERE PackageName = '"
sqlQuery = sqlQuery + DTSGlobalVariables.Parent.Name
sqlQuery = sqlQuery + "' AND PropertyName = 'DataSource'"
Set rs = conn.Execute(sqlQuery)
Do While rs.EOF = False
DTSGlobalVariables.Parent.Connections(rs.Fields("ObjectName").Value).DataSou
rce = _
rs.Fields("PropertyValue").Value
rs.MoveNext
Loop
rs.Close
conn.Close
Set rs = Nothing
Set comm = Nothing
Main = DTSTaskExecResult_Success
End Function
The alternative DynamicProperties task method would use the following
queries to assign the properties.
SELECT PropertyValue
FROM DTSPackageProperties
WHERE
PackageName = 'MyPackage' AND
ObjectName = 'MySource' AND
PropertyName = 'DataSource'
SELECT PropertyValue
FROM DTSPackageProperties
WHERE
PackageName = 'MyPackage' AND
ObjectName = 'MyDestination' AND
PropertyName = 'DataSource'
Hope this helps.
Dan Guzman
SQL Server MVP
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:OEs7OZ4SEHA.3852@.TK2MSFTNGP10.phx.gbl...
> Thanks, Dan -- but I can't seem to get my head around your suggestion.
Basically, what I
> want is to be able to specify a Source Query that would return a bunch of
rows. Then, for
> each row, I want to invoke some ActiveX snippet withOUT doing anything to
a "Destination".
> I don't see that it's too easy with DTS...
>
> "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> news:u8ri%23s0SEHA.3608@.TK2MSFTNGP11.phx.gbl...
DTS[vbcol=seagreen]
specify[vbcol=seagreen]
that I[vbcol=seagreen]
row,[vbcol=seagreen]
would be[vbcol=seagreen]
Task",[vbcol=seagreen]
each[vbcol=seagreen]
>|||Thanks Dan (and bb_43)!
I had hoped there would have been a simpler solution in the context of exist
ing DTS
objects, rather than having to write a lot of code. Alas, it seems like it'
s not quite
the case, even though DTS seems uniquely qualified to do this type of thing
(almost).
Since it can use a Connection to issue a query on that remote server and pro
cess the rows.
The only problem is that both the "Transform Data Task" and "Data Driven Que
ry Task" seem
to *require* a "destination" object; that you can't simply have an ActiveX t
ransformation
script for each row without having the data ultimately going somewhere.
Thanks again!
John Peterson
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:Oj%23iSG9SEHA.3476@.tk2msftngp13.phx.gbl...
> Basically, what I
> rows. Then, for
> a "Destination".
> Sorry, but I don't understand what you mean by <withOUT doing anything to
a
> "Destination">. Please elaborate.
> If you want to assign many properties from a single query, below is an
> example of the ActiveX script technique suggested by b_43@.hotmail.com.
>
> CREATE TABLE DTSPackageProperties
> (
> PackageName varchar(255) NOT NULL,
> ObjectName varchar(255) NOT NULL,
> PropertyName varchar(255) NOT NULL,
> PropertyValue varchar(255) NOT NULL,
> )
> ALTER TABLE DTSPackageProperties
> ADD CONSTRAINT PK_DTSPackageProperties
> PRIMARY KEY(PackageName, ObjectName, PropertyName)
> INSERT INTO DTSPackageProperties
> VALUES('MyPackage', 'MySource', 'DataSource',
> 'C:\InputFiles\MyInputFile.txt')
> INSERT INTO DTSPackageProperties
> VALUES('MyPackage', 'MyDestination', 'DataSource',
> 'C:\OutputFiles\MyOutputFile.txt')
> Function Main()
> Dim conn, rs, sqlQuery
> Set conn = CreateObject("ADODB.Connection")
> conn.Open "Provider=SQLOLEDB;" & _
> "Data Source=MyServer;" & _
> "Integrated Security=SSPI;" & _
> "Initial Catalog=MyDatabase"
> sqlQuery = "SELECT ObjectName, PropertyValue"
> sqlQuery = sqlQuery + " FROM DTSPackageProperties"
> sqlQuery = sqlQuery + " WHERE PackageName = '"
> sqlQuery = sqlQuery + DTSGlobalVariables.Parent.Name
> sqlQuery = sqlQuery + "' AND PropertyName = 'DataSource'"
> Set rs = conn.Execute(sqlQuery)
> Do While rs.EOF = False
> DTSGlobalVariables.Parent.Connections(rs.Fields("ObjectName").Value).DataS
ou
> rce = _
> rs.Fields("PropertyValue").Value
> rs.MoveNext
> Loop
> rs.Close
> conn.Close
> Set rs = Nothing
> Set comm = Nothing
> Main = DTSTaskExecResult_Success
> End Function
> The alternative DynamicProperties task method would use the following
> queries to assign the properties.
> SELECT PropertyValue
> FROM DTSPackageProperties
> WHERE
> PackageName = 'MyPackage' AND
> ObjectName = 'MySource' AND
> PropertyName = 'DataSource'
> SELECT PropertyValue
> FROM DTSPackageProperties
> WHERE
> PackageName = 'MyPackage' AND
> ObjectName = 'MyDestination' AND
> PropertyName = 'DataSource'
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:OEs7OZ4SEHA.3852@.TK2MSFTNGP10.phx.gbl...
> Basically, what I
> rows. Then, for
> a "Destination".
> DTS
> specify
> that I
> row,
> would be
> Task",
> each
>|||John,
if you do want to use the Transform Data Task without inserting rows you can
change the DTSTransformStatus constant from DTSTransformStat_OK to
DTSTransformStat_SkipInsert.
HTH,
Paul Ibison|||<blush> I did not know such a return value existed! Thanks so much, Paul -
- I'm sure
that'll do the trick! (And I think you pegged my issue *exactly*!)
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23MUhxx$SEHA.2128@.TK2MSFTNGP11.phx.gbl...
> John,
> if you do want to use the Transform Data Task without inserting rows you c
an
> change the DTSTransformStatus constant from DTSTransformStat_OK to
> DTSTransformStat_SkipInsert.
> HTH,
> Paul Ibison
>|||No problem. FYI I came across this info from this book which is the most
comprehensive DTS book I know of:
_2_1/202-5145180-8774263" target="_blank">http://www.amazon.co.uk/exec/obidos...5145180-8774263
Regards,
Paul Ibisonsql

How can I grant user run cmdexec

I have a DTS task that needs to run cmdexec, how can I
grant the user have the rights to run this DTS but not
giving him Admin rights?Hi,
First you can give grant execute on xp_cmdshell (gtrant execute on
xp_cmdshell to user) to the user in master database.
If they are not a member of the sysadmin role then they execute it under the
prefix of the SQL Agent Proxy Account.
sysadmin users would execute it as the account under which MSSQL Server
service starts.
How to set the proxy account:
1. Open enterprise manager and select management options
2. Right click abouve the sql Agent and select properties
3. Select the "job system" option
4. Set the "Non sysadmin job step proxy account
5. There you have give the valid OS level user with previlage.
http://support.microsoft.com/defaul...microsoft.com:
80/support/kb/articles/Q264/1/55.ASP&NoWebContent=1
Thanks
Hari
MCDBA
"Cora" <anonymous@.discussions.microsoft.com> wrote in message
news:3a2801c47f53$0d7a2ba0$a301280a@.phx.gbl...
> I have a DTS task that needs to run cmdexec, how can I
> grant the user have the rights to run this DTS but not
> giving him Admin rights?|||I tried, but got below error:
Unable to set the SQL Agent proxy account because of the reason listed
below.
'Error executing extended stored procedure: Specified user can not login'
I tried to add an account that already have SQL Admin. rights and I have
explicitly added the account on master\xp_cmdexec xsp.
Please advice.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:ORKb7Y1fEHA.596@.TK2MSFTNGP11.phx.gbl...
> Hi,
> First you can give grant execute on xp_cmdshell (gtrant execute on
> xp_cmdshell to user) to the user in master database.
> If they are not a member of the sysadmin role then they execute it under
the
> prefix of the SQL Agent Proxy Account.
> sysadmin users would execute it as the account under which MSSQL Server
> service starts.
> How to set the proxy account:
> 1. Open enterprise manager and select management options
> 2. Right click abouve the sql Agent and select properties
> 3. Select the "job system" option
> 4. Set the "Non sysadmin job step proxy account
> 5. There you have give the valid OS level user with previlage.
>
>
http://support.microsoft.com/defaul...microsoft.com:
> 80/support/kb/articles/Q264/1/55.ASP&NoWebContent=1
>
> Thanks
> Hari
> MCDBA
> "Cora" <anonymous@.discussions.microsoft.com> wrote in message
> news:3a2801c47f53$0d7a2ba0$a301280a@.phx.gbl...
>|||This can happen when the startup account for SQL Server
doesn't have the proper permissions. If you change the
accounts through Enterprise Manager, the permissions and
rights are taken care of for you. If not, you need to go
through and verify the correct rights and permission. You
could reset the service accounts through Enterprise Manager
or go through the following article to check the permissions
for the account:
HOW TO: Change the SQL Server or SQL Server Agent Service
Account Without Using SQL Enterprise Manager in SQL Server
2000
http://support.microsoft.com/?id=283811
-Sue
On Thu, 19 Aug 2004 10:49:52 +0800, "Nobody"
<nobody@.nospam.com> wrote:

>I tried, but got below error:
>Unable to set the SQL Agent proxy account because of the reason listed
>below.
>'Error executing extended stored procedure: Specified user can not login'
>I tried to add an account that already have SQL Admin. rights and I have
>explicitly added the account on master\xp_cmdexec xsp.
>Please advice.
>
>
>"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
>news:ORKb7Y1fEHA.596@.TK2MSFTNGP11.phx.gbl...
>the
>[url]http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:[/url
]
>

how can I get the error message?

I want to insert csv file into table.

In Control Flow Tab I insert a Data Flow Task and a Script Task

The Script Task is under the Data Flow Task as the failure handle to generate Log.

but I don't konw how to get the Data Flow Task's error message in Script Task?

thank you for you answer!!

Try moving the script task into an OnError eventhandler. The error message is available in there.

Let me know if this creates a problem.

-Jamie

|||

thank you for the answer.

I moved the script task to event handler tag. but it didn't run though my deta flow task generated a primary key error.

also I would like to know wether it is the err.description from that property I can konw the error message?

How Can I get my sproc to return records with 0 as ClientID

Can anyone help me modify this sproc's Where clause or Joins to let (T) task records with a 0 to be returned?

If I enter a ClientID I want to return only those task records with the ClientID I entered (this works).

If I enter no ClientID I want to return all task records, even those with a 0 in the ClientID field.

ALTER PROCEDURE dbo.CMAdmin

@.SID int

AS
SELECT
A.CompanyName,
C.FirstName,
C.LastName,
C.ClientID,Convert(varchar(10),
T.ActionDate, 10) AS [Action Date],
T.Priority,
T.Status,
T.Subject,
T.Note,
T.CompletionDate, 10) AS Completed,
T.DateEntered AS Entered,
T.EnteredBy AS [Entered By],

CASE
WHEN A.[CompanyName] IS NULL OR A.[CompanyName] = '' THEN
C.[FirstName] +' '+ C.[LastName]
ELSE A.[CompanyName]
END AS DRName

FROM
tblClients C LEFT OUTER JOIN tblClientAddresses A
ON C.ClientID = A.ClientID LEFT OUTER JOIN dbo.tblTasks T
ON C.ClientID = T.ClientID

WHERE
C.ClientID = Isnull(@.SID,C.ClientID)

hi

u can modify the where close like this

ALTER PROCEDURE dbo.CMAdmin

@.SID int

AS
SELECT
A.CompanyName,
C.FirstName,
C.LastName,
C.ClientID,Convert(varchar(10),
T.ActionDate, 10) AS [Action Date],
T.Priority,
T.Status,
T.Subject,
T.Note,
T.CompletionDate, 10) AS Completed,
T.DateEntered AS Entered,
T.EnteredBy AS [Entered By],

CASE
WHEN A.[CompanyName] IS NULL OR A.[CompanyName] = '' THEN
C.[FirstName] +' '+ C.[LastName]
ELSE A.[CompanyName]
END AS DRName

FROM
tblClients C LEFT OUTER JOIN tblClientAddresses A
ON C.ClientID = A.ClientID LEFT OUTER JOIN dbo.tblTasks T
ON C.ClientID = T.ClientID

WHERE
C.ClientID = COALESCE(@.SID,C.ClientID)

this query will return all records when client id is null and when it is not null it only returns rows specified in the where condition

for more details

http://www.sqlteam.com/item.asp?ItemID=2077

regards