how to treat truncations as a warning

I'm using SSIS to migrate data from one system to another. This is a usual extract, transform, cleanse and load type task.

The error handling is critical to get right. E.g. truncation of data on one column should stop that row being loaded but for other columns I might be happy to carry on loading the row but record a warning.

I'm finding the error disposition a bit limiting. I really feel the need for an 'Issue Warning' disposition which will act the same way as 'Ignore Error' in that the row continues being processed but will in addition copy a row to a warning output so that I can write a message to a log file for someone to manually investigate and correct that item of data post the conversion. Alternatively it would be useful to specify a severity (at a column level) when redirecting error output. This way I can put logic into a downstream component which would treat the error row differently depending on the severity of the error.

Am I missing a trick?

There's no built-in switch for enabling this behavior, but you can accomplish it with creative use of error redirection. I use error redirection or a conditional split to identify rows that either cause warnings or errors, flag them appropriately, then send the errors to a logging table. I send the warnings to a multicast that outputs the rows to both a logging table and to a Union All to put them back into the main flow.|||this will work for me, though it's a shame there isn't a built-in feature...|||

Nick Corrie wrote:

this will work for me, though it's a shame there isn't a built-in feature...

Nick,

That could be a good suggestion to make; you can post it at the connect site: http://connect.microsoft.com/VisualStudio/Feedback

How to transform text in number?

How to transform text in numberHello Slim !
Try this:
Select Convert(INT, @.Textstring)
Jens Süßmeyer.|||Explaining better:
to convert ' 2.00 ' for 2,00
"Jens S meyer" <jsuessmeyer@.[REJECT_SPAM]web.de> wrote in message
news:ON2kpboZDHA.1280@.tk2msftngp13.phx.gbl...
> Hello Slim !
> Try this:
> Select Convert(INT, @.Textstring)
> Jens S meyer.
>|||Hello Slim !
Here some coding example: Try this:
DECLARE @.INTSTRING varchar(100)
SET @.INTSTRING = '2,001'
Select Replace(@.INTString,',','.')
Implicit convertion will be used, if you try to calculate with this value,
explicit convertion could be used by you:
Select
Convert(ANY_NUMERIC_DATATYPE_CHOOSEN_BY_YOU,Replace(@.INTString,',','.'))
Jens Süßmeyer.|||I am trying to matter and I am receiving that message
"Jens S meyer" <jsuessmeyer@.[REJECT_SPAM]web.de> wrote in message
news:e3hHKroZDHA.1744@.TK2MSFTNGP12.phx.gbl...
> Hello Slim !
> Here some coding example: Try this:
> DECLARE @.INTSTRING varchar(100)
> SET @.INTSTRING = '2,001'
> Select Replace(@.INTString,',','.')
> Implicit convertion will be used, if you try to calculate with this value,
> explicit convertion could be used by you:
> Select
> Convert(ANY_NUMERIC_DATATYPE_CHOOSEN_BY_YOU,Replace(@.INTString,',','.'))
> Jens S meyer.
>|||I am using SQL in Brazil, as I do to substitute (.) point for (,) comma
DECLARE @.INTSTRING char(100)
SET @.INTSTRING = '0.50'
--Select Replace(@.INTString,'.',',')
Select convert(decimal(9,2),@.INTSTRING)
"Jens S meyer" <jsuessmeyer@.[REJECT_SPAM]web.de> wrote in message
news:u5AJAIpZDHA.2136@.TK2MSFTNGP10.phx.gbl...
> OK, so then you need the other way, i am using a GERMAN SQL Server with ,
> separated numerics, so you have to replace the . with the , and vice versa
> in the replace function.
> Jens S meyer.
> "Slim" <fslim@.bol.com.br> schrieb im Newsbeitrag
> news:eOoHHEpZDHA.3768@.tk2msftngp13.phx.gbl...
> > Trying to convert received the message:
> >
> > DECLARE @.INTSTRING char(100)
> > SET @.INTSTRING = '2,001'
> > --Select Replace(@.INTString,'.',',')
> >
> > Select convert(decimal(10,5),@.INTSTRING)
> >
> > Server: Msg 8114, Level 16, State 5, Line 4
> > Error converting data type varchar to numeric.
> >
> >
> > "Jens S meyer" <jsuessmeyer@.[REJECT_SPAM]web.de> wrote in message
> > news:uPqtS$oZDHA.3436@.tk2msftngp13.phx.gbl...
> > > Try to transform it the way you will need it in the database (with
> > convert).
> > > If the target database needs decimal(10,5), convert it to
> > > convert(decimal(10,5),Column).
> > >
> > > Gru, Jens.
> > >
> > >
> > >
> >
> >
>sql

How to transform fact table only by SQL?

Hi,
this is easy with OLAP tools, but I need to do it just with MS-SQL
server:

fatTable
yeartypeval
97a1
97b2
97c3
98a4
98b5
98c6
...

yeartype_atype_btype_c
97123
98456
99...

The problem is number of different types - not just 3 like a,b,c but
more than 100, so I don't want to do it manually like

