October 22, 2017 at 3:11 pm
Hi , I have got a a task where in I have to extract data to a destination CSV file with a header, Details and a Trailer.
The header has specific values with one of the value being current date,
The details are coming out of my data sources
The Trailer has specific values too, like Record_type , Some_ID, Row_count and Blank_Filed(This is for future use.)
I am using SSIS 2016 to extract the data to CSV file.
I have done extracting the Header and detail rows but , I am not able to figure out is to get the count of the rows coming out of my two sources.
Example: My source 1 gets me 7000 odd records and my source 2 gets me 6000 odd records, I want the Row_count in my Trailer to be the sum of 7000 and 6000 i.e 13000 odd.
Next time when I change the data sources The row count should result depending on the rows coming out of my two sources.
Right now, I have a Data flow having my Header source(a view) and Destination CSV, then pointing to another data flow with two OLE DB sources being merger using Merge transformation and pointing to the same CSV destination, pointing to a third data flow which has a trailer source which is a view again ( I don't want to populate the value under row_count which is hard-coded in this view but instead capture the rows coming out of my sources dynamically and extract it to the line of my trailer in the csv destination file.)
I would appreciate if you could tell me how do i achieve this.Please.As I have no C# knowledge can we do it using any of the other transformations available?
October 23, 2017 at 7:41 am
To at least get you the Row Count, SSIS has traditionally had a Row Count transformation that can provide that information into a package variable of your choice. Having to append your trailer to the file will probably still require a script task, but you don't necessarily need to know C#. You can just as easily use VB, which is usually easier to work with and to understand. Either language, however, will quite likely have a solution for your needs sitting out on the internet behind a Google search. Look for "SSIS Script Task to append a trailer to a CSV file".
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
October 23, 2017 at 1:12 pm
sgmunson - Monday, October 23, 2017 7:41 AMTo at least get you the Row Count, SSIS has traditionally had a Row Count transformation that can provide that information into a package variable of your choice. Having to append your trailer to the file will probably still require a script task, but you don't necessarily need to know C#. You can just as easily use VB, which is usually easier to work with and to understand. Either language, however, will quite likely have a solution for your needs sitting out on the internet behind a Google search. Look for "SSIS Script Task to append a trailer to a CSV file".
Alternatively, write out a second file containing only the trailer info and then use an Execute Process task to concatenate the files (using DOS commands). Have a look here, for example.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 31, 2017 at 1:27 pm
Why not populate a merged temporary table by selecting from the two sources (ie union all). You now have the row count so then dump your header, the temp table and the trailer row to the file.
The probability of survival is inversely proportional to the angle of arrival.
October 31, 2017 at 1:40 pm
sturner - Tuesday, October 31, 2017 1:27 PMWhy not populate a merged temporary table by selecting from the two sources (ie union all). You now have the row count so then dump your header, the temp table and the trailer row to the file.
One reason is that SSIS is not good at outputting from temporary tables due to their, erm, temporaryness.
Another is that the Details section may contain only numbers and dates, in which case shoe-horning text data into this temp table requires that one or more additional columns be created and then that UNION ALL you mentioned starts getting rather ugly.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply