November 30, 2017 at 8:00 am
Hi Team,
I'm trying to load history of TFS script deployed to database.
The output of history is as below, how can I get this on SQL in table format.
Thanks in advance
Need Output Table
| Changeset: 1234 | User: XXXX | Date: Monday, April 15, 2016 1:36:11 PM | Branched from $/Project1/Prod | branch $/Project1/Product/Databases/DB/View/view1.sql |
| Changeset: 2345 | User: XXXX | Date: Monday, April 15, 2016 1:36:11 PM | Branched from $/Project1/Prod | branch $/Project1/Product/Databases/DB3/Table/Table2.sql |
TFS history file:
-----------------------------------------------------------------------------------------------------------------------
Changeset: 1234
User: XXXX
Date: Monday, April 15, 2016 1:36:11 PM
Comment:
Branched from $/Project1/Prod
Items:
branch $/Project1/Product/Databases/DB/Views/view1.sql
-----------------------------------------------------------------------------------------------------------------------
Changeset: 1234
User: XXXX
Date: Monday, April 15, 2016 1:36:11 PM
Comment:
Branched from $/Project1/Prod
Items:
branch $/Project1/Product/Databases/DB3/Tables/Table2.sql
November 30, 2017 at 8:07 am
Is that output table a single column...? I'd suggest it would be better to have each item as a column. Why do you want to convert it into Delimited Tab format on your SQL Server where each item hasit's header prior to it? That's going to be awful to query.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
November 30, 2017 at 8:08 am
Tsql-561486 - Thursday, November 30, 2017 8:00 AMHi Team,I'm trying to load history of TFS script deployed to database.
The output of history is as below, how can I get this on SQL in table format.
Thanks in advance
Need Output Table
Changeset: 1234 User: XXXX Date: Monday, April 15, 2016 1:36:11 PM Branched from $/Project1/Prod branch $/Project1/Product/Databases/DB/View/view1.sql Changeset: 2345 User: XXXX Date: Monday, April 15, 2016 1:36:11 PM Branched from $/Project1/Prod branch $/Project1/Product/Databases/DB3/Table/Table2.sql TFS history file:
-----------------------------------------------------------------------------------------------------------------------
Changeset: 1234
User: XXXX
Date: Monday, April 15, 2016 1:36:11 PMComment:
Branched from $/Project1/ProdItems:
branch $/Project1/Product/Databases/DB/Views/view1.sql-----------------------------------------------------------------------------------------------------------------------
Changeset: 1234
User: XXXX
Date: Monday, April 15, 2016 1:36:11 PMComment:
Branched from $/Project1/ProdItems:
branch $/Project1/Product/Databases/DB3/Tables/Table2.sql
Two options come to mind
1) Write a chunk of code in an asynchronous script component
2) Import every row of data as a single string to a staging table, adding a row number to allow you to preserve ordering. Then write a ghastly stored proc which extracts the data from the staging table in the desired format.
Either way, this is going to require some significant coding.
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
November 30, 2017 at 8:30 am
Thom A - Thursday, November 30, 2017 8:07 AMIs that output table a single column...? I'd suggest it would be better to have each item as a column. Why do you want to convert it into Delimited Tab format on your SQL Server where each item hasit's header prior to it? That's going to be awful to query.
Yes, the initial output file is loaded to a table with single column, I'm looking to have this in table format from that single column table.
Each item as column may not be an option, as in production the number of script history might be more....
| Changeset | User | Date | BranchedFrom | branch |
| 1234 | XXXX | Monday, April 15, 2016 1:36:11 PM | $/Project1/Prod | $/Project1/Product/Databases/DB/View/view1.sql |
| 2345 | XXXX | Monday, April 15, 2016 1:36:11 PM | $/Project1/Prod | $/Project1/Product/Databases/DB3/Table/Table2.sql |
December 1, 2017 at 1:07 pm
Phil Parkin - Thursday, November 30, 2017 8:08 AMTsql-561486 - Thursday, November 30, 2017 8:00 AMHi Team,I'm trying to load history of TFS script deployed to database.
The output of history is as below, how can I get this on SQL in table format.
Thanks in advance
Need Output Table
Changeset: 1234 User: XXXX Date: Monday, April 15, 2016 1:36:11 PM Branched from $/Project1/Prod branch $/Project1/Product/Databases/DB/View/view1.sql Changeset: 2345 User: XXXX Date: Monday, April 15, 2016 1:36:11 PM Branched from $/Project1/Prod branch $/Project1/Product/Databases/DB3/Table/Table2.sql TFS history file:
-----------------------------------------------------------------------------------------------------------------------
Changeset: 1234
User: XXXX
Date: Monday, April 15, 2016 1:36:11 PMComment:
Branched from $/Project1/ProdItems:
branch $/Project1/Product/Databases/DB/Views/view1.sql-----------------------------------------------------------------------------------------------------------------------
Changeset: 1234
User: XXXX
Date: Monday, April 15, 2016 1:36:11 PMComment:
Branched from $/Project1/ProdItems:
branch $/Project1/Product/Databases/DB3/Tables/Table2.sqlTwo options come to mind
1) Write a chunk of code in an asynchronous script component
2) Import every row of data as a single string to a staging table, adding a row number to allow you to preserve ordering. Then write a ghastly stored proc which extracts the data from the staging table in the desired format.
Either way, this is going to require some significant coding.
Thank you Phil, got it worked with Option 2.
December 1, 2017 at 1:15 pm
Tsql-561486 - Friday, December 1, 2017 1:07 PMThank you Phil, got it worked with Option 2.
Well done! Out of interest, how many rows of code were required?
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 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply