troubleshooting Question

How do I Assign Variables in a Data Flow from a Source Text file in SSIS SQL 2005?

Avatar of KITS_Paddington
KITS_Paddington asked on
Microsoft SQL ServerMicrosoft SQL Server 2005
6 Comments1 Solution462 ViewsLast Modified:
I have a source text file which I need to import into a SQL table (sample text attached  NB in the actual import there are over 1million rows with 100.000 occurrences of '01').  Each time the row starts with '01' this represents a new record.  I need to be able to identify the subsequent rows by adding the reference from this row.  We did this in SQL 2000 by using the code below in a transformation.  

In the data flow, I have separated the first two characters of each row as Column00 and the rest of the line is column01.  

Each time the row starts with '01'  I need to set the variable to equal the text as a position 21-30 of that row.  I would then use the variable to map to a column in the destination file.  The variable would change each time  row beginning '01' is encountered.

I am thinking at the moment that a for loop container might be the way to do it, but have not worked out how to set variables from the data source in it yet.
'**********************************************************************
'  Visual Basic Transformation Script
'************************************************************************
 
'  Copy each source column to the destination column
Function Main()
	If DTSSource("Col001") = "01" Then	
		DTSGlobalVariables("Reference").Value = Mid(DTSSource("Col002"),21,10)
	End If
		DTSDestination("Reference") = DTSGlobalVariables("Reference").Value
	Main = DTSTransformStat_OK
End Function
VariableExample.txt
ASKER CERTIFIED SOLUTION
Steve Hogg
IT

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 6 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros