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).
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
Public Class ScriptMain
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))
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"
Free Guide: Managing storage for virtual environments
Complete a brief survey to get a complimentary 70-page whitepaper featuring the best methods and solutions for your virtual environment, as well as hypervisor-specific management advice from TechTarget experts. Don’t miss out on this exclusive content!