padding variables

I know this may be an elementary question but can anyone tell me how to pad a
variable.
For example
@.chvcode has value ��123�� and I want to pad it with ��00123��.
Logger
Wil your variable always be an int, if so you can do
SET @.chvcode = RIGHT(100000 + @.chvcode,5)
Andy
"Logger" wrote:

> I know this may be an elementary question but can anyone tell me how to pad a
> variable.
> For example
> @.chvcode has value ��123�� and I want to pad it with ��00123��.
>
sql

padding variables

I know this may be an elementary question but can anyone tell me how to pad
a
variable.
For example
@.chvcode has value ��123�� and I want to pad it with ��00123��.Logger
Wil your variable always be an int, if so you can do
SET @.chvcode = RIGHT(100000 + @.chvcode,5)
Andy
"Logger" wrote:

> I know this may be an elementary question but can anyone tell me how to pa
d a
> variable.
> For example
> @.chvcode has value ��123�� and I want to pad it with ��00123��.
>

padding variables

I know this may be an elementary question but can anyone tell me how to pad a
variable.
For example
@.chvcode has value â'123â' and I want to pad it with â'00123â'.Logger
Wil your variable always be an int, if so you can do
SET @.chvcode = RIGHT(100000 + @.chvcode,5)
Andy
"Logger" wrote:
> I know this may be an elementary question but can anyone tell me how to pad a
> variable.
> For example
> @.chvcode has value â'123â' and I want to pad it with â'00123â'.
>

package variables passed to Execute SQL Task

Hi,

I am having trouble getting the Execute SQL Task to recognize the package-level variables in an SSIS package. The tasks fail execution. The package contains three Execute SQL tasks and none of them receive the variables. However, when I replace the '?' in the code with the variable values they execute fine. These are the steps I have taken thus far:

Made sure the three variables are package level.

Verified I'm using an OLE DB valid connection.

Verified the variables are properly mapped in the parameters mapping section of the task properties.

Tried all 4 types of variable to parameter mapping (even though '?' in the query and '0,1,2..' in the name should work).

Changed the Bypassprepare property to 'True'.

Breakpoints and watch windows to verify the variable values.

I actually tried the simple tutorials on a one-liner SQL statement that uses variables, but had the same problem. I know there must be something simple that I'm missing here, but any info would be greatly appreciated!

Thanks!

Also, the code for the first package that holds the parameter is:

DECLARE @.DatabaseName varchar(100)
SET @.DatabaseName = ?
DECLARE @.SPID smallint

And, of course, when I replace the ? with the DB name in the Value field of the variable it executes.

The Parameter Mapping has the following values:

User:atabasename Input Varchar 0

And the Variables set up has the following values:

databasename package string DB_NAME

Is there a way to see the query after it reads the variables to see if I have something syntactically incorrect in the values? Please let me know if any further info would help.. I'm really stumped on this since I get the same results with examples found everywhere else too.

Thanks!|||

You could use an alternative approach to built the SQL statement. Create a new variable, let's say SQLStatement; then use an expression (set EvaluateAsExpresion=True) in that variable to form the SQL statement. something like:

"Select * from " + @.[User::Variable1]

Then in the execute SQL task set SQLSourceType=Variable and select the variable name in SourceVariable drop-down list.

I like this approach because you an see easily how the sql statement is being built.

|||Thanks for the quick reply Rafael. I am unclear as to how this would resolve the issue. The package contains three lengthy Execute SQL tasks, and the package needs to be replicated for several different databases that are independently accessed in a development environment. My original idea was to use the package variable to simply change the DB name. Are you saying I should try changing each task to a SQLSourceType=Variable and use a SQLStatement-like variable that uses an expression for the code presently used in the Execute SQL task? If so, it's definitely worth a try, but I'm still unclear as to how I'm misusing the package variables and I'm sure I'll find future situations where they will be useful. Any ideas?

Thanks again!
|||

jheywood wrote:

Are you saying I should try changing each task to a SQLSourceType=Variable and use a SQLStatement-like variable that uses an expression for the code presently used in the Execute SQL task?
Thanks again!

Yes, that is my idea. That give you the benefit of testing the expression and check how exactly the sql statement is gong to be sent.

jheywood wrote:

but I'm still unclear as to how I'm misusing the package variables and I'm sure I'll find future situations where they will be useful. Any ideas?

Not sure..did you try changing the value of ByPassPrepare in the execute sql task?

|||Yes, I tried that too. I was hoping it was maybe just some stupid syntax problem that I might have overlooked with the parameter or the variable, but I guess not.

I'll try the other method since that definitely seems like a viable option. Thanks so much for the help!

Please let me know if you think of anything else about the var use, or need more information. I'm really stumped on it.
sql

Package Variables Not Available at Runtime

Hello,
I have three package variables that I need to have access to at runtime. All three variables have package scope. The first two, 'StartDate' and 'EndDate' are DateTime variables. The third is called FilePath and is a String variable.

I have taken the following steps;

1. Enabled package configurations
2. Set up all three variables as Parent Package Variables, and have targeted the 'Value' property for each.
3. In the properties of the solution, I have set AllowConfigurationChanges to True.
4. After the package was built, I ran the Package Installation Wizard from the Manifest.

I have done both File System and SQL Server installations. When I go to run the package, none of the three package variables are available for modification.

What am I doing wrong?

Thank you for your help!

cdun2

Hi,

You are not doing any thing wrong.

The value you have to set into the configuration file (xxx.dtsconfig file), that has to be read by package, before executing the package.

The configuration file is like name value pair...Name will be created by SSIS, you have to fill the value

You can't modify the variable at runtime, it is updated from the configuration file at runtime.

Thanks

Dharmbir

|||Thank you for your response. Dumb question; Where do I find this file?|||cdun2,
That file is not applicable in your situation.

Phil|||Thanks. What configuration type do I need to set up for these package variables so that I can configure the package variables at run time? Should I be using an XML Configuration file?|||

cdun2 wrote:

Thanks. What configuration type do I need to set up for these package variables so that I can configure the package variables at run time? Should I be using an XML Configuration file?

Well, you can use XML config files (or SQL Server configurations) or you can use the /SET option on the command line.|||Thanks for your help!|||

I think in your case XML config file will be most usefull as it is easily readable.

when you create the config file, path will be mentioned in the wizard.

|||

Dharmbir wrote:

I think in your case XML config file will be most usefull as it is easily readable.

Why?|||

Dharmbir wrote:

I think in your case XML config file will be most usefull as it is easily readable.

when you create the config file, path will be mentioned in the wizard.

I've tried both SQL Server and XML configurations, and I must be missing something. I expected that with one or the other configurations that there would be something in the Execute Package Utility where I would see the package variables that I wanted to update, and then I could basically 'fill in the blanks'.

Now I understand that for the XML configuration files, the file has to be opened and modified directly. In the table I set up for the SQL Server configurations, do I just execute Update queries to modify the values? In the Execute Package Utility 'Set Values' property, I see where I can add the Property Path and its value. It looks like I need to type in the Property Path every time I run the package. Why would I have to do that If I have specifically set up specific package variables in the package configurations?

Thanks again.

|||If package configurations are turned on, then whatever you define in that configuration will get picked up at run time. So, for example, it is common to place the connectionstring parameter of your database connections into the configuration. Updating this parameter (either by editing the XML file, or by updating the configuration table in SQL Server) will cause that associated connection to use the new, updated connectionstring at runtime. You need not pass anything in if the configuration is correct and that you have "configured" the appropriate pieces of information (connectionstring, variable, etc...)

Does that make sense?|||Yes, it makes sense. I just expected some way to interface with the variables other than a direct edit to an XML file or an UPDATE statement against a table. I don't have anything against doing either, I just expected to see the variables expressed in the Execute Package Utility. The interface for the Connection Managers is similar to what I expected for the package variables that I set up in package configurations.|||

