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â'.
>

Padding partial table name with @variable value

Is there a way to this in SQL
declare @.partial_table_name varchar(128)
set @.partial_table_name = 'mytable'
select * from customer
into tbl_cust_@.partial_table_name
Above i am inserting the values from customer to table
tbl_cust__@.partial_table_name. I want to pass the @.partial_table_name value
to the table so that table name becomes tbl_cust_mytable. This is so that I
can dynamically insert into a table based opon conditions in my store proc.
Any help would be appreciated.
ThanksEXEC('SELECT * INTO tbl_cust_'+@.partial_table_name+' from customer');
I'd be very careful about where @.partial_table_name gets created. SQL
injection can yield to inadvertent statements being executed, such as:
SELECT * INTO tbl_cust_foo FROM (SELECT a=1); UPDATE Employees SET Salary =
Salary * 10 WHERE FullName='Malicious Employee'; SELECT * from customer;
Please give the following article a thorough read:
http://www.sommarskog.se/dynamic_sql.html
"b_dba" <b_dba@.discussions.microsoft.com> wrote in message
news:CB37F1E5-5C85-46B6-B443-6F504E1CD69D@.microsoft.com...
> Is there a way to this in SQL
> declare @.partial_table_name varchar(128)
> set @.partial_table_name = 'mytable'
> select * from customer
> into tbl_cust_@.partial_table_name
> Above i am inserting the values from customer to table
> tbl_cust__@.partial_table_name. I want to pass the @.partial_table_name
> value
> to the table so that table name becomes tbl_cust_mytable. This is so that
> I
> can dynamically insert into a table based opon conditions in my store
> proc.
> Any help would be appreciated.
> Thanks|||Thanks Aaron,
That was very helpful.Its doing what I wanted to do. I would be careful as
where the variable is created.
Appreciate your quick response.
cheers,
b_dba
"Aaron Bertrand [SQL Server MVP]" wrote:

> EXEC('SELECT * INTO tbl_cust_'+@.partial_table_name+' from customer');
> I'd be very careful about where @.partial_table_name gets created. SQL
> injection can yield to inadvertent statements being executed, such as:
> SELECT * INTO tbl_cust_foo FROM (SELECT a=1); UPDATE Employees SET Salary
=
> Salary * 10 WHERE FullName='Malicious Employee'; SELECT * from customer;
> Please give the following article a thorough read:
> http://www.sommarskog.se/dynamic_sql.html
>
>
>
> "b_dba" <b_dba@.discussions.microsoft.com> wrote in message
> news:CB37F1E5-5C85-46B6-B443-6F504E1CD69D@.microsoft.com...
>
>

Padd Character Function To Return Variable Type

All,
I have a UDF for SQL that will padd a varchar value to a specific width and
align left or right. I found this code online somewhere, but the problem is
that it accpets a value up to Varchar(8000) and returns a varchar(8000). I
am writing a query to upload to a mainframe and need fixed-width fields, but
each field is not the same size. I can pass in the correct width to the
function and it padds correctly, but in a DTS transformation to a text file,
it thinks all fields are 8000 characters wide.
I am asking if it is possible to return a variable size data type instead of
a fixed type.
Here is the orig function:
ALTER FUNCTION [dbo].[PaddChar] (
@.ValueToPad varchar(8000),
@.PadCharacter char(1),
@.Justification bit,
@.Width int
)
/ ****************************************
***********************************
*************************
This function allows the USER TO pass IN a string / character value AND it
will padd the value according TO given parameters. The parameters are
AS follows:
@.ValueToPad = Value to be padded BY function.
@.PadCharacter = Character used TO pad a given value.
@.Justification = Justification format bit
0 - Value will be RIGHT justified after padding.
1 - Value will be LEFT justified after padding.
@.Width = Total COLUMN width OF the output after padding.
----
--
NOTE - ALL VALUES passes IN FOR padding must be OF CHAR or
VARCHAR data type.
****************************************
************************************
*************************/
RETURNS varchar(8000) AS
BEGIN
DECLARE @.x int
IF @.ValueToPad IS NULL SET @.ValueToPad = ''
IF @.PadCharacter IS NULL SET @.PadCharacter = ''
SET @.X = @.Width - LEN(@.ValueToPad)
--Right Justify Value
IF @.Justification = 0
BEGIN
SET @.ValueToPad = REPLICATE(@.PadCharacter,@.X) + @.ValueToPad
END
ELSE
--Left Justify Value
BEGIN
SET @.ValueToPad = @.ValueToPad + REPLICATE(@.PadCharacter,@.Width)
END
RETURN @.ValueToPad
END
and what I want basically and which doesn't seem to work, is:
ALTER FUNCTION [dbo].[PaddChar] (
@.ValueToPad varchar(8000),
@.PadCharacter char(1),
@.Justification bit,
@.Width int
)
/ ****************************************
***********************************
*************************
This function allows the USER TO pass IN a string / character value AND it
will padd the value according TO given parameters. The parameters are
AS follows:
@.ValueToPad = Value to be padded BY function.
@.PadCharacter = Character used TO pad a given value.
@.Justification = Justification format bit
0 - Value will be RIGHT justified after padding.
1 - Value will be LEFT justified after padding.
@.Width = Total COLUMN width OF the output after padding.
----
--
NOTE - ALL VALUES passes IN FOR padding must be OF CHAR or
VARCHAR data type.
****************************************
************************************
*************************/
RETURNS varchar(@.Width) AS
BEGIN
DECLARE @.x int
IF @.ValueToPad IS NULL SET @.ValueToPad = ''
IF @.PadCharacter IS NULL SET @.PadCharacter = ''
SET @.X = @.Width - LEN(@.ValueToPad)
--Right Justify Value
IF @.Justification = 0
BEGIN
SET @.ValueToPad = REPLICATE(@.PadCharacter,@.X) + @.ValueToPad
END
ELSE
--Left Justify Value
BEGIN
SET @.ValueToPad = @.ValueToPad + REPLICATE(@.PadCharacter,@.Width)
END
RETURN @.ValueToPad
END
Does anyone have any ideas on this?
Thanks,
NathanWhen I need to export fixed-width fields my approach is a bit
different. I create a view that formats each column the way I need it
to a fixed length, then export from the view. Examples of formatting:
convert(char(30), ItemName) as ItemName
STR(ItemValue,9,2)
Roy|||Thank you, I rarely use CHAR since it does padd for storage in the database,
duh! That worked to replace my instances of the dbo.PaddChar in my query fo
r
all the intstances where I am just padding on the right with spaces.
However, I had to just wrap a couple of them that I am right aligning, or
padding with 0's.
Since my query only pulls back a few rows a day the performance isn't bad.
Thank you again,
Nathan
"Roy Harvey" wrote:

> When I need to export fixed-width fields my approach is a bit
> different. I create a view that formats each column the way I need it
> to a fixed length, then export from the view. Examples of formatting:
> convert(char(30), ItemName) as ItemName
> STR(ItemValue,9,2)
> Roy
>

PackageVariable setting does not work in another environment

hi

I have this variable setting in C# in my self developement project works fine.

but when i implement it in actual project does not work.

/Set \\package.Variables[User::ArchiveExcelFileConnectSt].Properties[Value];\"" + xlspath + "\"

when i test it in SQl query analyzer i need to replace // with / and works fine. but when i implement in c# it does not in my actual project. both have different Database.

Do i need to turn on some thing in my Actual Databas that my project points too or is there some thing else.

Thanks,

Jasmien

Take a look at this thread...

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

Are you trying to do this in a SQL statement? Or are you using the Microsoft.SQLServer.ManagedDTS library?

|||

i fixed that problem . I was basically my production was pointing to one database and in part of my code i was trying to over write with my local database so it was conflicting.

sql

package variable question

Hi Guys,

I'm trying to evaluate a user variable to ToLine expression property from SendMailTask.
My Question is, Is it possible to evaluate a user variable in expression(..)?

So, you have a variable in the user namespace that contains the string you want to use for hte ToLine? If so, given a variable named MyVar, the expression to use would be:

@.[User::MyVar]

Let me know if I misunderstood your question.
Mark|||

Larry Surat wrote:

Hi Guys,

I'm trying to evaluate a user variable to ToLine expression property from SendMailTask.
My Question is, Is it possible to evaluate a user variable in expression(..)?

Yes. Select your variable in the variables pane. Then, over in the properties pane, set EvaluateAsExpression=TRUE and Expression=<your expression>

-Jamie|||Hi Jamie,

I've tried setting the EvaluateAsExpression=TRUE of my variable but I still got error on my send mail task. The error is: "Error at Send Mail Task:No Recepient Specified".

Here's the situation. I'm retreiving an email address from a row and stores it to a variable varMailTo. From the Send mail task. I used expression for the ToLine Property. Initially, I supplied a value for the ToLine.
|||Hi Mark,

you've got my point...

My problem now is when i supply the said variable to the SendMailTask ToLine Property expression. I get the error. "Send Mail Task error: No recepient is specified" even if initially I supply the ToLine with an initial value. After executing the task, it removes the value I supplied.

I also tried setting the EvaluateAsExpression property of my variable to true..
|||That sounds like it should work Larry. Your process sounds correct so there's a small error in there somewhere, perhaps a syntax error or something.

Try to isolate the different parts of what you're trying to do. Good old message boxes can help here with debugging.

-Jamie|||Actually it sounds like you are not setting the variable in the correct location. Are you entering the variable name for the to line in the mail section of the editor or are you adding an expression for the to line from the expressions section of the editor? The former will not work because it takes the string as a literal. The latter is what you want because the variable's value will be used as the to line.

Setting the variable to evaluate as expression just means you have an expression in the variable that needs to be evaluated before it is applied to the property. So if your variable contained recip@.company.com you wouldn't need to evaluate as expression but if it contained "recip@." + "company.com" then you would since the variable needs to first be evaluated before being set into the property.

HTH,
Mattsql

Package Variable Names are Case Sensitive

Hi I just discoved that Package Variable Names are case sensitive. Is it a feature or a bug. Microsoft ...Case Sensitive!!!!It's by design. Case insensitivity is OK for VB, but they also have a lot more control over the key words and the environment WRT locale. We avoided those issues by being case sensitive.
K

Package Configuration with Environment Variable

Hi,

I have issues with the Connection Manager in the SSIS package when using package configs thru environment variable.

Here goes..

SSIS package1:

Connections used: devcon1, devcon2 - Dev Env and testcon1,testcon2 - Test Env. Now using all four. Ideally either devcons or testcons should reside at a time.

Environment variable:

Pckg_config = <location of config file which has testcon1 and testcon2>

I need to use only devcon1 and devcon2 in Dev env. In test i need to use only testcon1 and testcon2

Hence i set the values of devcons in devEnv.dtsconfig and testcons in testEnv.dtsconfig

Now i remove both testcons from ssis package. If i try to run the Test Env and my testcons which are marked in testenv.dtsconfig are not found as connections in ssis package then the ssis gives error wanting for those connections.

SSIS maintains the connections in the Connection Manager per package. Although internally it is a pool of connections.

Ideally i should be able to play around with the connection at run time. My package now works, if it is deployed with all the devcons and testcons together. However, ideally it should be either devcons or testcons. I am trying to be more explicable to reach to the masses here.

Am i doing something wrong? All your efforts in solving this puzzle will be greatly appreciated. Please participate.

Thanx,

TusharYou should have only two connections (devcon1 and 2) defined in your package. Set up a single configuration file that sets the two connections. When you deploy to test, you need to create a new copy of the configuration, and alter the connection strings in it to point to your test environment. So you should be using the same package file, but different configuration files in each environment.|||Thanx for responding.

I have followed exactly what you said with my prior set up. I have a devEnv.dtsconfig with devcons and testEnv.dtsConfig with testcons.

The issue is with the package. While creating the package i created it with devcons. Additionally i have testcons which do nothing but sit with the package and get deployed on the server.

If i do not add testcons.....testEnv.dtsconfig gives connection errors....when trying to locate testcons.

Let me know if you need more info...

Thanx again.

Tushar
|||You should have only two connection managers in your package. You should not have testcon1 and testcon2. To create your 2nd configuration file, copy the first configuration file into a new folder - but keep the same name. Open it in notepad, and edit the <ConfiguredValue> tag to set it to your test connection.

When you run the package for test, use the DTEXEC /CONF option to specify the 2nd configuration file.

Package configuration using Environment variable

I am doing SSIS package configuration using environment variable.

I have created a system environment variable that points to the dtsConfig file.

I opened the package and choosen the configuration type as environment variable and specified the environment variable

When I click the next button , it doesn't allow me to choose the configurable property.

Please suggest

If you want to have the location of the configuration file stored in an environment variable, but use the config file to actually store settings, you need to set the configuration type to XML configuration file, and set the "configuration location is stored in an environment variable" option. That's an indirect configuration.

Package configuration using Environment variable

I am doing SSIS package configuration using environment variable.

I have created a system environment variable that points to the dtsConfig file.

I opened the package and choosen the configuration type as environment variable and specified the environment variable

When I click the next button , it doesn't allow me to choose the configurable property.

Please suggest

If you want to have the location of the configuration file stored in an environment variable, but use the config file to actually store settings, you need to set the configuration type to XML configuration file, and set the "configuration location is stored in an environment variable" option. That's an indirect configuration.

Package Configuration in SQL

I have an OLEDB SQL Connection that has a connection string that is set as and Expression using a User Variable (@.[User::server_name]). I pass the server name as a command line option. The Package Configuration table is being accessed using the setting for User::server_name in the package and not using the parameter entry for it.
The command being issued is similar to:

dtexec /FILE "packagename.dtsx" /SET "\package.variables[server_name]";dbserver

