how to initialize package variables?

I have a For Loop container where each iteration a Master record is inserted. I also have a number of package variables capturing rowcounts from the dataflow task. I later update the master record with the rowcounts. How do I initialize the package variables containing the rowcounts because currently, some of the rowcounts are being populated with rowcounts from another file.

thanks

jpsR1 wrote:

I have a For Loop container where each iteration a Master record is inserted. I also have a number of package variables capturing rowcounts from the dataflow task. I later update the master record with the rowcounts. How do I initialize the package variables containing the rowcounts because currently, some of the rowcounts are being populated with rowcounts from another file.

thanks

The best way would be to do it in a script task. There's lots of material around to help you do this. Search for "ReadWriteVariables".

-Jamie

|||

Thanks Jamie. I did find some info but it helped when i searched for "ReadWriteVariables".

this is what I used. I had found a similar sample elsewhere and had tried it with no luck. The difference being, I originally had not put the prefix "User::" in front of the variable.

Phil / Mike, thanks for the help...

Now I'm actually confused (seems to happen alot since I delved into SQL 2005!). I actually placed the "User::" tag in front of my variables and it worked. Oh well, I have enough to play with for now (I especially like the functions for the read / write capabilities). I'm really just trying to start refreshing my scripting skills (or obviously lack thereof)...

You actually can put the "User::" in on the Script Task Editor and it works...

If I define the variables "User::varFTPDestPath" and "User::varFTPFileName" as ReadOnly and "User::varFTPDestPathFileName" as ReadWrite in the Script Task Editor, the following works:

Public Sub Main()

Dim strFTPDestPath, strFTPFileName As String
strFTPDestPath = Dts.Variables("User::varFTPSourcePath").Value.ToString
strFTPFileName = Dts.Variables("User::varFTPFileName").Value.ToString
Dts.Variables("User::varSourcePathFileName").Value = strFTPDestPath + strFTPFileName
MsgBox("varSourcePathFileName = " + Dts.Variables("User::varSourcePathFileName").Value.ToString)
Dts.TaskResult = Dts.Results.Success

End Sub

verbatim:

Dts.Variables("User::rcSourceFile").Value = 0

Dts.Variables("User::rcCleanInserts").Value = 0

thanks again!!!

sql

How to identify User Objects within sysobjects ?

I am querying master..sysobjects table for user defined objects. I have
been relying on category field to show if a given object is a user defined
object or system object. My understanding was that a category value of
zero (0) is a user object. Apparently this is not always true. I have
seen value 16 for some. Where this is documented if any ? BOL does not
talk about the values. Or may be there is a better way to find this out
other than Category field.
I appreciate in advance for any suggestions.
MacYou can use the OBJECTPROPERTY function for this:
SELECT <column list>
FROM sysobjects
WHERE OBJECTPROPERTY(id, 'IsMSShipped') = 0
Jacco Schalkwijk
SQL Server MVP
"Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
news:conj65$1o9g$1@.si05.rsvl.unisys.com...
>I am querying master..sysobjects table for user defined objects. I have
> been relying on category field to show if a given object is a user defined
> object or system object. My understanding was that a category value of
> zero (0) is a user object. Apparently this is not always true. I have
> seen value 16 for some. Where this is documented if any ? BOL does not
> talk about the values. Or may be there is a better way to find this out
> other than Category field.
> I appreciate in advance for any suggestions.
> Mac
>|||"Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
news:conj65$1o9g$1@.si05.rsvl.unisys.com...
>I am querying master..sysobjects table for user defined objects. I have
> been relying on category field to show if a given object is a user defined
> object or system object. My understanding was that a category value of
> zero (0) is a user object. Apparently this is not always true. I have
> seen value 16 for some. Where this is documented if any ? BOL does not
> talk about the values. Or may be there is a better way to find this out
> other than Category field.
> I appreciate in advance for any suggestions.
> Mac
>
Are you looking for specific objects?
SELECT Name from sysobjects where TYPE = 'U' for tables, 'P' for procs
etc.
I'm not sure if that will help you out or not.
Rick Sawtell
MCT, MCSD, MCDBA|||Excellent! Thanks Jacco for the reply. That must be it. I should check to
see if this is supported in earlier releases as well.
Mac
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:%23wkkjWJ2EHA.2676@.TK2MSFTNGP12.phx.gbl...
> You can use the OBJECTPROPERTY function for this:
> SELECT <column list>
> FROM sysobjects
> WHERE OBJECTPROPERTY(id, 'IsMSShipped') = 0
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
> news:conj65$1o9g$1@.si05.rsvl.unisys.com...
defined[vbcol=seagreen]
out[vbcol=seagreen]
>|||Thanks Rick. I was looking for user-stored procedures in the master db. I
can use the TYPE to filter the object types but to know whether it is system
defined or user defined object, I received an answer that ObjectProperty
function can be used to find that out.
"Rick Sawtell" <quickening@.msn.com> wrote in message
news:uCHA2XJ2EHA.2624@.TK2MSFTNGP11.phx.gbl...
> "Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
> news:conj65$1o9g$1@.si05.rsvl.unisys.com...
defined[vbcol=seagreen]
out[vbcol=seagreen]
> Are you looking for specific objects?
> SELECT Name from sysobjects where TYPE = 'U' for tables, 'P' for procs
> etc.
> I'm not sure if that will help you out or not.
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||> I was looking for user-stored procedures in the master db.
Safer to use INFORMATION_SCHEMA in the current DB than to use sysobjects in
the master db (sysobjects is going away eventually, and it is not
recommended to use these tables directly if you can avoid it).
SELECT ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE='PROCEDURE'
AND OBJECTPROPERTY(OBJECT_ID(ROUTINE_SCHEMA+
'.'+ROUTINE_NAME),
'IsMSShipped') = 0
http://www.aspfaq.com/
(Reverse address to reply.)
I
> can use the TYPE to filter the object types but to know whether it is
system
> defined or user defined object, I received an answer that ObjectProperty
> function can be used to find that out.
> "Rick Sawtell" <quickening@.msn.com> wrote in message
> news:uCHA2XJ2EHA.2624@.TK2MSFTNGP11.phx.gbl...
have[vbcol=seagreen]
> defined
of[vbcol=seagreen]
have[vbcol=seagreen]
not[vbcol=seagreen]
> out
>|||Thanks. I should look into that.
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uVER44J2EHA.2316@.TK2MSFTNGP15.phx.gbl...
> Safer to use INFORMATION_SCHEMA in the current DB than to use sysobjects
in
> the master db (sysobjects is going away eventually, and it is not
> recommended to use these tables directly if you can avoid it).
> SELECT ROUTINE_NAME
> FROM INFORMATION_SCHEMA.ROUTINES
> WHERE ROUTINE_TYPE='PROCEDURE'
> AND OBJECTPROPERTY(OBJECT_ID(ROUTINE_SCHEMA+
'.'+ROUTINE_NAME),
> 'IsMSShipped') = 0
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
> I
> system
ObjectProperty[vbcol=seagreen]
> have
> of
> have
> not
this[vbcol=seagreen]
procs[vbcol=seagreen]
>

How to identify User Objects within sysobjects ?

I am querying master..sysobjects table for user defined objects. I have
been relying on category field to show if a given object is a user defined
object or system object. My understanding was that a category value of
zero (0) is a user object. Apparently this is not always true. I have
seen value 16 for some. Where this is documented if any ? BOL does not
talk about the values. Or may be there is a better way to find this out
other than Category field.
I appreciate in advance for any suggestions.
MacYou can use the OBJECTPROPERTY function for this:
SELECT <column list>
FROM sysobjects
WHERE OBJECTPROPERTY(id, 'IsMSShipped') = 0
--
Jacco Schalkwijk
SQL Server MVP
"Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
news:conj65$1o9g$1@.si05.rsvl.unisys.com...
>I am querying master..sysobjects table for user defined objects. I have
> been relying on category field to show if a given object is a user defined
> object or system object. My understanding was that a category value of
> zero (0) is a user object. Apparently this is not always true. I have
> seen value 16 for some. Where this is documented if any ? BOL does not
> talk about the values. Or may be there is a better way to find this out
> other than Category field.
> I appreciate in advance for any suggestions.
> Mac
>|||"Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
news:conj65$1o9g$1@.si05.rsvl.unisys.com...
>I am querying master..sysobjects table for user defined objects. I have
> been relying on category field to show if a given object is a user defined
> object or system object. My understanding was that a category value of
> zero (0) is a user object. Apparently this is not always true. I have
> seen value 16 for some. Where this is documented if any ? BOL does not
> talk about the values. Or may be there is a better way to find this out
> other than Category field.
> I appreciate in advance for any suggestions.
> Mac
>
Are you looking for specific objects?
SELECT Name from sysobjects where TYPE = 'U' for tables, 'P' for procs
etc.
I'm not sure if that will help you out or not.
Rick Sawtell
MCT, MCSD, MCDBA|||Excellent! Thanks Jacco for the reply. That must be it. I should check to
see if this is supported in earlier releases as well.
Mac
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:%23wkkjWJ2EHA.2676@.TK2MSFTNGP12.phx.gbl...
> You can use the OBJECTPROPERTY function for this:
> SELECT <column list>
> FROM sysobjects
> WHERE OBJECTPROPERTY(id, 'IsMSShipped') = 0
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
> news:conj65$1o9g$1@.si05.rsvl.unisys.com...
> >I am querying master..sysobjects table for user defined objects. I have
> > been relying on category field to show if a given object is a user
defined
> > object or system object. My understanding was that a category value of
> > zero (0) is a user object. Apparently this is not always true. I have
> > seen value 16 for some. Where this is documented if any ? BOL does not
> > talk about the values. Or may be there is a better way to find this
out
> > other than Category field.
> >
> > I appreciate in advance for any suggestions.
> > Mac
> >
> >
>|||Thanks Rick. I was looking for user-stored procedures in the master db. I
can use the TYPE to filter the object types but to know whether it is system
defined or user defined object, I received an answer that ObjectProperty
function can be used to find that out.
"Rick Sawtell" <quickening@.msn.com> wrote in message
news:uCHA2XJ2EHA.2624@.TK2MSFTNGP11.phx.gbl...
> "Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
> news:conj65$1o9g$1@.si05.rsvl.unisys.com...
> >I am querying master..sysobjects table for user defined objects. I have
> > been relying on category field to show if a given object is a user
defined
> > object or system object. My understanding was that a category value of
> > zero (0) is a user object. Apparently this is not always true. I have
> > seen value 16 for some. Where this is documented if any ? BOL does not
> > talk about the values. Or may be there is a better way to find this
out
> > other than Category field.
> >
> > I appreciate in advance for any suggestions.
> > Mac
> >
> >
> Are you looking for specific objects?
> SELECT Name from sysobjects where TYPE = 'U' for tables, 'P' for procs
> etc.
> I'm not sure if that will help you out or not.
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||> I was looking for user-stored procedures in the master db.
Safer to use INFORMATION_SCHEMA in the current DB than to use sysobjects in
the master db (sysobjects is going away eventually, and it is not
recommended to use these tables directly if you can avoid it).
SELECT ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE='PROCEDURE'
AND OBJECTPROPERTY(OBJECT_ID(ROUTINE_SCHEMA+'.'+ROUTINE_NAME),
'IsMSShipped') = 0
--
http://www.aspfaq.com/
(Reverse address to reply.)
I
> can use the TYPE to filter the object types but to know whether it is
system
> defined or user defined object, I received an answer that ObjectProperty
> function can be used to find that out.
> "Rick Sawtell" <quickening@.msn.com> wrote in message
> news:uCHA2XJ2EHA.2624@.TK2MSFTNGP11.phx.gbl...
> >
> > "Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
> > news:conj65$1o9g$1@.si05.rsvl.unisys.com...
> > >I am querying master..sysobjects table for user defined objects. I
have
> > > been relying on category field to show if a given object is a user
> defined
> > > object or system object. My understanding was that a category value
of
> > > zero (0) is a user object. Apparently this is not always true. I
have
> > > seen value 16 for some. Where this is documented if any ? BOL does
not
> > > talk about the values. Or may be there is a better way to find this
> out
> > > other than Category field.
> > >
> > > I appreciate in advance for any suggestions.
> > > Mac
> > >
> > >
> >
> > Are you looking for specific objects?
> >
> > SELECT Name from sysobjects where TYPE = 'U' for tables, 'P' for procs
> > etc.
> >
> > I'm not sure if that will help you out or not.
> >
> > Rick Sawtell
> > MCT, MCSD, MCDBA
> >
> >
> >
>|||Thanks. I should look into that.
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uVER44J2EHA.2316@.TK2MSFTNGP15.phx.gbl...
> > I was looking for user-stored procedures in the master db.
> Safer to use INFORMATION_SCHEMA in the current DB than to use sysobjects
in
> the master db (sysobjects is going away eventually, and it is not
> recommended to use these tables directly if you can avoid it).
> SELECT ROUTINE_NAME
> FROM INFORMATION_SCHEMA.ROUTINES
> WHERE ROUTINE_TYPE='PROCEDURE'
> AND OBJECTPROPERTY(OBJECT_ID(ROUTINE_SCHEMA+'.'+ROUTINE_NAME),
> 'IsMSShipped') = 0
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
> I
> > can use the TYPE to filter the object types but to know whether it is
> system
> > defined or user defined object, I received an answer that
ObjectProperty
> > function can be used to find that out.
> >
> > "Rick Sawtell" <quickening@.msn.com> wrote in message
> > news:uCHA2XJ2EHA.2624@.TK2MSFTNGP11.phx.gbl...
> > >
> > > "Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
> > > news:conj65$1o9g$1@.si05.rsvl.unisys.com...
> > > >I am querying master..sysobjects table for user defined objects. I
> have
> > > > been relying on category field to show if a given object is a user
> > defined
> > > > object or system object. My understanding was that a category value
> of
> > > > zero (0) is a user object. Apparently this is not always true. I
> have
> > > > seen value 16 for some. Where this is documented if any ? BOL does
> not
> > > > talk about the values. Or may be there is a better way to find
this
> > out
> > > > other than Category field.
> > > >
> > > > I appreciate in advance for any suggestions.
> > > > Mac
> > > >
> > > >
> > >
> > > Are you looking for specific objects?
> > >
> > > SELECT Name from sysobjects where TYPE = 'U' for tables, 'P' for
procs
> > > etc.
> > >
> > > I'm not sure if that will help you out or not.
> > >
> > > Rick Sawtell
> > > MCT, MCSD, MCDBA
> > >
> > >
> > >
> >
> >
>

How to identify User Objects within sysobjects ?

I am querying master..sysobjects table for user defined objects. I have
been relying on category field to show if a given object is a user defined
object or system object. My understanding was that a category value of
zero (0) is a user object. Apparently this is not always true. I have
seen value 16 for some. Where this is documented if any ? BOL does not
talk about the values. Or may be there is a better way to find this out
other than Category field.
I appreciate in advance for any suggestions.
Mac
You can use the OBJECTPROPERTY function for this:
SELECT <column list>
FROM sysobjects
WHERE OBJECTPROPERTY(id, 'IsMSShipped') = 0
Jacco Schalkwijk
SQL Server MVP
"Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
news:conj65$1o9g$1@.si05.rsvl.unisys.com...
>I am querying master..sysobjects table for user defined objects. I have
> been relying on category field to show if a given object is a user defined
> object or system object. My understanding was that a category value of
> zero (0) is a user object. Apparently this is not always true. I have
> seen value 16 for some. Where this is documented if any ? BOL does not
> talk about the values. Or may be there is a better way to find this out
> other than Category field.
> I appreciate in advance for any suggestions.
> Mac
>
|||"Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
news:conj65$1o9g$1@.si05.rsvl.unisys.com...
>I am querying master..sysobjects table for user defined objects. I have
> been relying on category field to show if a given object is a user defined
> object or system object. My understanding was that a category value of
> zero (0) is a user object. Apparently this is not always true. I have
> seen value 16 for some. Where this is documented if any ? BOL does not
> talk about the values. Or may be there is a better way to find this out
> other than Category field.
> I appreciate in advance for any suggestions.
> Mac
>
Are you looking for specific objects?
SELECT Name from sysobjects where TYPE = 'U' for tables, 'P' for procs
etc.
I'm not sure if that will help you out or not.
Rick Sawtell
MCT, MCSD, MCDBA
|||Excellent! Thanks Jacco for the reply. That must be it. I should check to
see if this is supported in earlier releases as well.
Mac
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid > wrote
in message news:%23wkkjWJ2EHA.2676@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> You can use the OBJECTPROPERTY function for this:
> SELECT <column list>
> FROM sysobjects
> WHERE OBJECTPROPERTY(id, 'IsMSShipped') = 0
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
> news:conj65$1o9g$1@.si05.rsvl.unisys.com...
defined[vbcol=seagreen]
out
>
|||Thanks Rick. I was looking for user-stored procedures in the master db. I
can use the TYPE to filter the object types but to know whether it is system
defined or user defined object, I received an answer that ObjectProperty
function can be used to find that out.
"Rick Sawtell" <quickening@.msn.com> wrote in message
news:uCHA2XJ2EHA.2624@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> "Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
> news:conj65$1o9g$1@.si05.rsvl.unisys.com...
defined[vbcol=seagreen]
out
> Are you looking for specific objects?
> SELECT Name from sysobjects where TYPE = 'U' for tables, 'P' for procs
> etc.
> I'm not sure if that will help you out or not.
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>
|||> I was looking for user-stored procedures in the master db.
Safer to use INFORMATION_SCHEMA in the current DB than to use sysobjects in
the master db (sysobjects is going away eventually, and it is not
recommended to use these tables directly if you can avoid it).
SELECT ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE='PROCEDURE'
AND OBJECTPROPERTY(OBJECT_ID(ROUTINE_SCHEMA+'.'+ROUTIN E_NAME),
'IsMSShipped') = 0
http://www.aspfaq.com/
(Reverse address to reply.)
I
> can use the TYPE to filter the object types but to know whether it is
system[vbcol=seagreen]
> defined or user defined object, I received an answer that ObjectProperty
> function can be used to find that out.
> "Rick Sawtell" <quickening@.msn.com> wrote in message
> news:uCHA2XJ2EHA.2624@.TK2MSFTNGP11.phx.gbl...
have[vbcol=seagreen]
> defined
of[vbcol=seagreen]
have[vbcol=seagreen]
not
> out
>
|||Thanks. I should look into that.
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uVER44J2EHA.2316@.TK2MSFTNGP15.phx.gbl...
> Safer to use INFORMATION_SCHEMA in the current DB than to use sysobjects
in[vbcol=seagreen]
> the master db (sysobjects is going away eventually, and it is not
> recommended to use these tables directly if you can avoid it).
> SELECT ROUTINE_NAME
> FROM INFORMATION_SCHEMA.ROUTINES
> WHERE ROUTINE_TYPE='PROCEDURE'
> AND OBJECTPROPERTY(OBJECT_ID(ROUTINE_SCHEMA+'.'+ROUTIN E_NAME),
> 'IsMSShipped') = 0
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
> I
> system
ObjectProperty[vbcol=seagreen]
> have
> of
> have
> not
this[vbcol=seagreen]
procs
>

How to identify a dialog as encrypted?

I am trying to lookup a dialog from conversation_endpoints, however if a dialog was created with the encryption setting to ON and thereis no master Key in the database then the record put in the conversation_endpoints is the same as one without encryption.

How can I distinguish between the one requested with no ecryption and requested with encryption but setup with none due to the lack of a key?

There is no way to find out looking only at the sys.conversation_endpoints table. But if you request encryption and there is no master key, the messages will stay pending with status set to 'The session keys for this conversation could not be created or accessed. The database master key is required for this operation.' in the sys.transmission_queue.

|||Thats what I guessed, shame really