Padding using Type 4 Drivers

Hope someone can advise me (a humble Java Programmer) of a problem I
am having.
We have a char(8) field in a MS SQL Server 7 DB, some of the data is
only 7 chars though.
We have recently upgraded to MS SQL Srever 2000.
The problem is, the JDBC queries now return space padded.
so that "1234567" comes back as "12345678 ".
They didn't do this before.
I understand there is an ODBC configuration, but as we are using
type-4 JDBC drivers, we don't go via the JDBC-ODBC bridge. We go
direct to the DB.
Does anybody know what configuration a SQL Server DB has which effects
the ANSI padding when using a type-4 JDBC driver?
I am clueless on this I can tell you!.
| From: prithpal.roda@.talk21.com (Prithpal)
| Newsgroups: microsoft.public.sqlserver.jdbcdriver
| Subject: Padding using Type 4 Drivers
| Date: 16 Nov 2004 08:40:55 -0800
| Organization: http://groups.google.com
| Lines: 19
| Message-ID: <b47276f6.0411160840.6acfded0@.posting.google.com >
| NNTP-Posting-Host: 194.72.54.254
| Content-Type: text/plain; charset=ISO-8859-1
| Content-Transfer-Encoding: 8bit
| X-Trace: posting.google.com 1100623255 12885 127.0.0.1 (16 Nov 2004
16:40:55 GMT)
| X-Complaints-To: groups-abuse@.google.com
| NNTP-Posting-Date: Tue, 16 Nov 2004 16:40:55 +0000 (UTC)
| Path:
cpmsftngxa10.phx.gbl!TK2MSFTFEED02.phx.gbl!TK2MSFT NGP08.phx.gbl!news-out.cwi
x.com!newsfeed.cwix.com!newsfeed.icl.net!proxad.ne t!216.239.36.134.MISMATCH!
postnews.google.com!not-for-mail
| Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.jdbcdriver:6475
| X-Tomcat-NG: microsoft.public.sqlserver.jdbcdriver
|
| Hope someone can advise me (a humble Java Programmer) of a problem I
| am having.
|
| We have a char(8) field in a MS SQL Server 7 DB, some of the data is
| only 7 chars though.
| We have recently upgraded to MS SQL Srever 2000.
|
| The problem is, the JDBC queries now return space padded.
| so that "1234567" comes back as "12345678 ".
| They didn't do this before.
|
| I understand there is an ODBC configuration, but as we are using
| type-4 JDBC drivers, we don't go via the JDBC-ODBC bridge. We go
| direct to the DB.
|
| Does anybody know what configuration a SQL Server DB has which effects
| the ANSI padding when using a type-4 JDBC driver?
|
| I am clueless on this I can tell you!.
|
Hello,
It is possible that your SQL Server 7 was using a different ANSI_PADDING
value than what you have set in SQL Server 2000.
Whenever you make a connection to SQL Server, you can specify the ANSI
settings that you require. If you do not specify these explicitly, then
your connection will receive a default set of ANSI settings based on the
configuration in SQL Server. You can use the Security Audit -> Audit Login
event in SQL Profiler to observe the ANSI settings used when your
connection is first made. Your Profiler output may look something like
this:
-- network protocol: TCP/IP
set quoted_identifier on
set implicit_transactions off
set cursor_close_on_commit off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set language us_english
set dateformat mdy
set datefirst 7
These default settings are documented in the SQL Server Books Online under
the topic "SET"
(http://msdn.microsoft.com/library/de...-us/tsqlref/ts
_set-set_40c4.asp) under the subheading "SQL-92 Settings Statements". You
can also run DBCC USEROPTIONS from within your connection to see this
information.
Are you using the Microsoft JDBC driver or a third-party driver?.
Carb Simien, MCSE MCDBA MCAD
Microsoft Developer Support - Web Data
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.
|||CarbinoS@.online.microsoft.com ("Carb Simien [MSFT]") wrote in message news:<9aT3U$AzEHA.3956@.cpmsftngxa10.phx.gbl>...
> --
> Hello,
> It is possible that your SQL Server 7 was using a different ANSI_PADDING
> value than what you have set in SQL Server 2000.
> Whenever you make a connection to SQL Server, you can specify the ANSI
> settings that you require. If you do not specify these explicitly, then
> your connection will receive a default set of ANSI settings based on the
> configuration in SQL Server. You can use the Security Audit -> Audit Login
> event in SQL Profiler to observe the ANSI settings used when your
> connection is first made. Your Profiler output may look something like
> this:
> -- network protocol: TCP/IP
> set quoted_identifier on
> set implicit_transactions off
> set cursor_close_on_commit off
> set ansi_warnings on
> set ansi_padding on
> set ansi_nulls on
> set concat_null_yields_null on
> set language us_english
> set dateformat mdy
> set datefirst 7
> These default settings are documented in the SQL Server Books Online under
> the topic "SET"
> (http://msdn.microsoft.com/library/de...-us/tsqlref/ts
> _set-set_40c4.asp) under the subheading "SQL-92 Settings Statements". You
> can also run DBCC USEROPTIONS from within your connection to see this
> information.
> Are you using the Microsoft JDBC driver or a third-party driver?.
> Carb Simien, MCSE MCDBA MCAD
> Microsoft Developer Support - Web Data
> Please reply only to the newsgroups.
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Are you secure? For information about the Strategic Technology Protection
> Program and to order your FREE Security Tool Kit, please visit
> http://www.microsoft.com/security.
Thanks very much for the information.
We are indeed using a third-party driver as we are not aware of a
Microsoft Type 4 driver which will connect to sql server 7.
We have performed the tests using a weblogic driver and jtds, both
having the same results. The DBA insist that the sql server 7 and sql
server 2000 are configured the same..... but I have my doubts!.
|||I ran the suggested DBCC OPTIONS command for the box that has NO padding.
This is what I get:
C:\temp\JDBC>java -cp .;jtds-0.9.jar ConnectInfo staging
Running against staging
textsize 2147483647
language us_english
dateformat mdy
datefirst 7
quoted_identifier SET
ansi_null_dflt_on SET
ansi_defaults SET
ansi_warnings SET
ansi_padding SET
ansi_nulls SET
concat_null_yields_null SET
oes that make any sense? I get no padding, but padding is set for the connection.
|||| From: prithpal.roda@.talk21.com (Prithpal)
| Newsgroups: microsoft.public.sqlserver.jdbcdriver
| Subject: Re: Padding using Type 4 Drivers
| Date: 17 Nov 2004 01:38:32 -0800
| Organization: http://groups.google.com
| Lines: 54
| Message-ID: <b47276f6.0411170138.2d960a00@.posting.google.com >
| References: <b47276f6.0411160840.6acfded0@.posting.google.com >
<9aT3U$AzEHA.3956@.cpmsftngxa10.phx.gbl>
| NNTP-Posting-Host: 194.72.54.254
| Content-Type: text/plain; charset=ISO-8859-1
| Content-Transfer-Encoding: 8bit
| X-Trace: posting.google.com 1100684312 31457 127.0.0.1 (17 Nov 2004
09:38:32 GMT)
| X-Complaints-To: groups-abuse@.google.com
| NNTP-Posting-Date: Wed, 17 Nov 2004 09:38:32 +0000 (UTC)
| Path:
cpmsftngxa10.phx.gbl!TK2MSFTNGXA02.phx.gbl!cpmsftn gxa06.phx.gbl!TK2MSFTNGP08
.phx.gbl!news-out.cwix.com!newsfeed.cwix.com!newsfeed.yul.equant .net!newsrea
d.com!newsprint.newsread.com!postnews.google.com!n ot-for-mail
| Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.jdbcdriver:6480
| X-Tomcat-NG: microsoft.public.sqlserver.jdbcdriver
|
| CarbinoS@.online.microsoft.com ("Carb Simien [MSFT]") wrote in message
news:<9aT3U$AzEHA.3956@.cpmsftngxa10.phx.gbl>...
| > --
| > Hello,
| >
| > It is possible that your SQL Server 7 was using a different
ANSI_PADDING
| > value than what you have set in SQL Server 2000.
| >
| > Whenever you make a connection to SQL Server, you can specify the ANSI
| > settings that you require. If you do not specify these explicitly,
then
| > your connection will receive a default set of ANSI settings based on
the
| > configuration in SQL Server. You can use the Security Audit -> Audit
Login
| > event in SQL Profiler to observe the ANSI settings used when your
| > connection is first made. Your Profiler output may look something like
| > this:
| >
| > -- network protocol: TCP/IP
| > set quoted_identifier on
| > set implicit_transactions off
| > set cursor_close_on_commit off
| > set ansi_warnings on
| > set ansi_padding on
| > set ansi_nulls on
| > set concat_null_yields_null on
| > set language us_english
| > set dateformat mdy
| > set datefirst 7
| >
| > These default settings are documented in the SQL Server Books Online
under
| > the topic "SET"
| >
(http://msdn.microsoft.com/library/de...-us/tsqlref/ts
| > _set-set_40c4.asp) under the subheading "SQL-92 Settings Statements".
You
| > can also run DBCC USEROPTIONS from within your connection to see this
| > information.
| >
| > Are you using the Microsoft JDBC driver or a third-party driver?.
| >
| > Carb Simien, MCSE MCDBA MCAD
| > Microsoft Developer Support - Web Data
| >
| > Please reply only to the newsgroups.
| > This posting is provided "AS IS" with no warranties, and confers no
rights.
| >
| > Are you secure? For information about the Strategic Technology
Protection
| > Program and to order your FREE Security Tool Kit, please visit
| > http://www.microsoft.com/security.
|
| Thanks very much for the information.
|
| We are indeed using a third-party driver as we are not aware of a
| Microsoft Type 4 driver which will connect to sql server 7.
|
| We have performed the tests using a weblogic driver and jtds, both
| having the same results. The DBA insist that the sql server 7 and sql
| server 2000 are configured the same..... but I have my doubts!.
|
In an ealier post, you mentioned upgrading from SQL 7 to SQL 2000. Are you
seeing the same behavior with both database versions?
I did some more research for this question, and here is what I found. For
SQL Server 2000, the ANSI_PADDING setting is applied when the table is
created. If ANSI_PADDING was turned off when the table was created, then
the char(8) column would return the data with trailing spaces removed
(assuming the column allows nulls). If ANSI_PADDING was on when the table
was created, then the char(8) column would return the trailing space. This
behavior can be observed in Query Analyzer:
SET ANSI_PADDING
http://msdn.microsoft.com/library/de...us/tsqlref/ts_
set-set_2uw7.asp
In another test, I used the Microsoft JDBC driver to query the table that
was created with ANSI_PADDING turned off. I still observe the trailing
spaces in this scenario. I'm not sure why this is the case, but the
behavior appears to be consistent with the other JDBC drivers you have
tested.
All things aside, you should consider using a variable-length data type for
your column definitions if you are concerned with trailing spaces in your
table.
Carb Simien, MCSE MCDBA MCAD
Microsoft Developer Support - Web Data
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.

Padding an auto-incremented Int column to ten zeros

Good day gentleman,
Is it possible to pad columns of Int data type with zeros?

I currently have a primary key column of data type Int which is auto-incremented by 1. My problem is I need to pad the auto-incremented ID's with ten zeros so, 0000000009 instead of 9.

I can pad the ID's in the c# dataaccess layer, but i need to generate/store them padded in the database to allow direct reporting.

I am using Enterprise Manager and i can see a Formula field when the table is in design view, can i use this? i am just guessing here.

Any comments or pointers would be a big help.What you are discussing is a display issue. An int is, by definition, isolated from any particular display formatting. It is just a number. You could add an additional column (nvarchar) and have a trigger on inserts that populates that nvarchar() column with a string value with 0 padding.

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
>

Package Configuration using xml-file fails

Hallo,

I use SSIS Version 9.00.1399.00 and keep getting problems trying to use package configuration.

I choose xml configuration file as type and browse a path on our LAN to create te configuration file .

Then I select the properties of a OLEDB communication manager as Properties to Export

Doing so I obtain this configuration:Name:
JACBE_IF_CONFIG

Type:
Configuration File

New configuration file will be created.

File name:
L:\Projects\Vinci\SSIS\DVL\FMC loader Import\FMC Loader Import\FMC Loader Import\JACBE_IF_CONFIG.xml

