Hi,

I am a DBA. We deploy SSIS packages on MSDB when a migration request comes
from the developers. The config file is on a file system. The packages are
run under SQl agent using a proxy account. Everything works well.

However, when I deploy the package I don't see the package version number
changing. I would like to use the version number to verify that the migration
is valid. How do I get/set the version number?

Thanks!
Uma

Hello Uma:
I have a standard methodology that I use for most all my packages. The first
step is to record the start time, package name and version in a log table in
SQL, and retrieve back to the package a Load_ID variable. Then when the
package is finished, the last step is to update that same SQL table (updating
only the one row, based on Load_ID) with Rows processed, stop time, etc.
So my log table has a record of when ANY SSIS package has been run, and one
of the items logged is the Version.

There are three items that make up the "Version" of an SSIS package: Major
Version, Minor Version, and Build. You can see, and change them, in the
Package Properties (scroll all the way to the bottom of the properties list).
Major and Minor versions will NOT change automaticaly. Build will be
incremented every time a package is saved inside Development Studio.

To capture this information, I have a String variable in each package
(called "Version", of course) and it has its "EvaluateAsExpression" property
set to True. Then in the Expressions, I have the following:

(DT_STR, 3, 1252) @[System::VersionMajor] + "." + (DT_STR, 3, 1252)
@[System::VersionMinor] + "." + (DT_STR, 5, 1252) @[System::VersionBuild]

This yields a string in the format of "1.2.34" which gets saved in my log
table.

Is this the kind of thing you are looking for?

HTH
--
Todd C

[If this response was helpful, please indicate by clicking the appropriate
answer at the bottom]


"Uma" wrote:

Hello Uma:
I have a standard methodology that I use for most all my packages. The first
step is to record the start time, package name and version in a log table in
SQL, and retrieve back to the package a Load_ID variable. Then when the
package is finished, the last step is to update that same SQL table (updating
only the one row, based on Load_ID) with Rows processed, stop time, etc.
So my log table has a record of when ANY SSIS package has been run, and one
of the items logged is the Version.

There are three items that make up the "Version" of an SSIS package: Major
Version, Minor Version, and Build. You can see, and change them, in the
Package Properties (scroll all the way to the bottom of the properties list).
Major and Minor versions will NOT change automaticaly. Build will be
incremented every time a package is saved inside Development Studio.

To capture this information, I have a String variable in each package
(called "Version", of course) and it has its "EvaluateAsExpression" property
set to True. Then in the Expressions, I have the following:

(DT_STR, 3, 1252) @[System::VersionMajor] + "." + (DT_STR, 3, 1252)
@[System::VersionMinor] + "." + (DT_STR, 5, 1252) @[System::VersionBuild]

This yields a string in the format of "1.2.34" which gets saved in my log
table.

Is this the kind of thing you are looking for?

HTH
--
Todd C

[If this response was helpful, please indicate by clicking the appropriate
answer at the bottom]


"Uma" wrote:

Hello Uma:
I have a standard methodology that I use for most all my packages. The first
step is to record the start time, package name and version in a log table in
SQL, and retrieve back to the package a Load_ID variable. Then when the
package is finished, the last step is to update that same SQL table (updating
only the one row, based on Load_ID) with Rows processed, stop time, etc.
So my log table has a record of when ANY SSIS package has been run, and one
of the items logged is the Version.

There are three items that make up the "Version" of an SSIS package: Major
Version, Minor Version, and Build. You can see, and change them, in the
Package Properties (scroll all the way to the bottom of the properties list).
Major and Minor versions will NOT change automaticaly. Build will be
incremented every time a package is saved inside Development Studio.

To capture this information, I have a String variable in each package
(called "Version", of course) and it has its "EvaluateAsExpression" property
set to True. Then in the Expressions, I have the following:

(DT_STR, 3, 1252) @[System::VersionMajor] + "." + (DT_STR, 3, 1252)
@[System::VersionMinor] + "." + (DT_STR, 5, 1252) @[System::VersionBuild]

This yields a string in the format of "1.2.34" which gets saved in my log
table.

Is this the kind of thing you are looking for?

HTH
--
Todd C

[If this response was helpful, please indicate by clicking the appropriate
answer at the bottom]


"Uma" wrote:

Hello Uma:
I have a standard methodology that I use for most all my packages. The first
step is to record the start time, package name and version in a log table in
SQL, and retrieve back to the package a Load_ID variable. Then when the
package is finished, the last step is to update that same SQL table (updating
only the one row, based on Load_ID) with Rows processed, stop time, etc.
So my log table has a record of when ANY SSIS package has been run, and one
of the items logged is the Version.

There are three items that make up the "Version" of an SSIS package: Major
Version, Minor Version, and Build. You can see, and change them, in the
Package Properties (scroll all the way to the bottom of the properties list).
Major and Minor versions will NOT change automaticaly. Build will be
incremented every time a package is saved inside Development Studio.

To capture this information, I have a String variable in each package
(called "Version", of course) and it has its "EvaluateAsExpression" property
set to True. Then in the Expressions, I have the following:

(DT_STR, 3, 1252) @[System::VersionMajor] + "." + (DT_STR, 3, 1252)
@[System::VersionMinor] + "." + (DT_STR, 5, 1252) @[System::VersionBuild]

This yields a string in the format of "1.2.34" which gets saved in my log
table.

Is this the kind of thing you are looking for?

HTH
--
Todd C

