SQL 2005 SSIS Package to load table from positional flat file

5 pts.
Tags:
SQL 2005
SQL tables
SSIS flat file package
SSIS Package
VBScript
I have a positional flat file which I need to load contents into a SQL table. I've created an SSIS package and will need to call a VB script to parse the file but since I'm new at VB and SQL I need some assistance creating the VB script. There are 4 fields in the table I need to populate. The first 2 are just hardcoded values and the other 2 need to be loaded from the file. The first field that needs to be loaded is the first 8 characters of the file and the last field that needs to be loaded is everything else (it could be 5 or 6 characters). File Format: 2008010100001a 2008010200002 So column1 will be hardcoded "field1_data" column2 will be hardcoded "field2_data" column3 will need to be the first 8 characters from the file (in this example 20080101 for the first line and 20080102 for the second line). column4 will need to be the rest of the line in the file (in this example 00001a for the first line and 00002 for the second line). Here is the script I have so far. Any assistance would be great ' Microsoft SQL Server Integration Services user script component ' This is your new script component in Microsoft Visual Basic .NET ' ScriptMain is the entrypoint class for script components Imports System Imports System.Data Imports System.Math Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper Imports Microsoft.SqlServer.Dts.Runtime.Wrapper Public Class ScriptMain Inherits UserComponent Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer) Row.field1 = "field1_data" Row.field2 = "field2_data" Row.field3 = Substring(Row.Inputcolumn(0, 8)) Row.field4 = Substring(Row.Inputcolumn(8, 6)) End Sub Public Overrides Sub CreateNewOutputRows() ' ' Add rows by calling AddRow method on member variable called "<Output Name>Buffer" ' E.g., MyOutputBuffer.AddRow() if your output was named "My Output" ' End Sub End Class

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following