Properties:
\Package.Connections[JACBE_IF].Properties[UserName]
\Package.Connections[JACBE_IF].Properties[ServerName]
\Package.Connections[JACBE_IF].Properties[RetainSameConnection]
\Package.Connections[JACBE_IF].Properties[ProtectionLevel]
\Package.Connections[JACBE_IF].Properties[Password]
\Package.Connections[JACBE_IF].Properties[Name]
\Package.Connections[JACBE_IF].Properties[InitialCatalog]
\Package.Connections[JACBE_IF].Properties[Description]
\Package.Connections[JACBE_IF].Properties[ConnectionString]

The system creates a XML file but when I run the package I get the following error in the output pane.

Information: 0x40016041 at FMC_People: The package is attempting to configure from the XML file "L:\Projects\Vinci\SSIS\DVL\FMC loader Import\FMC Loader Import\FMC Loader Import\JACBE_IF_CONFIG.xml".

SSIS package "FMC_People.dtsx" starting.

Information: 0x4004300A at Dataprocessing_PEOPLE, DTS.Pipeline: Validation phase is beginning.

Error: 0xC0202009 at FMC_People, Connection manager "JACBE_IF": An OLE DB error has occurred. Error code: 0x80040E21.

An OLE DB record is available.Source: "Microsoft OLE DB Service Components"Hresult: 0x80040E21Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".

Error: 0xC020801C at Dataprocessing_PEOPLE, FMC_ARE_PRESENT_destination 1 [22338]: The AcquireConnection method call to the connection manager "JACBE_IF" failed with error code 0xC0202009.

Error: 0xC0047017 at Dataprocessing_PEOPLE, DTS.Pipeline: component "FMC_ARE_PRESENT_destination 1" (22338) failed validation and returned error code 0xC020801C.

Error: 0xC004700C at Dataprocessing_PEOPLE, DTS.Pipeline: One or more component failed validation.

Error: 0xC0024107 at Dataprocessing_PEOPLE: There were errors during task validation.

SSIS package "FMC_People.dtsx" finished: Failure.

I don't get it. Where do I go wrong?

I tried the same with a DtsConfig file instead of an XML but to no avail. the way of working as described in BOL and in the book professional SQL SERVER 2005 Integration service seems to me perfectly similar.

Any ideas anyone? I would be most gratefull.

Kind regards,

Paul Baudouin

It won't store the password for you - you have to add it in manually. Check the contents of the file to see if the password is there (if you need it there).

By the way, you only need to store the ConnectionString property. Take out all the rest and give it a go!

-Jamie

|||

Dear Jamie,

You are a real help!

Your solution , only adding the connectionstring to the configuration file, worked when I manually added ';Password = @.@.@.@.@.' to the ConnectionString in the configurationfile.

(For security reasons I put @.@.@.@.@. instead of the real pasword.)

It would be nice if I could put an encrypted password in the configuration file instead of the plain string. I guess this is possible in SSIS?

Greetings,

Paul

|||

Paul Baudouin wrote:

It would be nice if I could put an encrypted password in the configuration file instead of the plain string. I guess this is possible in SSIS?

Unfortunately not! Encrypted configuration files is soething I have suggested and I believe it is being considered for future development.

If you want it - ask for it at Microsoft Connect. if you don't ask you don't get!

-Jamie

|||

If this was enabled, the config would most likely need to be encrypted with a certificate. Would that be acceptable?

We don't want to protect the config with a password, because then where do you store that password, and so on ...

|||

Paul,

how did you add it manually, this is how my connection string in the configuration file looks like

\Package.Connections[mydatabase].Properties[Password]

thanks,

kushpaw

|||

Donald Farmer wrote:

If this was enabled, the config would most likely need to be encrypted with a certificate. Would that be acceptable?

We don't want to protect the config with a password, because then where do you store that password, and so on ...

Absolutely. that makes sense to me Donald.

|||

Kushpaw,

As I said above, you're probably best storing the ConenctioNStrig property rather than the password. Thereafter it will be obvious where you put the password.

-Jamie

|||

Dont mean to reopen this question but ..I have a package whose config is stored in an SQL Table. We r still in deplyment phase and what puzzles me is how will the package know first time when it runs where to look for a config file as prod environment will have a diff sql server. IS there a way to have an xml file which will tell the package which db to look in for rest of the config file. Do I make sense?

My present config which is in sql table has config vals for FlatFile CS, two SQL DB CS I am presuming one db CS is for the SQL Destination component and the other one is to tell the package where to look for the SSIS Configurations table.

Can I remove this last Db CS store in an XML file so that when we move to production we can edit this one XML file and go on....

|||

ray_newbie_SSIS wrote:

Dont mean to reopen this question but ..I have a package whose config is stored in an SQL Table. We r still in deplyment phase and what puzzles me is how will the package know first time when it runs where to look for a config file as prod environment will have a diff sql server. IS there a way to have an xml file which will tell the package which db to look in for rest of the config file. Do I make sense?

Yes that makes sense. The way to do this is to use Indirect Configurations:

Indirect configurations ROCK!
(http://blogs.conchango.com/jamiethomson/archive/2005/11/02/2342.aspx)

Your other option is to populate the properties that can change between environments using the /SET option of dtexec. I wouldn't recommend this though - configurations are generally the way to go.

-Jamie

Package Configuration using xml-file fails

Hallo,

I use SSIS Version 9.00.1399.00 and keep getting problems trying to use package configuration.

I choose xml configuration file as type and browse a path on our LAN to create te configuration file .

Then I select the properties of a OLEDB communication manager as Properties to Export

Doing so I obtain this configuration:Name:
JACBE_IF_CONFIG

Type:
Configuration File

New configuration file will be created.

File name:
L:\Projects\Vinci\SSIS\DVL\FMC loader Import\FMC Loader Import\FMC Loader Import\JACBE_IF_CONFIG.xml

Properties:
\Package.Connections[JACBE_IF].Properties[UserName]
\Package.Connections[JACBE_IF].Properties[ServerName]
\Package.Connections[JACBE_IF].Properties[RetainSameConnection]
\Package.Connections[JACBE_IF].Properties[ProtectionLevel]
\Package.Connections[JACBE_IF].Properties[Password]
\Package.Connections[JACBE_IF].Properties[Name]
\Package.Connections[JACBE_IF].Properties[InitialCatalog]
\Package.Connections[JACBE_IF].Properties[Description]
\Package.Connections[JACBE_IF].Properties[ConnectionString]

The system creates a XML file but when I run the package I get the following error in the output pane.

Information: 0x40016041 at FMC_People: The package is attempting to configure from the XML file "L:\Projects\Vinci\SSIS\DVL\FMC loader Import\FMC Loader Import\FMC Loader Import\JACBE_IF_CONFIG.xml".

SSIS package "FMC_People.dtsx" starting.

Information: 0x4004300A at Dataprocessing_PEOPLE, DTS.Pipeline: Validation phase is beginning.

Error: 0xC0202009 at FMC_People, Connection manager "JACBE_IF": An OLE DB error has occurred. Error code: 0x80040E21.

An OLE DB record is available.Source: "Microsoft OLE DB Service Components"Hresult: 0x80040E21Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".

Error: 0xC020801C at Dataprocessing_PEOPLE, FMC_ARE_PRESENT_destination 1 [22338]: The AcquireConnection method call to the connection manager "JACBE_IF" failed with error code 0xC0202009.

Error: 0xC0047017 at Dataprocessing_PEOPLE, DTS.Pipeline: component "FMC_ARE_PRESENT_destination 1" (22338) failed validation and returned error code 0xC020801C.

Error: 0xC004700C at Dataprocessing_PEOPLE, DTS.Pipeline: One or more component failed validation.

Error: 0xC0024107 at Dataprocessing_PEOPLE: There were errors during task validation.

SSIS package "FMC_People.dtsx" finished: Failure.

I don't get it. Where do I go wrong?

I tried the same with a DtsConfig file instead of an XML but to no avail. the way of working as described in BOL and in the book professional SQL SERVER 2005 Integration service seems to me perfectly similar.

Any ideas anyone? I would be most gratefull.

Kind regards,

Paul Baudouin

It won't store the password for you - you have to add it in manually. Check the contents of the file to see if the password is there (if you need it there).

By the way, you only need to store the ConnectionString property. Take out all the rest and give it a go!

-Jamie

|||

Dear Jamie,

You are a real help!

Your solution , only adding the connectionstring to the configuration file, worked when I manually added ';Password = @.@.@.@.@.' to the ConnectionString in the configurationfile.

(For security reasons I put @.@.@.@.@. instead of the real pasword.)

It would be nice if I could put an encrypted password in the configuration file instead of the plain string. I guess this is possible in SSIS?

Greetings,

Paul

|||

Paul Baudouin wrote:

It would be nice if I could put an encrypted password in the configuration file instead of the plain string. I guess this is possible in SSIS?

Unfortunately not! Encrypted configuration files is soething I have suggested and I believe it is being considered for future development.

If you want it - ask for it at Microsoft Connect. if you don't ask you don't get!

-Jamie

|||

If this was enabled, the config would most likely need to be encrypted with a certificate. Would that be acceptable?

We don't want to protect the config with a password, because then where do you store that password, and so on ...

|||

Paul,

how did you add it manually, this is how my connection string in the configuration file looks like

\Package.Connections[mydatabase].Properties[Password]

thanks,

kushpaw

|||

Donald Farmer wrote:

If this was enabled, the config would most likely need to be encrypted with a certificate. Would that be acceptable?

We don't want to protect the config with a password, because then where do you store that password, and so on ...

Absolutely. that makes sense to me Donald.

|||

Kushpaw,

As I said above, you're probably best storing the ConenctioNStrig property rather than the password. Thereafter it will be obvious where you put the password.

-Jamie

|||

Dont mean to reopen this question but ..I have a package whose config is stored in an SQL Table. We r still in deplyment phase and what puzzles me is how will the package know first time when it runs where to look for a config file as prod environment will have a diff sql server. IS there a way to have an xml file which will tell the package which db to look in for rest of the config file. Do I make sense?

My present config which is in sql table has config vals for FlatFile CS, two SQL DB CS I am presuming one db CS is for the SQL Destination component and the other one is to tell the package where to look for the SSIS Configurations table.

Can I remove this last Db CS store in an XML file so that when we move to production we can edit this one XML file and go on....

|||

ray_newbie_SSIS wrote:

Dont mean to reopen this question but ..I have a package whose config is stored in an SQL Table. We r still in deplyment phase and what puzzles me is how will the package know first time when it runs where to look for a config file as prod environment will have a diff sql server. IS there a way to have an xml file which will tell the package which db to look in for rest of the config file. Do I make sense?

Yes that makes sense. The way to do this is to use Indirect Configurations:

Indirect configurations ROCK!
(http://blogs.conchango.com/jamiethomson/archive/2005/11/02/2342.aspx)

Your other option is to populate the properties that can change between environments using the /SET option of dtexec. I wouldn't recommend this though - configurations are generally the way to go.

-Jamie

Package Configuration - XML

Type:
Configuration File

New configuration file will be created.

File name:
C:\Test.dtsconfig

Properties:
\Package.Connections[Flat File Connection Manager].Properties[ConnectionString]

So lets say, I modify the connectionstring for the Flat File connection manager by editing the xml file. Next time, I run the package, it does not grab the new value. Am I misunderstanding the concept of Package Configuration?

No, you're not misunderstanding. This should be working. if the package uses a configuration file then messages will be output to the Output window indicating this. My best guess is tat something is configured incorrectly.

Also, are you using checkpoint files? If so, make sure that no checkpoint file is present to make sure that the package starts from scratch.

-Jamie

|||

Well what I did to get it working was the following:

On my config file i'm putting the values of variables, and i mapped the expressions to the variables. Thus, when I edit the xml file, I can change the value of lets say username to Bob and in return since i have it mapped, it automatically takes that value and applies it according to the connection.

|||The thing that usually gets me with configuration files is case sensitivity. A Source connection is not the same as a source connection. So if you have SuppressConfigurationWarnings attribute in your package set to True, the configuration file property is not used and you're not even aware you have a problem. If I had a dollar for every time...

Package Configuration - XML

Type:
Configuration File

New configuration file will be created.

File name:
C:\Test.dtsconfig

Properties:
\Package.Connections[Flat File Connection Manager].Properties[ConnectionString]

So lets say, I modify the connectionstring for the Flat File connection manager by editing the xml file. Next time, I run the package, it does not grab the new value. Am I misunderstanding the concept of Package Configuration?

No, you're not misunderstanding. This should be working. if the package uses a configuration file then messages will be output to the Output window indicating this. My best guess is tat something is configured incorrectly.

Also, are you using checkpoint files? If so, make sure that no checkpoint file is present to make sure that the package starts from scratch.

-Jamie

|||

Well what I did to get it working was the following:

On my config file i'm putting the values of variables, and i mapped the expressions to the variables. Thus, when I edit the xml file, I can change the value of lets say username to Bob and in return since i have it mapped, it automatically takes that value and applies it according to the connection.

|||The thing that usually gets me with configuration files is case sensitivity. A Source connection is not the same as a source connection. So if you have SuppressConfigurationWarnings attribute in your package set to True, the configuration file property is not used and you're not even aware you have a problem. If I had a dollar for every time...

Overloading add/subtract operators for CLR UDT

Hi,

I have an implementation of the UDT - 3-dimentional vector. In my code I have implemented add, subtract and multiply methods for the type. I have also implemented overloaded operators for +/-/* in my C# code. Those overloaded operator are working as expected in C# tests. However when I��m trying to use +/-/* operators in T-SQL over my UDT it returns the following error:

Invalid operator for data type. Operator equals add, type equals Vector.

The following fragment does work:

DECLARE @.v1 Vector, @.v2 Vector, @.v3 Vector;

SELECT @.v1 = CAST('1,1,1' as Vector), @.v2 = CAST('2,2,2' as Vector)

SELECT @.v1 'v1', @.v2 'v2', @.v1.[Add](@.v2) 'v1 + v2'

And this fragment does not work:

DECLARE @.v1 Vector, @.v2 Vector, @.v3 Vector;

SELECT @.v1 = CAST('1,1,1' as Vector), @.v2 = CAST('2,2,2' as Vector)

SELECT @.v1 'v1', @.v2 'v2', @.v1+@.v2 'v1 + v2'

I guess that SQL Server is not aware of the operators�� overload I have implemented in the C# code. Is there any way to instruct SQL Server to use overloaded operators in the T-SQL so the code will look naturally @.a + @.b instead of @.a.[Add](@.b) and as a result use standard summary functions SUM() instead of writing user defined aggregate function for the Vector type field?

Maxim

? No, unfortunately overloading in SQLCLR is not possible. By the way, I prefer to do statics for Add/Subtract/etc, so instead of: @.a.Add(@.b) You'd do: type::Add(@.a, @.b) That way it's a little bit closer to the "a + b" syntax... But obviously not quite the same. You'll also still have to create your own UDA. -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <Maxim Michtchenko@.discussions.microsoft.com> wrote in message news:03e7313f-081c-44f5-89f0-ce44d7de911d@.discussions.microsoft.com... Hi, I have an implementation of the UDT - 3-dimentional vector. In my code I have implemented add, subtract and multiply methods for the type. I have also implemented overloaded operators for +/-/* in my C# code. Those overloaded operator are working as expected in C# tests. However when I��m trying to use +/-/* operators in T-SQL over my UDT it returns the following error: Invalid operator for data type. Operator equals add, type equals Vector. The following fragment does work: DECLARE @.v1 Vector, @.v2 Vector, @.v3 Vector; SELECT @.v1 = CAST('1,1,1' as Vector), @.v2 = CAST('2,2,2' as Vector) SELECT @.v1 'v1', @.v2 'v2', @.v1.[Add](@.v2) 'v1 + v2' And this fragment does not work: DECLARE @.v1 Vector, @.v2 Vector, @.v3 Vector; SELECT @.v1 = CAST('1,1,1' as Vector), @.v2 = CAST('2,2,2' as Vector) SELECT @.v1 'v1', @.v2 'v2', @.v1+@.v2 'v1 + v2' I guess that SQL Server is not aware of the operators�� overload I have implemented in the C# code. Is there any way to instruct SQL Server to use overloaded operators in the T-SQL so the code will look naturally @.a + @.b instead of @.a.[Add](@.b) and as a result use standard summary functions SUM() instead of writing user defined aggregate function for the Vector type field? Maxim|||

Thanks,

I guess I'll have to accept that. By the way I agree with you - static method looks clearner.

Maxim

|||

I have a method that is overloaded. One takes 3 parameters and one takes 5. If overloading does not work, why does the CLR Function that I am trying to create work if I pass in 5 parameters? Does it take the method with the most parameters?

Overlapping Trigger Precedence

Hi,
What is the execution precedence if there exist more than one update, delete
or insert triggers
I mean if there are more than one type of trigger such as more than one
update trigger.
Also, Can we specify a precedence for them?
and last If we call an update statement in the same update trigger and for
the same table that has this trigger, Is the trigger considers itself, I
mean is it recurs or not, or is it disables itself for that execution period
?.
Thanks in advance...
Emre GuldoganThere is no guarantee as to what order they'll fire in.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Emre Guldogan" <ask me please...> wrote in message
news:e$KbhKMJFHA.656@.TK2MSFTNGP14.phx.gbl...
> Hi,
> What is the execution precedence if there exist more than one update,
delete
> or insert triggers
> I mean if there are more than one type of trigger such as more than one
> update trigger.
> Also, Can we specify a precedence for them?
> and last If we call an update statement in the same update trigger and for
> the same table that has this trigger, Is the trigger considers itself, I
> mean is it recurs or not, or is it disables itself for that execution
period
> ?.
> Thanks in advance...
> Emre Guldogan
>|||You can use sp_settriggerorder to define the first and last trigger to
execute. For the rest the order is undefined. If it matters to you then
put all the logic in one trigger.
David Portas
SQL Server MVP
--|||On Wed, 9 Mar 2005 18:11:48 +0200, "Emre Guldogan" <ask me please...>
wrote:

>What is the execution precedence if there exist more than one update, delet
e
>or insert triggers
>I mean if there are more than one type of trigger such as more than one
>update trigger.
>Also, Can we specify a precedence for them?
Hi Emre,
Already addressed by David and Adam.

>and last If we call an update statement in the same update trigger and for
>the same table that has this trigger, Is the trigger considers itself, I
>mean is it recurs or not, or is it disables itself for that execution perio
d
>?.
There are two options governing this:
1. Recursive triggers. If this is off, a trigger can't fire itself. So
if an update trigger on table AA performans an UPDATE AA command, that
specific trigger is not called. This disables only direct recursion: if
the update trigger on AA performs a DELETE FROM BB statement, and the
delete trigger on BB performs UPDATE AA, the trigger will be called
recursively.
The recursive triggers option is a database setting: it will affect all
triggers in the current database, but it won't affect other databases on
the same server. You use ALTER DATABASE to change it.
2. Nested triggers. If this is off, statements executed from a trigger
don't fire any trigger at all.
The nested triggers option is a server setting: it will affect all
triggers in all databases on the server. You use sp_configure to change
it.
Other options to prevent recursive execution of triggers for specific
triggers only are:
* TRIGGER_NESTLEVEL() - check this to find how many nested trigger
executions there currently are.
* TRGIGER_NESTLEVEL(object_ID('trigger_nam
e')) - check this to find how
many times the specified trigger is currently nested.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Overlapping or nonoverlapping publication? Which type applies to my design?

I'm going through the documentation for SQL Server 2005 replication
and am trying to understand what type of publication I should be
using. Here is how replication should be working in my system:
I have a database on a server that will be accessible over the
Internet. This database can be used by multiple companies. Tables are
designed so that a company ID is used to identify records that belong
to a specific company. Every record in every table can be filtered out
to belong to a unique company. That means that no two companies ever
share the same data. Each company can have multiple mobile users using
PDAs to communicate with the server. Each user receives data only from
their company. Some of the data is created on the server and
replicated to the PDA. Some data is also created on the PDA and
replicated to the server. Under no condition is data that is created
on the server ever edited on a PDA. Data created on a PDA is always
inserted into the table and once inserted it can never be modified.
Mobile users cannot modify or delete data. When one mobile user
replicates their data with the server, the newly inserted records that
were created on their PDA are also replicated to all other PDAs within
the same company but this data can never be modified by any mobile
user. In essence, the entire database on the server is replicated to
all of the PDA users but the application on the PDA will only allow
inserts into certain tables - no modifications or deletions. All
mobile users should always get newly inserted data made by other
mobile users within the same company.
I'm having a hard time trying to figure out what kind of publication
is required. In a section of the documentation on replication under
the title "Considerations for Nonoverlapping Partitions", it is
written:
* The Subscriber cannot update columns referenced in a parameterized
filter.
*If an insert at a Subscriber does not belong to the partition, it is
not deleted. However, it will not be replicated to other Subscribers.
I'm not sure what these two statements mean. The first statement
mentions "cannot update columns". No columns will be updated, but
inserts will be done.
In the second statement, it refers to an insert "not belonging to the
partition". If all PDA users are subscribing to the same publication,
won't they all get a copy of the inserted records that are made on all
other PDAs?
If you have a nonoverlapping partion, which is what I assume is what I
should be using, how could any insert possibly belong to some other
partition? Isn't there just one partition?
What kind of publication should I be using: overlapping or
nonoverlapping?
Thanks for your help
Polaris
Hi Hilary,
You wrote:
> With overlapping partitions data can be owned by more than two salesmen,
> with non-overlapping it would be only one.
> From what you describe you should use non-overlapping partitions.
So if you recommend non-overlapping, then are you saying that each PDA
client within a single company would be its own "owner"? Yet I want
it such that all the data on ALL PDAs is identical after replication.
Each user on each PDA can insert data but not modify or delete
existing data. Will non-overlapping satisfy this requirement?
Thanks
Johann
On Feb 25, 3:10 pm, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:
> Basically it means if you are filtering on the salespersonID column for
> example the subscribers will be unable to update this column to have another
> value. Your second question means that if you add a row which has a
> salesmanID value of 5 and your filter for that subscription is 4, this row
> will remain in the subscriber and never move to the publisher.
> The way to look at partitions is that it is a block of related data. So its
> the salesman row, his clients, and all of its orders and all related data.
> Should a salesman take on new clients, the partition will have the new
> clients and their orders added to it. Should the salesman change territories
> with another salesman there would be a partition realignment which would
> include all the new clients, their orders, etc.
> By default if you were to update the client salespersonID on the client
> table, only the client row would move to the subscriber. With join filters
> all the clients data will also move as the update statement on the parent
> table will also affect all child rows.
> With overlapping partitions data can be owned by more than two salesmen,
> with non-overlapping it would be only one.
> From what you describe you should use non-overlapping partitions. You can
> have one partition for every salesman on the publisher, and if you are not
> using hierarchies (or doing republishing) you will only have one partition
> on the subscriber.
> HTH
> --
> Hilary Cotter
|||Perhaps I am missing something here, but I don't think you need to do any
filtering at all, or at least not join filtering which is where partitions
come in.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Polaris431" <polaris431@.gmail.com> wrote in message
news:1172415914.436083.96840@.a75g2000cwd.googlegro ups.com...
> Hi Hilary,
> You wrote:
> So if you recommend non-overlapping, then are you saying that each PDA
> client within a single company would be its own "owner"? Yet I want
> it such that all the data on ALL PDAs is identical after replication.
> Each user on each PDA can insert data but not modify or delete
> existing data. Will non-overlapping satisfy this requirement?
> Thanks
> Johann
>
> On Feb 25, 3:10 pm, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:
>
>

overflow of the data type while processing dimension

While trying to process a cube the error below came up while processing one of the dimensions.

The database is an ORACLE database running on a UNIX platform even though that piece of information would not be neccesary in solving this riddle.

I believe it has to do with date format differences on both database platforms. Is there anyone out there who has been able to resolve such a problem or point me out in the right direction.

See below for an excerpt of the log.

Start time: 30-Mar-07 13:04:47; End time: 30-Mar-07 13:05:02; Duration: 0:00:15
SQL queries 1
SELECT
DISTINCT
"T_0"."DEALER_CODE" "DIMM_SUBSCRIBER_DEALER_XREF0_0"
FROM "DLRPROFILE"."DIMM_SUBSCRIBER_DEALER_XREF" "T_0"
Processing Dimension Attribute 'ENTRY DATE' failed. 1 rows have been read.
Start time: 30-Mar-07 13:04:47; End time: 30-Mar-07 13:06:06; Duration: 0:01:19
SQL queries 1
SELECT
DISTINCT
"T_0"."ENTRY_DATE" "DIMM_SUBSCRIBER_DEALER_XREF0_0"
FROM "DLRPROFILE"."DIMM_SUBSCRIBER_DEALER_XREF" "T_0"
Error Messages 1
Errors in the back-end database access module. OLE DB reported an overflow of the data type for column 0. Errors in the OLAP storage engine: An error occurred while the 'ENTRY DATE' attribute of the 'DIMM SUBSCRIBER DEALER XREF' dimension from the 'DEALER_SALES_CONNECTION_ANALYSIS' database was being processed.
Processing Dimension Attribute 'REGION CODE' completed successfully. 10 rows have been read.
Start time: 30-Mar-07 13:04:47; End time: 30-Mar-07 13:06:06; Duration: 0:01:19

Please another question is there some sort of literature addressing SSAS and ORACLE database working in tandem.

I need Help.......

What is the data type of the column for the 'ENTRY DATE' attribute in Oracle compared with the data type that is setup for the Attribute in the dimension? It sounds like the attribute is using a smaller data type.

overflow error

Can i change from datetime data type to small datetime coz when i tried it produced an overflow error??yup ... use convert(smalldatetime, datecol)