[If this response was helpful, please indicate by clicking the appropriate
answer at the bottom]


"Uma" wrote:

Hello Uma:
I have a standard methodology that I use for most all my packages. The first
step is to record the start time, package name and version in a log table in
SQL, and retrieve back to the package a Load_ID variable. Then when the
package is finished, the last step is to update that same SQL table (updating
only the one row, based on Load_ID) with Rows processed, stop time, etc.
So my log table has a record of when ANY SSIS package has been run, and one
of the items logged is the Version.

There are three items that make up the "Version" of an SSIS package: Major
Version, Minor Version, and Build. You can see, and change them, in the
Package Properties (scroll all the way to the bottom of the properties list).
Major and Minor versions will NOT change automaticaly. Build will be
incremented every time a package is saved inside Development Studio.

To capture this information, I have a String variable in each package
(called "Version", of course) and it has its "EvaluateAsExpression" property
set to True. Then in the Expressions, I have the following:

(DT_STR, 3, 1252) @[System::VersionMajor] + "." + (DT_STR, 3, 1252)
@[System::VersionMinor] + "." + (DT_STR, 5, 1252) @[System::VersionBuild]

This yields a string in the format of "1.2.34" which gets saved in my log
table.

Is this the kind of thing you are looking for?

HTH
--
Todd C

[If this response was helpful, please indicate by clicking the appropriate
answer at the bottom]


"Uma" wrote:

Hello Uma:
I have a standard methodology that I use for most all my packages. The first
step is to record the start time, package name and version in a log table in
SQL, and retrieve back to the package a Load_ID variable. Then when the
package is finished, the last step is to update that same SQL table (updating
only the one row, based on Load_ID) with Rows processed, stop time, etc.
So my log table has a record of when ANY SSIS package has been run, and one
of the items logged is the Version.

There are three items that make up the "Version" of an SSIS package: Major
Version, Minor Version, and Build. You can see, and change them, in the
Package Properties (scroll all the way to the bottom of the properties list).
Major and Minor versions will NOT change automaticaly. Build will be
incremented every time a package is saved inside Development Studio.

To capture this information, I have a String variable in each package
(called "Version", of course) and it has its "EvaluateAsExpression" property
set to True. Then in the Expressions, I have the following:

(DT_STR, 3, 1252) @[System::VersionMajor] + "." + (DT_STR, 3, 1252)
@[System::VersionMinor] + "." + (DT_STR, 5, 1252) @[System::VersionBuild]

This yields a string in the format of "1.2.34" which gets saved in my log
table.

Is this the kind of thing you are looking for?

HTH
--
Todd C

[If this response was helpful, please indicate by clicking the appropriate
answer at the bottom]


"Uma" wrote:

Hello Uma:
I have a standard methodology that I use for most all my packages. The first
step is to record the start time, package name and version in a log table in
SQL, and retrieve back to the package a Load_ID variable. Then when the
package is finished, the last step is to update that same SQL table (updating
only the one row, based on Load_ID) with Rows processed, stop time, etc.
So my log table has a record of when ANY SSIS package has been run, and one
of the items logged is the Version.

There are three items that make up the "Version" of an SSIS package: Major
Version, Minor Version, and Build. You can see, and change them, in the
Package Properties (scroll all the way to the bottom of the properties list).
Major and Minor versions will NOT change automaticaly. Build will be
incremented every time a package is saved inside Development Studio.

To capture this information, I have a String variable in each package
(called "Version", of course) and it has its "EvaluateAsExpression" property
set to True. Then in the Expressions, I have the following:

(DT_STR, 3, 1252) @[System::VersionMajor] + "." + (DT_STR, 3, 1252)
@[System::VersionMinor] + "." + (DT_STR, 5, 1252) @[System::VersionBuild]

This yields a string in the format of "1.2.34" which gets saved in my log
table.

Is this the kind of thing you are looking for?

HTH
--
Todd C

[If this response was helpful, please indicate by clicking the appropriate
answer at the bottom]


"Uma" wrote:

Hello Uma:
I have a standard methodology that I use for most all my packages. The first
step is to record the start time, package name and version in a log table in
SQL, and retrieve back to the package a Load_ID variable. Then when the
package is finished, the last step is to update that same SQL table (updating
only the one row, based on Load_ID) with Rows processed, stop time, etc.
So my log table has a record of when ANY SSIS package has been run, and one
of the items logged is the Version.

There are three items that make up the "Version" of an SSIS package: Major
Version, Minor Version, and Build. You can see, and change them, in the
Package Properties (scroll all the way to the bottom of the properties list).
Major and Minor versions will NOT change automaticaly. Build will be
incremented every time a package is saved inside Development Studio.

To capture this information, I have a String variable in each package
(called "Version", of course) and it has its "EvaluateAsExpression" property
set to True. Then in the Expressions, I have the following:

(DT_STR, 3, 1252) @[System::VersionMajor] + "." + (DT_STR, 3, 1252)
@[System::VersionMinor] + "." + (DT_STR, 5, 1252) @[System::VersionBuild]

This yields a string in the format of "1.2.34" which gets saved in my log
table.

Is this the kind of thing you are looking for?

HTH
--
Todd C

[If this response was helpful, please indicate by clicking the appropriate
answer at the bottom]


"Uma" wrote:

Versioning is a head ache. Use Team Foundation Server to source safe your
packages.

"Uma" <Uma (AT) discussions (DOT) microsoft.com> wrote