cdun2 wrote:

Yes, it makes sense. I just expected some way to interface with the variables other than a direct edit to an XML file or an UPDATE statement against a table. I don't have anything against doing either, I just expected to see the variables expressed in the Execute Package Utility. The interface for the Connection Managers is similar to what I expected for the package variables that I set up in package configurations.

Nope. You can do it in the Execute Package Utility though. Use the /SET command line option: http://msdn2.microsoft.com/en-us/library/ms162810.aspx|||

Phil Brammer wrote:

Nope. You can do it in the Execute Package Utility though. Use the /SET command line option: http://msdn2.microsoft.com/en-us/library/ms162810.aspx

Did you mean the dtexec utility? At least I know how I'm supposed to alter the variable values once they are configured in the package. I'll probably just write a proc to update the SQL SSIS Configurations table that I have.

Thanks again!

cdun2

Package variables in case of an Exception

Hi there,

I may be blind, but I'm sure that I have seen a post about package variables 'ExceptionMessage'
and 'ExceptionCode' or somehow which are only available when an Exception occurs.
I cannot find it anywhere :(

If there isn't any then the question is: Can I read out the message and the code of an occurring Exception e.g. from a Script Component/ Custom Task which is controlled by an error constraint?

Can someone help?
Fridtjof

Fridtjof,

The best way to do this is place a TRY...CATCH block in your script component. In the catch block you can raise an OnError event containing the error message.

Look here for some useful stuff:

http://blogs.conchango.com/jamiethomson/archive/2005/08/08/1969.aspx

http://blogs.conchango.com/jamiethomson/archive/2005/10/10/2253.aspx

http://blogs.conchango.com/jamiethomson/archive/2005/11/03/2356.aspx

http://sqljunkies.com/WebLog/simons/archive/2005/12/15/17626.aspx

-Jamie

|||Jamie,

great contribution!

But you focussed on script component. What if I want to use a custom control flow task to handle the exception. There I don't have any input columns esp. error inputs.

My idea was that in case of an exception you could read the code and the message from the
package variable for any further handling, e.g. do some custom logging.

Fridtjof
|||

The same general approach (i.e. throw OnError events) should work - the syntax is just a little different in a script task that's all.

-Jamie

|||Jamie,

sorry, but this is not what I meant.

E.g. I have an Exec-SQL task A and a Custom Control Flow task named B.
A and B are connected via a red precedence constraint. A runs first and B second in case of
an Exception.

In B I want to read out the Exception Message (for custom logging).

This works for Data Flow Tasks like you said. But not for Control Flow as far as I see.

Sorry that I wasn't that precise but I was looking for both Control Flow and Data Flow...

Thanks
Fridtjof
|||

Every tasks that errors throws an error event.

The error will be available in the OnError eventhandler. You can do what you like with it in there.

-Jamie

|||I guess the OnError event is called only if the task itself raises an error.

The point was that, as described above, task A raises an exception and task B is then called via an error constraint. I want to evaluate e.g. an SQLException from an ExecSql Task in a subsequent custom task since I cannot do this in the ExecSQL Task itself.

Fridtjof
|||

Friedel wrote:

I guess the OnError event is called only if the task itself raises an error.
The point was that, as described above, task A raises an exception and task B is then called via an error constraint. I want to evaluate e.g. an SQLException from an ExecSql Task in a subsequent custom task since I cannot do this in the ExecSQL Task itself.
Fridtjof

I don't understand why putting the custom task ito the OnError eventhandler is not sufficient. I guess I just don't understand your scenario well enough!

-Jamie

|||OMG, I've got it!!!

You meant the Eventhandler of the package which is available at the Workbench!!!

I tried to implement an EventHandler in C# in the Custom task. How stupid :(

But now everything is great again.

Thanks for having this long thread talking at cross-purposes. Sorry for that.
Fridtjof

Package variables "disappear" from job properties

Creating a SQL Agent job to run a package works just fine. On the Step Properties, Set values tab, I can add variables and the values I want for them, no problem. The job runs just fine. If I want to go edit the values however, they're always gone.

Is there some security setting that hides the variables and their values which is on by default?

GregsListAcct wrote:

Creating a SQL Agent job to run a package works just fine. On the Step Properties, Set values tab, I can add variables and the values I want for them, no problem. The job runs just fine. If I want to go edit the values however, they're always gone.

Is there some security setting that hides the variables and their values which is on by default?

Greg,

I don't know the answer to this but regardless, you should stop using the SSIS sub-system and just call teh package usinng the command-line sub-system instead.

There is an article at wiki.sqlis.com that explains the rationale behind this but the site is down so i can't link to it. Basically you don't get any diagnostic info out of the SSIS sub-system.

-Jamie

Package variables

Couldn't quite find the answer I was looking for via a forum search.

I have 9 packages that are currently called by a master package - all child packages take a datetime variable from the master package which is subsequently used in the child processes.

The question I have is that if I run the master package manually from Visual Studio I can set the master package variable manually to a date of my choosing and run it, which works fine. However we will be wishing to automate the package execution at some point and want to know the best way to run the package(s) on an automated basis and have the data variable supplied to the master package.

What would be the best way to do this ?

Presently we have a variable called MasterPackage_vLoaddate which is a DateTime data type.

Any help appreciated.

You can use the /SET option of dtexec.exe to set your variable at run time.

Or, you can use expressions on that variable if you want to use something like getdate().|||

Thanks Phil..

OK.. you'll need to tell me take me thru this in a little more basic detail

Let's say I wanted to run this job at 07:00 every morning. The variable (date) should always be for the prior day.

So I am guessing i'd need to use somthing like

dateadd(day,-1,getdate())

Can I just add that to the package in VS ? Or where exactly would I use this in order to set the variable correctly. Sorry for being such a noob!!

|||Yes, you have the correct formula.

So, in the properties of the variable you wish to work with, set "EvaluateAsExpression" equal to true. Then, in the "expression" parameter, add your formula. It's that simple.

Every time the package runs, this formula will be evaluated.|||

Will,

Keep in mind that if you want the ability of running the package for any date you choose, you would need to put extra logic on this (e.g. you need to reprocess a day that is not the current day; or if for some reason the package did not run in a specifc day).

You could easily implemnet that logic with a table that keeps track of the executions dates.

Just my two cents

|||

OK...

I'll test that in the morning. I used

set "EvaluateAsExpression" equal to true

and then used the formula getdate("d",-1,getdate())

I am supposing that it will just be a straightforward job of scheduling the master package run in SQL Agent and then the other packages will be called as per expectations ?

Cheers... your reply helped

|||

Will Riley wrote:

I am supposing that it will just be a straightforward job of scheduling the master package run in SQL Agent and then the other packages will be called as per expectations ?

Yep, that should be pretty straightforward.|||

Hi Rafael,

I do already have a table that logs package execution so that in theory, the same day cannot be processed twice. Would you suggest that I derive the date variable from that table (i.e. max date + 1 i.e. something like a SQL command.... SELECT dateadd("d",1,MAX(load_dates.date)) )

If so , how would I pass that to the package ? it might be something I hold in reserve in case the above proves unworkable from Phil.

Cheers

|||

Will Riley wrote:

Hi Rafael,

I do already have a table that logs package execution so that in theory, the same day cannot be processed twice. Would you suggest that I derive the date variable from that table (i.e. max date + 1 i.e. something like a SQL command.... SELECT dateadd("d",1,MAX(load_dates.date)) )

If so , how would I pass that to the package ? it might be something I hold in reserve in case the above proves unworkable from Phil.

Cheers

My solution will work, it just won't be as flexible as Rafael's.

For the table derived option, use an Execute SQL task in the control flow to select the max(date)+1 and then store the result into your package's date variable.|||

Just one more thing....

Am I likely to run in to an issue with times?

The above solution will equate to a datetime value of something like

11/02/2007 07:00

I would rather have

11/02/2007 00:00

As there may well be issues if the time is anything other than 00:00

Is there a simple bit of SSIS "expression" that will evaluate to "yesterday" at 00:00 ?

|||

Will Riley wrote:

Just one more thing....

Am I likely to run in to an issue with times?

The above solution will equate to a datetime value of something like

11/02/2007 07:00

I would rather have

11/02/2007 00:00

As there may well be issues if the time is anything other than 00:00

Is there a simple bit of SSIS "expression" that will evaluate to "yesterday" at 00:00 ?

Put this in front of your formula: (DT_DBTIMESTAMP)(DT_DBDATE)

So you would have:
(DT_DBTIMESTAMP)(DT_DBDATE)dateadd("d",-1,getdate())

If you don't even want the 00:00 on the end, don't include the (DT_DBTIMESTAMP) cast.|||

Phil Brammer wrote:

My solution will work, it just won't be as flexible as Rafael's.

I never meant it won't work. I just wanted to add a point I thought was important to get that issue solved.

|||

Will Riley wrote:

Hi Rafael,

I do already have a table that logs package execution so that in theory, the same day cannot be processed twice. Would you suggest that I derive the date variable from that table (i.e. max date + 1 i.e. something like a SQL command.... SELECT dateadd("d",1,MAX(load_dates.date)) )

If so , how would I pass that to the package ? it might be something I hold in reserve in case the above proves unworkable from Phil.

I think the approach beeing discussed would work fine; you just need to base the logic on that table you already have (good you already have it!).

Having a logic that depends in 'today' date is asking for trouble in long long term; when things do not happen/run as expected.

|||

Rafael Salas wrote:

I never meant it won't work. I just wanted to add a point I thought was important to get that issue solved.

I agree. Will mentioned it.|||

Phil, Rafael,

Thanks both - between you you provided both the answer(s) I needed and some good advice in terms of the use/dangers of dynamic variables.

Cheers

Package MajorVersion and MinorVersion, when do they change?

HI, I am wondering when those system variables change. I just cannot see when these are updated. Also, VersionComment, whta is the purpose of this?

Thank you,

Ccote

You change them. For your own version control system.|||

Thanks, is there a way to change those properties via VSS? Or maybe I do not unerstand.

Ccote

|||

No, you would update the values on the properties through BIDS. It is so you can set your own version numbers.

|||

OK I saw it, thanks! What would be the difference (or usage of) between Major and Minor version? And also, is there a way to set these properties via a script task? Open all packages and set properties?

Thanks again!

Ccote

|||

ccote wrote:

OK I saw it, thanks! What would be the difference (or usage of) between Major and Minor version? And also, is there a way to set these properties via a script task? Open all packages and set properties?

Thanks again!

Ccote

"Major" version might be a complete overhaul of logic. Perhaps you've restructured the way totals are calculated and are now performing via new business rules.

"Minor" version might be a minor change -- a where clause addition, fix a typo, etc...

In the end, it's up to you.|||

ccote wrote:

OK I saw it, thanks! What would be the difference (or usage of) between Major and Minor version? And also, is there a way to set these properties via a script task? Open all packages and set properties?

Thanks again!

Ccote

You can use the SSIS API to load packages and set the properties, then save the packages again, if you want to automate the process.

|||

Ok thanks guys!

Package is getting Parent variables too late?...

Hi,

I am sending variables from package to package by configuration parent packages in each package...
I usually set logging to text files in a certain path that should be received from the parent variable... the strange thing is that if i let the variable in the child package with an incorrect path, my package just through an error even if i am passing the variable from the parent with a correct value...

So... my question is... does my package starts execution without any synch over the parent packages? When are they set? How can i be sure to start my logging with the right parent variable settings?

You can try for example to create a package... add a new variable like "A" and "B" set its value to a default string like "I DONT WANT THIS", add an Execute SQL Task that will record the values of "A" variable to a temporary table...

Then use dtexecui.exe to execute the package and set variable "A" to "THIS IS WHAT I NEED" you will see that in the table will appear one record with the "I DONT WANT THIS" value...

I can't understand this...

Also... if i have 100 variables configured to get values from the parent... is the execution synch or asynch?

Best Regards,

Parent variable configuration is applied to child package at runtime after all other package configuration types are applied and loggers initialized. This is a design decision we have made. To workaround this, you have to use other form of package configuration like XML, SQL Server, etc.|||

So, tell me... even if thats the case... why my parent variables are not set when a certain Execute SQL Task gets actually executed, inserting incorrect values into the table? This is not a very good procedure... How can we make sure to be using the right values in the child package, based on the parent values?

Regards,

|||This should have work. Did you see any error/warning messages about package failed to apply any parent variable configuration?|||

Nop, no errors and no warnings... This is defenitly odd..

Regards

|||

I have had problems with this as well...

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2045623&SiteID=1

Package is getting Parent variables too late?...

Hi,

I am sending variables from package to package by configuration parent packages in each package...
I usually set logging to text files in a certain path that should be received from the parent variable... the strange thing is that if i let the variable in the child package with an incorrect path, my package just through an error even if i am passing the variable from the parent with a correct value...

So... my question is... does my package starts execution without any synch over the parent packages? When are they set? How can i be sure to start my logging with the right parent variable settings?

You can try for example to create a package... add a new variable like "A" and "B" set its value to a default string like "I DONT WANT THIS", add an Execute SQL Task that will record the values of "A" variable to a temporary table...

Then use dtexecui.exe to execute the package and set variable "A" to "THIS IS WHAT I NEED" you will see that in the table will appear one record with the "I DONT WANT THIS" value...

I can't understand this...

Also... if i have 100 variables configured to get values from the parent... is the execution synch or asynch?

Best Regards,

Parent variable configuration is applied to child package at runtime after all other package configuration types are applied and loggers initialized. This is a design decision we have made. To workaround this, you have to use other form of package configuration like XML, SQL Server, etc.|||

So, tell me... even if thats the case... why my parent variables are not set when a certain Execute SQL Task gets actually executed, inserting incorrect values into the table? This is not a very good procedure... How can we make sure to be using the right values in the child package, based on the parent values?

Regards,

|||This should have work. Did you see any error/warning messages about package failed to apply any parent variable configuration?|||

Nop, no errors and no warnings... This is defenitly odd..

Regards

|||

I have had problems with this as well...

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2045623&SiteID=1

sql

Package Configuration + Environment variable

We are using Package configuration with environment variables. The problem we are having that if we try to open project from other PC (PC 2) it gives the error:

Error1Error loading F0005.dtsx: Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available.z:\visual studio 2005\projects\sales data mart\extract to staging area\F0005.dtsx11

We are using environment variable named DWConfig and have configured correct path in each PC. Ifwe edit package configuration in PC 2 and go thru the same procedures without any amendments the errors is removed for that PC and if, again we OPEN that project in PC 1 it gives same error and if we go thru package configuration wizard again error is removed.

Can any one tell me is there any solution of that problem?

Note: Our project is saved on server (neither PC 1 nor PC 2)

regards,

Anas.

We are using Package configuration with environment variables. The problem we are having that if we try to open project from other PC (PC 2) it gives the error:

Error 1 Error loading F0005.dtsx: Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available. z:\visual studio 2005\projects\sales data mart\extract to staging area\F0005.dtsx 1 1

We are using environment variable named DWConfig and have configured correct path in each PC. If we edit package configuration in PC 2 and go thru the same procedures (Package Configuration Wizard) without any amendments the errors is removed for that PC and if, again we OPEN that project in PC 1 it gives same error and if we go thru package configuration wizard again error is removed.

Can any one tell me is there any solution of that problem?

Note: Our project is saved on server (neither PC 1 nor PC 2)

regards,

Anas.

|||

I have got the answer from:

http://support.microsoft.com/kb/904800/

thanks,

Anas