Wednesday, January 09, 2013 1:58 PM
I am attempting to pull a extract from our server, SQL Server 2008 R2. In a normal extract I would use SSIS to a delimited Text File, however this extract is looking for something a litte bit more complex. It needs to have a delimiter at the end of a record(Line Feed), one after each field (Comma), and then one after each value in that field (Tab).
An example of how this would look would be:
ID, Address Line 1 Address Line 2 Address Line 3, City, State, Zip, Address Type, Phone Num 1 Phone Number 2 ...., Phone Number Type 1 Phone Number Type 2 ...\LF
For the multiple Address line that is a simple concatenation from the db, but the phone numbers are providing some issues. Each ID could have multiple numbers, in our db as separate records. For Example:
ID Phone Number Phone Number Type
12 555-555-1234 Home
12 555-555-1235 Cell
Any ideas on how i would work through the issue of trying to make this work?
Any help would be much appreciated.
Wednesday, January 09, 2013 2:07 PM
I can think of 2 alternatives :
1. Either importing from comma-delimited text into a staging table, where address and phone fields will be strings with none, one or more Tabs in the middle.
Then, you'll have to import from the staging table into the final table taking care of the Tabs.
2. Or, write a VB/VC# script to import straight from the text file into the final table, and do the line parsing (splitting commas and tabs) yourself
Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu
Wednesday, January 09, 2013 2:26 PMThank you for the prompt reply. Unfortunately, this isn't for me to import the data. I need to pull this information from our server, to send it to someone else. One the other end this is how they want to receive the data from us. Do you have any ideas?
- Edited by mjrobison Wednesday, January 09, 2013 2:44 PM
Wednesday, January 09, 2013 2:46 PM
for getting one long row from many short, try to look for pivot transformation.
For export to special formatted flat file, you have to create your own destination. It can be done by Script transformation in DFT used as destination. But it needs coding skills.
I used similar solution for migration and it worked.
Please mark as helpful and propose as answer if you find this as correct!!! nosekz.eu
Wednesday, January 09, 2013 4:51 PM
If you are writing a SSIS package then you can pull all the data into stage table with main conditions.
Then have to massage the data to see if there is multiple values like for phone number - then re-write the data as TAB delimited.
In the next step do this task - i.e comma demiter and line feed ending you can done.
But could you tell me what is the exact error you are getting?