select
year, a.val, b.val, c.val
from
(select year, val from factTable where type='a') a
full join (select year, val from factTable where type='b') b
on a.year = b.year
full join (select year, val from factTable where type='c') c
on a.year = c.year

is it possible somehow with DTS or otherwise? I just need to present
the data in spreadsheet in more readable form, but I cannot find any
way how to export the result from MS-SQLserverOLAPservices to Excel...

Martinhttp://www.winnetmag.com/SQLServer/...5608/15608.html
http://www.sqlteam.com/item.asp?ItemID=2955

--
David Portas
SQL Server MVP
--|||mrazek@.compik.fd.cvut.cz (Martin Mrazek) wrote in message news:<dcd39e84.0405271815.13dd8e97@.posting.google.com>...
> Hi,
> this is easy with OLAP tools, but I need to do it just with MS-SQL
> server:
> fatTable
> yeartypeval
> 97a1
> 97b2
> 97c3
> 98a4
> 98b5
> 98c6
> ...
> yeartype_atype_btype_c
> 97123
> 98456
> 99...
> The problem is number of different types - not just 3 like a,b,c but
> more than 100, so I don't want to do it manually like
> select
> year, a.val, b.val, c.val
> from
> (select year, val from factTable where type='a') a
> full join (select year, val from factTable where type='b') b
> on a.year = b.year
> full join (select year, val from factTable where type='c') c
> on a.year = c.year
> is it possible somehow with DTS or otherwise? I just need to present
> the data in spreadsheet in more readable form, but I cannot find any
> way how to export the result from MS-SQLserverOLAPservices to Excel...
> Martin

It sounds like you're looking for a dynamic crosstab query:

http://www.aspfaq.com/show.asp?id=2462
http://www.winnetmag.com/SQLServer/...5608/15608.html

You might find it easier to build the query string in a client
program, rather than in pure TSQL.

Simon

how to transform an .MDF database into a permanent database in sql server express?

Hi, i have a MDF file used by an asp.net application (with sql server 2005 express).

I want to make it to a permanent database in sql server express.

I know that i can attach it from Management Studio, but is there no better way?

Does it exist a way to 'import' or 'migrate' or copy' a .MDF.database into the central sql server, as if created directly in sql server express? After that, i can delete the .MDF file.

Thanks

Tartuffe

Hello,

if you would create a new database with SQL Server, it will also create an .mdf-file. You can see this for yourself in the following location:

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data

So, I'm not sure what your problem is with the mdf files, but they are perfectly normal SQL Database files and attaching is therefore a good way to bring your database online. If you want, you can first also create a brand new database first, and then import the date. In Management Studio you can right click the new database in the Object Explorer, select tasks > import data... and it should start the SQL Server Import and Export Wizard. Since it's a wizard, I think you will be able to perform the task as you see fit.

Good luck!

|||

Hi, thanks for replying.

Unfortunately, i have Management Studio express and when rightclicking on the new created db, i can't see Import, only backup/restore shrink ...

|||

Hi tartuffe2,

I my opinion, using attach database is the best way for this scenario.

SQL Server 2005 has many ways to implement move, copy, and update database, such as detach/attach, backup/restore, import/export, database mirroring, log shipping, replication. But some of them do not support in Express Edition. For your request, attach is the fastest and simplest way.

Thanks.

How to transform "full width" String into "half width" String?

Hi, everyone

There is a table t1 with two fields, such as,
ID NAME
１　　　Ｔｏｋｙｏ
2 Xian
3 America

For there are full-width and half-width strings in the values of the two fields, I can not select and get the right records. So I want to transform the two fields ID and NAME, I fail to find the function in the SQL Server 2005. Please give me some advice.

Thank you very much!try trimming all the blank spaces in the fields before running any select/ use the fields with Trim() running on them.|||thank you, wash.

The function trim only trims the blank spaces in the field.However, field values with full width or half width do not mean there are blank spces in the field.|||I have no idea what you are talking about

what are full/hald width strings?|||I think I know what he wants.

If all the data consists of single words, you can eliminate all the spaces using the REPLACE function:select replace('t o k y o ', ' ', '')
...but if some of the records contain multiple words, you are out of luck.
How did your data end up looking like this in the first place?|||How about this:

select substring (name, 1, length(name)/2)

Truncates half the string, every time. May not be what you want, but...|||Do these guys even get emails when we reply?

Half?

Ever hear of RI?|||sorry, Brett Kaiser , I did not get your email.|||thanks all.

I still failed in the problems with full width and half width. If possible, please create a table, and insert some records with full-width and half-width string, you will find it is difficult to cope with the problem.|||here's a variation of what blindman suggested that will work for your funny-spaced words as long as they are separated by two spaces between words:

select replace(replace(replace('t o k y o n i g h t s', ' ', '__ACK_ACK__'),' ',''),'__ACK_ACK__',' ')

You can replace the __ACK_ACK__ with any string you like as long as it's guaranteed never to appear in the strings you are operating on. I figure __ACK_ACK__ is pretty unlikely to appear since it's Martian. :)|||no problem will have a solution untill you define your problem statement clearly. :S