Padding and Writing to a fixed format flat file!

Hi,

I am trying to write to a fixed format flat file using Flat File Destination Data Flow Component. I have all required information gathered from more than one sources. But when I tried to format the columns to a big string that will make up one line in the flat file, I could not figure out how to do that. Couple of issues that I am facing are:

How to padd different columns? For example, One interger column has could be 1 to 10 character long in my case. When I convert to string, dont know how to padd the remaining characters i.e. if the value of integer is '1234', it should be written to file as '1234 ' . Which transformation is best in this case, if available? How to convert T-SQL datetime to a specific date and time format to write in the flate file? I have to write these date formats depending upon one of the parameters passed. Also, I dont want to put a delimiter at the end of each column, just the new line characters at the end of each record. Some of the columns has some unwanted characters (like new line characters) how to find them and remove them from the string. Can we directly write columns to a specific position in the flat file? e.g. col 1 a position 1 and col2 starts at postion 20 etc.

Your co-operation will be appreciated.

Thanks,

Paraclete

Take a look at a ragged-right or fixed width flat file connection manager. WHen you create the connection manager, you can choose ragged right or fixed width instead of delimited. This should solve a number of your issues.sql

Pad Trailing Spaces in a Report Expression

I am trying to pad a fixed number of trailing spaces into a report expression, as follows:

Data:

CUSTOMER_NAME
-
Michael
Peter
John

Result (e.g. with 10 spaces padded, assuming all names are below 10 characters long):

"Michael "
"Peter "
"John "

Is there an easy way to achieve this ?

Thanks.

Kenny

If you want a 10 character string, right padded with spaces, you could do this:

=Fields!datafield.Value & right(" ", 10 - len(Fields!datafield.Value))

(10 spaces in quotes)

HTH

BobP

Owner of the database after restore

I restored a database SynComp_TEST from a backup using a login testbinu,
which have dbcreator fixed server role. ( SynComp_TEST not exists earlier)
I got below message ..
Processed 1672 pages for database 'SynComp_TEST', file 'Metadata_001' on
file1.
Processed 104 pages for database 'SynComp_TEST', file 'Datasets_001' on file
1.
Processed 8 pages for database 'SynComp_TEST', file 'Indexes_001' on file 1.
Processed 1 pages for database 'SynComp_TEST', file 'Logs_001' on file 1.
Msg 916, Level 14, State 1, Server BOYD, Line 1
Server user 'testbinu' is not a valid user in database 'SynComp_TEST'.
Msg 3013, Level 16, State 1, Server BOYD, Line 1
RESTORE DATABASE is terminating abnormally.
Now I think the owner of the database is 'sa ' ?Am I right ?
Is there any way I can restore this database with testbinu as the owner ?
Or at least testbinu as a valid user in the database?
Hi,
First you have to correct the user testbinu. The error has come because the
login testbinu was not there in the master database syslogins table.
You can rectify the problem using the procedure sp_change_users_login (see
sql server books online on usage)
After builing up the Login/ user chain , you can change the database owner
to testbinu if you need
use <dbname>
go
sp_changedbowner testbinu
Thanks
Hari
MCDBA
"Abraham" <binu_ca@.yahoo.com> wrote in message
news:#34ScB$cEHA.3664@.TK2MSFTNGP12.phx.gbl...
> I restored a database SynComp_TEST from a backup using a login testbinu,
> which have dbcreator fixed server role. ( SynComp_TEST not exists earlier)
> I got below message ..
> Processed 1672 pages for database 'SynComp_TEST', file 'Metadata_001' on
> file1.
> Processed 104 pages for database 'SynComp_TEST', file 'Datasets_001' on
file
> 1.
> Processed 8 pages for database 'SynComp_TEST', file 'Indexes_001' on file
1.
> Processed 1 pages for database 'SynComp_TEST', file 'Logs_001' on file 1.
> Msg 916, Level 14, State 1, Server BOYD, Line 1
> Server user 'testbinu' is not a valid user in database 'SynComp_TEST'.
> Msg 3013, Level 16, State 1, Server BOYD, Line 1
> RESTORE DATABASE is terminating abnormally.
>
> Now I think the owner of the database is 'sa ' ?Am I right ?
> Is there any way I can restore this database with testbinu as the owner ?
> Or at least testbinu as a valid user in the database?
>

Owner of the database after restore

I restored a database SynComp_TEST from a backup using a login testbinu,
which have dbcreator fixed server role. ( SynComp_TEST not exists earlier)
I got below message ..
Processed 1672 pages for database 'SynComp_TEST', file 'Metadata_001' on
file1.
Processed 104 pages for database 'SynComp_TEST', file 'Datasets_001' on file
1.
Processed 8 pages for database 'SynComp_TEST', file 'Indexes_001' on file 1.
Processed 1 pages for database 'SynComp_TEST', file 'Logs_001' on file 1.
Msg 916, Level 14, State 1, Server BOYD, Line 1
Server user 'testbinu' is not a valid user in database 'SynComp_TEST'.
Msg 3013, Level 16, State 1, Server BOYD, Line 1
RESTORE DATABASE is terminating abnormally.
Now I think the owner of the database is 'sa ' ?Am I right ?
Is there any way I can restore this database with testbinu as the owner ?
Or at least testbinu as a valid user in the database?Hi,
First you have to correct the user testbinu. The error has come because the
login testbinu was not there in the master database syslogins table.
You can rectify the problem using the procedure sp_change_users_login (see
sql server books online on usage)
After builing up the Login/ user chain , you can change the database owner
to testbinu if you need
use <dbname>
go
sp_changedbowner testbinu
Thanks
Hari
MCDBA
"Abraham" <binu_ca@.yahoo.com> wrote in message
news:#34ScB$cEHA.3664@.TK2MSFTNGP12.phx.gbl...
> I restored a database SynComp_TEST from a backup using a login testbinu,
> which have dbcreator fixed server role. ( SynComp_TEST not exists earlier)
> I got below message ..
> Processed 1672 pages for database 'SynComp_TEST', file 'Metadata_001' on
> file1.
> Processed 104 pages for database 'SynComp_TEST', file 'Datasets_001' on
file
> 1.
> Processed 8 pages for database 'SynComp_TEST', file 'Indexes_001' on file
1.
> Processed 1 pages for database 'SynComp_TEST', file 'Logs_001' on file 1.
> Msg 916, Level 14, State 1, Server BOYD, Line 1
> Server user 'testbinu' is not a valid user in database 'SynComp_TEST'.
> Msg 3013, Level 16, State 1, Server BOYD, Line 1
> RESTORE DATABASE is terminating abnormally.
>
> Now I think the owner of the database is 'sa ' ?Am I right ?
> Is there any way I can restore this database with testbinu as the owner ?
> Or at least testbinu as a valid user in the database?
>|||Follow the steps that Hari outlined for you to change the
owner.
To check a db owner, you can execute:
sp_helpdb
-Sue
On Tue, 27 Jul 2004 11:40:11 -0400, "Abraham"
<binu_ca@.yahoo.com> wrote:

>I restored a database SynComp_TEST from a backup using a login testbinu,
>which have dbcreator fixed server role. ( SynComp_TEST not exists earlier)
>I got below message ..
>Processed 1672 pages for database 'SynComp_TEST', file 'Metadata_001' on
>file1.
>Processed 104 pages for database 'SynComp_TEST', file 'Datasets_001' on fil
e
>1.
>Processed 8 pages for database 'SynComp_TEST', file 'Indexes_001' on file 1
.
>Processed 1 pages for database 'SynComp_TEST', file 'Logs_001' on file 1.
>Msg 916, Level 14, State 1, Server BOYD, Line 1
>Server user 'testbinu' is not a valid user in database 'SynComp_TEST'.
>Msg 3013, Level 16, State 1, Server BOYD, Line 1
>RESTORE DATABASE is terminating abnormally.
>
>Now I think the owner of the database is 'sa ' ?Am I right ?
>Is there any way I can restore this database with testbinu as the owner ?
>Or at least testbinu as a valid user in the database?
>

Owner of the database after restore

I restored a database SynComp_TEST from a backup using a login testbinu,
which have dbcreator fixed server role. ( SynComp_TEST not exists earlier)
I got below message ..
Processed 1672 pages for database 'SynComp_TEST', file 'Metadata_001' on
file1.
Processed 104 pages for database 'SynComp_TEST', file 'Datasets_001' on file
1.
Processed 8 pages for database 'SynComp_TEST', file 'Indexes_001' on file 1.
Processed 1 pages for database 'SynComp_TEST', file 'Logs_001' on file 1.
Msg 916, Level 14, State 1, Server BOYD, Line 1
Server user 'testbinu' is not a valid user in database 'SynComp_TEST'.
Msg 3013, Level 16, State 1, Server BOYD, Line 1
RESTORE DATABASE is terminating abnormally.
Now I think the owner of the database is 'sa ' ?Am I right ?
Is there any way I can restore this database with testbinu as the owner ?
Or at least testbinu as a valid user in the database?Hi,
First you have to correct the user testbinu. The error has come because the
login testbinu was not there in the master database syslogins table.
You can rectify the problem using the procedure sp_change_users_login (see
sql server books online on usage)
After builing up the Login/ user chain , you can change the database owner
to testbinu if you need
use <dbname>
go
sp_changedbowner testbinu
Thanks
Hari
MCDBA
"Abraham" <binu_ca@.yahoo.com> wrote in message
news:#34ScB$cEHA.3664@.TK2MSFTNGP12.phx.gbl...
> I restored a database SynComp_TEST from a backup using a login testbinu,
> which have dbcreator fixed server role. ( SynComp_TEST not exists earlier)
> I got below message ..
> Processed 1672 pages for database 'SynComp_TEST', file 'Metadata_001' on
> file1.
> Processed 104 pages for database 'SynComp_TEST', file 'Datasets_001' on
file
> 1.
> Processed 8 pages for database 'SynComp_TEST', file 'Indexes_001' on file
1.
> Processed 1 pages for database 'SynComp_TEST', file 'Logs_001' on file 1.
> Msg 916, Level 14, State 1, Server BOYD, Line 1
> Server user 'testbinu' is not a valid user in database 'SynComp_TEST'.
> Msg 3013, Level 16, State 1, Server BOYD, Line 1
> RESTORE DATABASE is terminating abnormally.
>
> Now I think the owner of the database is 'sa ' ?Am I right ?
> Is there any way I can restore this database with testbinu as the owner ?
> Or at least testbinu as a valid user in the database?
>

Owner of the database after restore

I restored a database SynComp_TEST from a backup using a login testbinu,
which have dbcreator fixed server role. ( SynComp_TEST not exists earlier)
I got below message ..
Processed 1672 pages for database 'SynComp_TEST', file 'Metadata_001' on
file1.
Processed 104 pages for database 'SynComp_TEST', file 'Datasets_001' on file
1.
Processed 8 pages for database 'SynComp_TEST', file 'Indexes_001' on file 1.
Processed 1 pages for database 'SynComp_TEST', file 'Logs_001' on file 1.
Msg 916, Level 14, State 1, Server BOYD, Line 1
Server user 'testbinu' is not a valid user in database 'SynComp_TEST'.
Msg 3013, Level 16, State 1, Server BOYD, Line 1
RESTORE DATABASE is terminating abnormally.
Now I think the owner of the database is 'sa ' ?Am I right ?
Is there any way I can restore this database with testbinu as the owner ?
Or at least testbinu as a valid user in the database?Hi,
First you have to correct the user testbinu. The error has come because the
login testbinu was not there in the master database syslogins table.
You can rectify the problem using the procedure sp_change_users_login (see
sql server books online on usage)
After builing up the Login/ user chain , you can change the database owner
to testbinu if you need
use <dbname>
go
sp_changedbowner testbinu
Thanks
Hari
MCDBA
"Abraham" <binu_ca@.yahoo.com> wrote in message
news:#34ScB$cEHA.3664@.TK2MSFTNGP12.phx.gbl...
> I restored a database SynComp_TEST from a backup using a login testbinu,
> which have dbcreator fixed server role. ( SynComp_TEST not exists earlier)
> I got below message ..
> Processed 1672 pages for database 'SynComp_TEST', file 'Metadata_001' on
> file1.
> Processed 104 pages for database 'SynComp_TEST', file 'Datasets_001' on
file
> 1.
> Processed 8 pages for database 'SynComp_TEST', file 'Indexes_001' on file
1.
> Processed 1 pages for database 'SynComp_TEST', file 'Logs_001' on file 1.
> Msg 916, Level 14, State 1, Server BOYD, Line 1
> Server user 'testbinu' is not a valid user in database 'SynComp_TEST'.
> Msg 3013, Level 16, State 1, Server BOYD, Line 1
> RESTORE DATABASE is terminating abnormally.
>
> Now I think the owner of the database is 'sa ' ?Am I right ?
> Is there any way I can restore this database with testbinu as the owner ?
> Or at least testbinu as a valid user in the database?
>