March 6, 2019 at 9:38 am
We have a package that loads data from TAB delimited files, and it works just fine.
Now a new client is sending files that are PIPE delimited.
What I would like to be able to do (IF I KNEW C#) is to create a script task in a data flow that can read the first line of the file, find the delimiter, write out the delimiter type to a variable and use a conditional split to process the file with the correct CM.
Can someone help me with that C# or if you have a different suggestion.
Thanks.
March 6, 2019 at 12:09 pm
You can't create a script task in a data flow. You can create a script component. But if you're using a flat file source, the script component would be downstream from the source and can't control it.
You could use a script task in the main control flow, and use it to open the file, determine the delimiter, and set a package variable. Then create two nearly-identical data flows, and use expressions on the precedence arrows to determine whether the TAB dataflow or the PIPE dataflow is executed after the script task.
Or you could learn some C# and create a script component to replace the flat file data source. There are thousands of examples on the internet, this one looked pretty good: http://radacad.com/script-component-as-source-ssis
1. Add a script component to your data flow. Configure it as a source.
2. Define the output for the script component, same as you would for the flat file source. And all the columns.
3. Define variables at the class level for a streamreader and a character (for the delimiter).
4. In the PreExecute method, open the file (get the filename from the connection manager), read one line and figure out the delimiter.
Save the delimiter in the class variable, close the file and reopen it.
5. In the CreateNewOutputRows method, read a line with ReadLine(), split the line using String.Split() and the delimiter character, add a row to the output buffer and copy the values from the string array to the output buffer columns. Repeat until end of file.
6. In the PostExecute method, close the file and dispose the StringReader.
This may sound like a lot of code, but given all the examples on the internet you should be able to figure it out.
For more powerful string splitting, handling embedded quotes etc, instead of string.split you can use the Microsoft.VisualBasic.FileIO.TextFieldParser object. This is available in C# as well as VB, but requires adding a project reference.
March 6, 2019 at 12:58 pm
I said script task when I meant script component.
I love your idea of precedence expression constraints to control the flow.
I do have the 2 nearly identical data flows.
VB is slightly different. Still searching for assistance in C# to read in @path and write out the @delimiter variable.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply