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
Steve Hogg

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