"Richard R" <general@adslleamcres> wrote in message
news:eyFMbq7nDHA.1004@TK2MSFTNGP09.phx.gbl...
> Hi guys,
>
> I've been trying to load in fixed-width text data into my data warehouse,
> but the data occasionally has errors in one field.
> ( It's supposed to be an integer and the OS/400 system that generates the
> file randomly puts in alphabetic characters. This causes the load to fail
as
> the string can't conver to an integer.)
>
> Is it possible to use the multi-phase data pump to put these rows into
> another file, or better still another SQL table?
> It should be, but I'm struggling a bit.
> I can't have two destination connections on my data pump, which presumably
> means I have to define the destination in the ActiveX. Which means, I
think,
> that I have to send the data to a file system object. Can I just write
the
> row to a text file withour worrying about the formatting?
>
> Any help appreciated.
> Especially as I've lent my DTS books to a colleague and can't get them
back
> for a week :-<
>
> Richard R
>
>

"Allan Mitchell" <allan@no-spam.sqldts.com> wrote in message
news:OPHyew7nDHA.1740@TK2MSFTNGP12.phx.gbl...
> Why not do this in 2 phases.
>
> 1. Use the DB2 equivalent of ISNUMERIC() to load the good stuff
> 2. Load the other stuff into wherever you want using a seperate datapump
>

"Richard R" <general@adslleamcres> wrote in message
news:%23SY%23I58nDHA.3504@TK2MSFTNGP11.phx.gbl...
> Hi,
>
> Unfortunately I don't own the OS/400 system, all I get is the text file to
> deal with as best as I can.
>
> I've tried the vb script below on the Transform fail phase of the pump.
> What seems to happen though is that the Create Object line works, but non
of
> the rest do, and the job fails with the same error anyway.
>
> '-------Start------------------
> dim objFSO
> dim objStream
>
> const OUTPUT_FILE = "c:\logfile.txt"
> const fsoForWriting = 2
>
> set objFSO = CreateObject("Scripting.FileSystemObject")
> MsgBox("Createobject" )
>
> set objStream = objFSO.OpenTextFile(OUTPUT_FILE,
> fsoForWriting, true)
> MsgBox("OpenTextfile" )
>
> strRecord = "This is just a test string"
> MsgBox(strRecord )
>
> objStream.WriteLine(strRecord)
> objStream.WriteBlankLines(1)
>
> Main = DTSTaskExecResult_Success
>
> '------------End-------------
>
> "Allan Mitchell" <allan@no-spam.sqldts.com> wrote in message
> news:OPHyew7nDHA.1740@TK2MSFTNGP12.phx.gbl...
> > Why not do this in 2 phases.
> >
> > 1. Use the DB2 equivalent of ISNUMERIC() to load the good stuff
> > 2. Load the other stuff into wherever you want using a seperate
datapump
> >
>
>

"Allan Mitchell" <allan@no-spam.sqldts.com> wrote in message
news:e1ZR2D9nDHA.964@TK2MSFTNGP10.phx.gbl...
> You do not have to own the AS400 system. The SQL Query is in your DTS
> DataPump task
>
>
> Anyway
>
> This works for me
>
>
> --Source Table
> CREATE TABLE IHoldMixedStuff(col1 varchar(10))
> --Destination Table
> CREATE TABLE IHoldOnlyIntegerValues(col1 INT)
> --Data In Source that will blow up for Destination
> INSERT IHoldMixedStuff(col1) VALUES('ABC')
> INSERT IHoldMixedStuff(col1) VALUES('123')
>
>
>
> Function TransFailureMain()
>
>
> dim objFSO
> dim objStream
>
> const OUTPUT_FILE = "c:\logfile.txt"
> const fsoForWriting = 2
>
> set objFSO = CreateObject("Scripting.FileSystemObject")
>
> set objStream = objFSO.OpenTextFile(OUTPUT_FILE, fsoForWriting, true)
>
> strRecord = DTSSource("col1")
>
> objStream.WriteLine(strRecord)
> objStream.WriteBlankLines(1)
>
> objStream.close
>
>
>
> TransFailureMain = DTSTransformstat_OK
> End Function
>
>
> --
>
>
> Allan Mitchell (Microsoft SQL Server MVP)
> MCSE,MCDBA
> www.SQLDTS.com
> I support PASS - the definitive, global community
> for SQL Server professionals - http://www.sqlpass.org
>
>
>
>
> "Richard R" <general@adslleamcres> wrote in message
> news:%23SY%23I58nDHA.3504@TK2MSFTNGP11.phx.gbl...
> > Hi,
> >
> > Unfortunately I don't own the OS/400 system, all I get is the text file
to
> > deal with as best as I can.
> >
> > I've tried the vb script below on the Transform fail phase of the pump.
> > What seems to happen though is that the Create Object line works, but
non
> of
> > the rest do, and the job fails with the same error anyway.
> >
> > '-------Start------------------
> > dim objFSO
> > dim objStream
> >
> > const OUTPUT_FILE = "c:\logfile.txt"
> > const fsoForWriting = 2
> >
> > set objFSO = CreateObject("Scripting.FileSystemObject")
> > MsgBox("Createobject" )
> >
> > set objStream = objFSO.OpenTextFile(OUTPUT_FILE,
> > fsoForWriting, true)
> > MsgBox("OpenTextfile" )
> >
> > strRecord = "This is just a test string"
> > MsgBox(strRecord )
> >
> > objStream.WriteLine(strRecord)
> > objStream.WriteBlankLines(1)
> >
> > Main = DTSTaskExecResult_Success
> >
> > '------------End-------------
> >
> > "Allan Mitchell" <allan@no-spam.sqldts.com> wrote in message
> > news:OPHyew7nDHA.1740@TK2MSFTNGP12.phx.gbl...
> > > Why not do this in 2 phases.
> > >
> > > 1. Use the DB2 equivalent of ISNUMERIC() to load the good stuff
> > > 2. Load the other stuff into wherever you want using a seperate
> datapump
> > >
> >
> >
>
>

"Richard R" <general@adslleamcres> wrote in message
news:e9B7Qa9nDHA.2140@TK2MSFTNGP09.phx.gbl...
> Sorry Allan, it's been a long week and I'm being really thick
>
> Can you explain where in the process I run your inserts?
> My c:\duffdata.txt file is something like:
>
> 1234
> 5678
> 9012
> abc
> 4523
> .. and so on.
>
> Are you suggesting I bring this into a single file that can handle any
> format, and then transform those rows with a SQL query?
>
> Thanks for your help (and patience)
>
> Rich
>
>
>
>
>
> "Allan Mitchell" <allan@no-spam.sqldts.com> wrote in message
> news:e1ZR2D9nDHA.964@TK2MSFTNGP10.phx.gbl...
> > You do not have to own the AS400 system. The SQL Query is in your DTS
> > DataPump task
> >
> >
> > Anyway
> >
> > This works for me
> >
> >
> > --Source Table
> > CREATE TABLE IHoldMixedStuff(col1 varchar(10))
> > --Destination Table
> > CREATE TABLE IHoldOnlyIntegerValues(col1 INT)
> > --Data In Source that will blow up for Destination
> > INSERT IHoldMixedStuff(col1) VALUES('ABC')
> > INSERT IHoldMixedStuff(col1) VALUES('123')
> >
> >
> >
> > Function TransFailureMain()
> >
> >
> > dim objFSO
> > dim objStream
> >
> > const OUTPUT_FILE = "c:\logfile.txt"
> > const fsoForWriting = 2
> >
> > set objFSO = CreateObject("Scripting.FileSystemObject")
> >
> > set objStream = objFSO.OpenTextFile(OUTPUT_FILE, fsoForWriting, true)
> >
> > strRecord = DTSSource("col1")
> >
> > objStream.WriteLine(strRecord)
> > objStream.WriteBlankLines(1)
> >
> > objStream.close
> >
> >
> >
> > TransFailureMain = DTSTransformstat_OK
> > End Function
> >
> >
> > --
> >
> >
> > Allan Mitchell (Microsoft SQL Server MVP)
> > MCSE,MCDBA
> > www.SQLDTS.com
> > I support PASS - the definitive, global community
> > for SQL Server professionals - http://www.sqlpass.org
> >
> >
> >
> >
> > "Richard R" <general@adslleamcres> wrote in message
> > news:%23SY%23I58nDHA.3504@TK2MSFTNGP11.phx.gbl...
> > > Hi,
> > >
> > > Unfortunately I don't own the OS/400 system, all I get is the text
file
> to
> > > deal with as best as I can.
> > >
> > > I've tried the vb script below on the Transform fail phase of the
pump.
> > > What seems to happen though is that the Create Object line works, but
> non
> > of
> > > the rest do, and the job fails with the same error anyway.
> > >
> > > '-------Start------------------
> > > dim objFSO
> > > dim objStream
> > >
> > > const OUTPUT_FILE = "c:\logfile.txt"
> > > const fsoForWriting = 2
> > >
> > > set objFSO = CreateObject("Scripting.FileSystemObject")
> > > MsgBox("Createobject" )
> > >
> > > set objStream = objFSO.OpenTextFile(OUTPUT_FILE,
> > > fsoForWriting, true)
> > > MsgBox("OpenTextfile" )
> > >
> > > strRecord = "This is just a test string"
> > > MsgBox(strRecord )
> > >
> > > objStream.WriteLine(strRecord)
> > > objStream.WriteBlankLines(1)
> > >
> > > Main = DTSTaskExecResult_Success
> > >
> > > '------------End-------------
> > >
> > > "Allan Mitchell" <allan@no-spam.sqldts.com> wrote in message
> > > news:OPHyew7nDHA.1740@TK2MSFTNGP12.phx.gbl...
> > > > Why not do this in 2 phases.
> > > >
> > > > 1. Use the DB2 equivalent of ISNUMERIC() to load the good stuff
> > > > 2. Load the other stuff into wherever you want using a seperate
> > datapump
> > > >
> > >
> > >
> >
> >
>
>

"Allan Mitchell" <allan@no-spam.sqldts.com> wrote in message
news:OoVpzd9nDHA.1740@TK2MSFTNGP12.phx.gbl...
> What I have done here is identify those records (PK) that failed.
>
> What you can then do is up to you but.
>
> 1. Look at those values the following morning
> 2. Mail them to the admin
> 3. You are not going to be able to rework them as they came from the
Source
> and for the sake of the source they are correct
> 4. Why is the destination a different datatype to the destination if they
> need to represent the same thing.
>
>
>
> --
>
>
> Allan Mitchell (Microsoft SQL Server MVP)
> MCSE,MCDBA
> www.SQLDTS.com
> I support PASS - the definitive, global community
> for SQL Server professionals - http://www.sqlpass.org
>
>
>
>
> "Richard R" <general@adslleamcres> wrote in message
> news:e9B7Qa9nDHA.2140@TK2MSFTNGP09.phx.gbl...
> > Sorry Allan, it's been a long week and I'm being really thick
> >
> > Can you explain where in the process I run your inserts?
> > My c:\duffdata.txt file is something like:
> >
> > 1234
> > 5678
> > 9012
> > abc
> > 4523
> > .. and so on.
> >
> > Are you suggesting I bring this into a single file that can handle any
> > format, and then transform those rows with a SQL query?
> >
> > Thanks for your help (and patience)
> >
> > Rich
> >
> >
> >
> >
> >
> > "Allan Mitchell" <allan@no-spam.sqldts.com> wrote in message
> > news:e1ZR2D9nDHA.964@TK2MSFTNGP10.phx.gbl...
> > > You do not have to own the AS400 system. The SQL Query is in your DTS
> > > DataPump task
> > >
> > >
> > > Anyway
> > >
> > > This works for me
> > >
> > >
> > > --Source Table
> > > CREATE TABLE IHoldMixedStuff(col1 varchar(10))
> > > --Destination Table
> > > CREATE TABLE IHoldOnlyIntegerValues(col1 INT)
> > > --Data In Source that will blow up for Destination
> > > INSERT IHoldMixedStuff(col1) VALUES('ABC')
> > > INSERT IHoldMixedStuff(col1) VALUES('123')
> > >
> > >
> > >
> > > Function TransFailureMain()
> > >
> > >
> > > dim objFSO
> > > dim objStream
> > >
> > > const OUTPUT_FILE = "c:\logfile.txt"
> > > const fsoForWriting = 2
> > >
> > > set objFSO = CreateObject("Scripting.FileSystemObject")
> > >
> > > set objStream = objFSO.OpenTextFile(OUTPUT_FILE, fsoForWriting, true)
> > >
> > > strRecord = DTSSource("col1")
> > >
> > > objStream.WriteLine(strRecord)
> > > objStream.WriteBlankLines(1)
> > >
> > > objStream.close
> > >
> > >
> > >
> > > TransFailureMain = DTSTransformstat_OK
> > > End Function
> > >
> > >
> > > --
> > >
> > >
> > > Allan Mitchell (Microsoft SQL Server MVP)
> > > MCSE,MCDBA
> > > www.SQLDTS.com
> > > I support PASS - the definitive, global community
> > > for SQL Server professionals - http://www.sqlpass.org
> > >
> > >
> > >
> > >
> > > "Richard R" <general@adslleamcres> wrote in message
> > > news:%23SY%23I58nDHA.3504@TK2MSFTNGP11.phx.gbl...
> > > > Hi,
> > > >
> > > > Unfortunately I don't own the OS/400 system, all I get is the text
> file
> > to
> > > > deal with as best as I can.
> > > >
> > > > I've tried the vb script below on the Transform fail phase of the
> pump.
> > > > What seems to happen though is that the Create Object line works,
but
> > non
> > > of
> > > > the rest do, and the job fails with the same error anyway.
> > > >
> > > > '-------Start------------------
> > > > dim objFSO
> > > > dim objStream
> > > >
> > > > const OUTPUT_FILE = "c:\logfile.txt"
> > > > const fsoForWriting = 2
> > > >
> > > > set objFSO = CreateObject("Scripting.FileSystemObject")
> > > > MsgBox("Createobject" )
> > > >
> > > > set objStream = objFSO.OpenTextFile(OUTPUT_FILE,
> > > > fsoForWriting, true)
> > > > MsgBox("OpenTextfile" )
> > > >
> > > > strRecord = "This is just a test string"
> > > > MsgBox(strRecord )
> > > >
> > > > objStream.WriteLine(strRecord)
> > > > objStream.WriteBlankLines(1)
> > > >
> > > > Main = DTSTaskExecResult_Success
> > > >
> > > > '------------End-------------
> > > >
> > > > "Allan Mitchell" <allan@no-spam.sqldts.com> wrote in message
> > > > news:OPHyew7nDHA.1740@TK2MSFTNGP12.phx.gbl...
> > > > > Why not do this in 2 phases.
> > > > >
> > > > > 1. Use the DB2 equivalent of ISNUMERIC() to load the good stuff
> > > > > 2. Load the other stuff into wherever you want using a seperate
> > > datapump
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>

"Allan Mitchell" <allan@no-spam.sqldts.com> wrote in message
news:OoVpzd9nDHA.1740@TK2MSFTNGP12.phx.gbl...
> What I have done here is identify those records (PK) that failed.
>
> What you can then do is up to you but.
>
> 1. Look at those values the following morning
> 2. Mail them to the admin
> 3. You are not going to be able to rework them as they came from the
Source
> and for the sake of the source they are correct
> 4. Why is the destination a different datatype to the destination if they
> need to represent the same thing.
>
>
>
> --
>
>
> Allan Mitchell (Microsoft SQL Server MVP)
> MCSE,MCDBA
> www.SQLDTS.com
> I support PASS - the definitive, global community
> for SQL Server professionals - http://www.sqlpass.org
>
>
>
>
> "Richard R" <general@adslleamcres> wrote in message
> news:e9B7Qa9nDHA.2140@TK2MSFTNGP09.phx.gbl...
> > Sorry Allan, it's been a long week and I'm being really thick
> >
> > Can you explain where in the process I run your inserts?
> > My c:\duffdata.txt file is something like:
> >
> > 1234
> > 5678
> > 9012
> > abc
> > 4523
> > .. and so on.
> >
> > Are you suggesting I bring this into a single file that can handle any
> > format, and then transform those rows with a SQL query?
> >
> > Thanks for your help (and patience)
> >
> > Rich
> >
> >
> >
> >
> >
> > "Allan Mitchell" <allan@no-spam.sqldts.com> wrote in message
> > news:e1ZR2D9nDHA.964@TK2MSFTNGP10.phx.gbl...
> > > You do not have to own the AS400 system. The SQL Query is in your DTS
> > > DataPump task
> > >
> > >
> > > Anyway
> > >
> > > This works for me
> > >
> > >
> > > --Source Table
> > > CREATE TABLE IHoldMixedStuff(col1 varchar(10))
> > > --Destination Table
> > > CREATE TABLE IHoldOnlyIntegerValues(col1 INT)
> > > --Data In Source that will blow up for Destination
> > > INSERT IHoldMixedStuff(col1) VALUES('ABC')
> > > INSERT IHoldMixedStuff(col1) VALUES('123')
> > >
> > >
> > >
> > > Function TransFailureMain()
> > >
> > >
> > > dim objFSO
> > > dim objStream
> > >
> > > const OUTPUT_FILE = "c:\logfile.txt"
> > > const fsoForWriting = 2
> > >
> > > set objFSO = CreateObject("Scripting.FileSystemObject")
> > >
> > > set objStream = objFSO.OpenTextFile(OUTPUT_FILE, fsoForWriting, true)
> > >
> > > strRecord = DTSSource("col1")
> > >
> > > objStream.WriteLine(strRecord)
> > > objStream.WriteBlankLines(1)
> > >
> > > objStream.close
> > >
> > >
> > >
> > > TransFailureMain = DTSTransformstat_OK
> > > End Function
> > >
> > >
> > > --
> > >
> > >
> > > Allan Mitchell (Microsoft SQL Server MVP)
> > > MCSE,MCDBA
> > > www.SQLDTS.com
> > > I support PASS - the definitive, global community
> > > for SQL Server professionals - http://www.sqlpass.org
> > >
> > >
> > >
> > >
> > > "Richard R" <general@adslleamcres> wrote in message
> > > news:%23SY%23I58nDHA.3504@TK2MSFTNGP11.phx.gbl...
> > > > Hi,
> > > >
> > > > Unfortunately I don't own the OS/400 system, all I get is the text
> file
> > to
> > > > deal with as best as I can.
> > > >
> > > > I've tried the vb script below on the Transform fail phase of the
> pump.
> > > > What seems to happen though is that the Create Object line works,
but
> > non
> > > of
> > > > the rest do, and the job fails with the same error anyway.
> > > >
> > > > '-------Start------------------
> > > > dim objFSO
> > > > dim objStream
> > > >
> > > > const OUTPUT_FILE = "c:\logfile.txt"
> > > > const fsoForWriting = 2
> > > >
> > > > set objFSO = CreateObject("Scripting.FileSystemObject")
> > > > MsgBox("Createobject" )
> > > >
> > > > set objStream = objFSO.OpenTextFile(OUTPUT_FILE,
> > > > fsoForWriting, true)
> > > > MsgBox("OpenTextfile" )
> > > >
> > > > strRecord = "This is just a test string"
> > > > MsgBox(strRecord )
> > > >
> > > > objStream.WriteLine(strRecord)
> > > > objStream.WriteBlankLines(1)
> > > >
> > > > Main = DTSTaskExecResult_Success
> > > >
> > > > '------------End-------------
> > > >
> > > > "Allan Mitchell" <allan@no-spam.sqldts.com> wrote in message
> > > > news:OPHyew7nDHA.1740@TK2MSFTNGP12.phx.gbl...
> > > > > Why not do this in 2 phases.
> > > > >
> > > > > 1. Use the DB2 equivalent of ISNUMERIC() to load the good stuff
> > > > > 2. Load the other stuff into wherever you want using a seperate
> > > datapump
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>