Scope parameter: types

Showing posts with label types. Show all posts

Monday, March 26, 2012

Script to populate for a large DB

can someone help me provide a script that will populate around 50GB of data
? Ideally it would be multiple tables with some various data types...if I
could add more data to Northwind or pubs or even AdventureWorks, that would
be fine..
ThanksOn May 6, 10:43 am, "Hassan" <has...@.hotmail.com> wrote:
> can someone help me provide a script that will populate around 50GB of data
> ? Ideally it would be multiple tables with some various data types...if I
> could add more data to Northwind or pubs or even AdventureWorks, that would
> be fine..
> Thanks
BCP and Bulk Insert are good options for populating large amounts of
data quickly. This link might be helpful.
http://msdn2.microsoft.com/en-us/library/ms175915.aspx
Also, you could consider using SSIS (Integration Services), if you are
using SQL Server 2005. It has some pretty useful ETL/transformation
options that should be helpful.
http://msdn2.microsoft.com/en-us/library/ms167031.aspx
http://www.tutorialized.com/tutorial/Your-First-Script-Component-in-SQL-Server-2005-Integration-Services/13640
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||I am aware of these tools, but I wanted scripts where I could just run and
it would start populating..
"EMartinez" <emartinez.pr1@.gmail.com> wrote in message
news:1178481390.533564.160560@.y5g2000hsa.googlegroups.com...
> On May 6, 10:43 am, "Hassan" <has...@.hotmail.com> wrote:
>> can someone help me provide a script that will populate around 50GB of
>> data
>> ? Ideally it would be multiple tables with some various data types...if I
>> could add more data to Northwind or pubs or even AdventureWorks, that
>> would
>> be fine..
>> Thanks
>
> BCP and Bulk Insert are good options for populating large amounts of
> data quickly. This link might be helpful.
> http://msdn2.microsoft.com/en-us/library/ms175915.aspx
> Also, you could consider using SSIS (Integration Services), if you are
> using SQL Server 2005. It has some pretty useful ETL/transformation
> options that should be helpful.
> http://msdn2.microsoft.com/en-us/library/ms167031.aspx
> http://www.tutorialized.com/tutorial/Your-First-Script-Component-in-SQL-Server-2005-Integration-Services/13640
> Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>|||Hassan
TestBase www.tenerus.com
TESTBytes www.cai.com
"Hassan" <hassan@.hotmail.com> wrote in message
news:uURKzgDkHHA.3484@.TK2MSFTNGP02.phx.gbl...
>I am aware of these tools, but I wanted scripts where I could just run and
>it would start populating..
> "EMartinez" <emartinez.pr1@.gmail.com> wrote in message
> news:1178481390.533564.160560@.y5g2000hsa.googlegroups.com...
>> On May 6, 10:43 am, "Hassan" <has...@.hotmail.com> wrote:
>> can someone help me provide a script that will populate around 50GB of
>> data
>> ? Ideally it would be multiple tables with some various data types...if
>> I
>> could add more data to Northwind or pubs or even AdventureWorks, that
>> would
>> be fine..
>> Thanks
>>
>> BCP and Bulk Insert are good options for populating large amounts of
>> data quickly. This link might be helpful.
>> http://msdn2.microsoft.com/en-us/library/ms175915.aspx
>> Also, you could consider using SSIS (Integration Services), if you are
>> using SQL Server 2005. It has some pretty useful ETL/transformation
>> options that should be helpful.
>> http://msdn2.microsoft.com/en-us/library/ms167031.aspx
>> http://www.tutorialized.com/tutorial/Your-First-Script-Component-in-SQL-Server-2005-Integration-Services/13640
>> Hope this helps.
>> Regards,
>> Enrique Martinez
>> Sr. Software Consultant
>

Script to populate for a large DB

Tuesday, March 20, 2012

Script Source: Error on truncation etc

I have a script source to deal with a source that has different "record types" (first 3 columns are the same then the remaining 2 to 30 columns are different based on the record type).

Script source was working fine... then one of the columns that I had set to String with length of 2 came in with a length of 3 (which is not per spec)... instead of failing - all the columns after the one that had the bad value were null and the script just stopped as soon as it hit that.. AND said it was success. Which means it imported the data incorrectly and since the script says it was a sucess you'd never know anything went wrong and it only imported 30 rows instead of 10k+

Any ideas on how to capture this error?

Code (shortened with .... but should be enough - sorry the forum butchers the code formatting - if someone has a tip for pasting code from VS let me know):

Imports System

Imports System.Data

Imports System.Math

Imports System.IO

Imports System.Convert

Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper

Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Public Class ScriptMain

Inherits UserComponent

Public Overrides Sub CreateNewOutputRows()

Dim oCurrentFile As File

Dim oStreamReader As StreamReader

Dim sCurrentLine As String

Dim aCurrentLine() As String

Try

oStreamReader = oCurrentFile.OpenText(Me.Variables.SourceName)

sCurrentLine = oStreamReader.ReadLine()

Do While sCurrentLine IsNot Nothing

aCurrentLine = sCurrentLine.Split(Chr(44))

Select Case aCurrentLine(2) ' This is the 3rd column.. "Record Type" which tells us what type of record it is and how many columns etc (Chr(44) is a comma)

Case "BF"

BFRecordsBuffer.AddRow()

BFRecordsBuffer.TimeStamp = aCurrentLine(0)

BFRecordsBuffer.EyeCatcher = aCurrentLine(1)

BFRecordsBuffer.RecordType = aCurrentLine(2)

BFRecordsBuffer.Sym = aCurrentLine(3)

....

BFRecordsBuffer.RecordCount = Convert.ToInt32(aCurrentLine(24))

...

If aCurrentLine.GetLength(0) >= 30 Then

BFRecordsBuffer.SeqNo = aCurrentLine(29)

End If

Case "QF"

QFRecordsBuffer.AddRow()

QFRecordsBuffer.Timestamp = aCurrentLine(0)

QFRecordsBuffer.EyeCatcher = aCurrentLine(1)

QFRecordsBuffer.RecordType = aCurrentLine(2)

...

....

End Select

sCurrentLine = oStreamReader.ReadLine()

Loop

BFRecordsBuffer.SetEndOfRowset()

QFRecordsBuffer.SetEndOfRowset()

....

Catch ex As Exception

Me.ComponentMetaData.FireError(1, "Script Component", ex.Message, "", 0, True)

Finally

oStreamReader.Close()

End Try

End Sub

End Class

Chris Honcoop wrote:

Code (shortened with .... but should be enough - sorry the forum butchers the code formatting - if someone has a tip for pasting code from VS let me know):

I can help with that. Go here: http://www.jtleigh.com/people/colin/software/CopySourceAsHtml/

Yuo can see an example of the result here:

http://blogs.conchango.com/jamiethomson/archive/2006/12/19/SSIS-Nugget_3A00_-Extract-last-word-from-a-sentance-using-regular-expressions.aspx

-Jamie

|||

Thanks Jamie, I installed it and don't see it as an option under add-ons.. nor where it mentions it will be in the menus - am I missing something? In re: to your blog welcome to the wonderful world of regex ;)

Any ideas on the script source truncation/data type mismatch "non error"?

|||

Chris Honcoop wrote:

Thanks Jamie, I installed it and don't see it as an option under add-ons.. nor where it mentions it will be in the menus - am I missing something? In re: to your blog welcome to the wonderful world of regex ;)
Any ideas on the script source truncation/data type mismatch "non error"?

It doesn't work in VSA unfortunately so copy and paste it into a regular VB.Net project and copy as html from there.

Not sure about the error you're getting i'm afraid.

-Jamie

|||Its something with the way I do the error trapping.. I comment out the try / catch etc and the script source will fail when the columns are bad. Of course getting a useful error message about which column etc is experiencing the problem is an entirely different headache.|||To have the standard SSIS options of ignore,redirect, and fail

component in your own custom script component, there are a couple of

changes to consider.

1. Add a try/catch inside your loop. As written, when the first error

happens (e.g. "ABC" rather than "AB"), the exception will be caught

outside of the loop, and no rows beyond that will be processed. This

change will allow the implementation of the ignore and/or redirect

options, and of course, continued processing.

2. In the newly added try/catch, add an catch for the

DoesNotFitBufferException. This will "catch" the error. You can also simply ignore this particular

exception type, and the string will be truncated, or re-throw the

exception, and the component will fail, or lastly, redirect it (

essentially adding a row to an "error" output on your component)

3. To report which column name/index is experiencing the problem will

require a cache of column information (names, types,lengths, etc) by

overriding the PreExecute function, and then grabbing a reference to

the the base buffers by overriding the PrimeOutput function. The

column cache and base buffers can then be referenced in your

CreateNewOutputRows implementation.

Hopefully this will help you along your way. If not, I'll find some script component code and post that as well.

Monday, March 26, 2012

Script to populate for a large DB

Script to populate for a large DB

Script to populate for a large DB

Tuesday, March 20, 2012

Script Source: Error on truncation etc

Scope parameter

Blog Archive

About Me