I am wondering if this by design? I would think that all parameter variables would be set prior to any other configuration entries are processed.

Thanks,
MikeHi Mike,

Do you get any warnings or errors with the command line you specified? Is there more than one variable called server_name with a different namespace or at different scope? You can also add the /rep v option and see if there is any other information that might be useful to determine what is happening.

Thanks,
Matt|||Here is the error I was getting:

Started: 11:27:03 AM
Warning: 2005-12-13 11:27:06.54
Code: 0x8002F304
Source: packagename
Description: A problem occurred with the following messages: "Invalid object
name 'dbo.SSISConfigurations'.".
End Warning
Warning: 2005-12-13 11:27:06.54
Code: 0x80012059
Source: packagename
Description: Failed to load at least one of the configuration entries for the
package. Check configurations entries and previous warnings to see descriptions
of which configuration failed.
End Warning

The table dbo.SSISConfigurations does not exist on the server I had set in the package, but does on the server I am passing as a parameter. This was done specifically to test for this type of issue, where when deployed to a client that does not have the server configured in the package.

Thanks,
Mike|||It looks to me that you try to apply configuration from a server that you want to be specified at runtime.

The problem is that configuration happens at load time and before any set from the command line.

The way I see it is to have define din the package a configuration that would set the desired server name in your variable and this configuration is applied before any other configuration.

HTH,
Ovidiu Burlacu|||Thanks for the feedback. You confirmed what I suspected about the order of evaluation between Configuration and Parameters.

I have attempted to run dtexec with /ConfigFile parameter but it errors with unable to locate the configuration file. I put the full path of the configuration file and it still errors:

dtexec /FILE "C:\SSIS\Package.dtsx" /CONFIGFILE "C:\SSIS\TestConfig.dtsConfig" /MAXCONCURRENT 1 /CHECKPOINTING OFF

I am running on IA64 system, so I tried both 32bit and 64bit dtexec programs and they error the same. So not sure where to go from here.

Thanks,
Mike|||The solution you are trying will not work and the reason is the sequence of configurations:
1. Configurations defined in the package
2. Configurations specified in the command line
You need to open the package in the designer and specify the configuration for your variable in the package and make sure that this is the first configuration in the list.

HTH,
Ovidiu|||

I have tried in the designer to set the configuration file, but it has a fully qualified path to the configuration file. So when deployed, it will not match correctly on a that system.

I would think that the system should process all command line parameters prior to loading any configurations. Maybe I should submit this as a wish list item. I just don't see how to override on a deployed system. This seems a lot like the Chicken and the Egg...

Thanks,
Mike

|||Since you can have multiple configurations in a you should have one configuration (the first one) that loads the server name from something that can be setup on each computer that will run packages (this could be an environment variable or a configuration file that will always be in a fixed location). Then the second configuration would be able to work because the first one would be able to already configure the server name.

What you want is problematic because it implies interaction in loading configurations from the utility that loads the package. If this was done then anyone could easily circumvent configuration loading by not requesting the package to load up its configurations. While you could argue the same thing is happening now just by the package not being able to find the configuration it is actually different because in the first case there would be no warning informing you that the configuration was not applies, whereas in the second there is always a warning and therefore you are aware of the problem as opposed to the package just not running and you having no idea why. All this being said please feel free to submit this as a wish list item, I was just trying to give you insight as to why it is the way it is.

Thanks,
Matt

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

package configuration

Does it matter if the configuration is by xml,sql server,parent package variable,environment variable etc.

I use xml configuration which i kind of feel easy as i can change the connection directly by typing.Not used with others much..

Is there any security issue here .

Please let me know

Hi,

I use XML too and I pass it from environment variable which gives more portability.

For security, yes, this is a concern. my XML config holds the user name and password in the connection string itself.

You have to secure the folder where you store the config file.

A server admin will always have the possibility to look into it though.

Regards,

Philippe

|||

SQL Server table has the added benefit that you can add as many configurations as you want and you won't see any warning if they all are not used in a package (not to mention you go to a single place where a change is required). Obviously, you need to store the connection string to that table somewhere else, and likely you would use the 'indirect' method or a second (XML, env. variable, etc).

The Environment variable and Registry key types, require a high level of permission at the OS level; which in some scenarios can not be an option (security policies, etc).

Parent variable type has the limitation that you don't have control over the order on which they

take place. They always get configured in the last place.

As you can see there is no perfect method, but yet you have a several options.

|||

When I store configuration information on sql server, is it safe to assume that I just the need the connection for that sqlserver/table and I'm done?

Also is it possible to directly insert/update/delete data to that table?

Thanks

|||

Yes (I think, depends on exactly what you are asking), and yes.