June 19, 2019 at 11:27 pm
how to pass delimited text from stored procedure to show in flat file
any idea how we can manipulate this in stored procedure? using temp tables?
June 20, 2019 at 12:57 pm
CREATE TABLE #output (TypeNo int,Line varchar(255));
INSERT #output (TypeNoLine)
SELECT 1,'HEADER|"'+CONVERT(char(8),GETDATE(),112))+'|"'+REPLACE(CONVERT(char(6),GETDATE(),108),':','') FROM #output;
INSERT #output (TypeNo,Line)
SELECT 2,CAST(id as varchar(20))+'|"'+CAST(empid as varchar(20))+'|"'+[text]
FROM [sometable];
INSERT #output (TypeNo,Line)
SELECT 3,'COUNT|"'+CAST(COUNT(*)+1 as varchar(20)) FROM #output;
SELECT Line
FROM #output
ORDER BY TypeNo ASC;
Far away is close at hand in the images of elsewhere.
Anon.
June 20, 2019 at 2:43 pm
thank you for the reply
June 21, 2019 at 3:07 am
Hello David, Instead of calling from procedure, can we handle in SSIS package using script component? with out casting original columns from the table?
June 21, 2019 at 7:48 am
Hello David, Instead of calling from procedure, can we handle in SSIS package using script component? with out casting original columns from the table?
Due to the fact the COUNT line has two values and the other lines have 3 and are of different types you would have to CAST the data anyway to make them varchar, I am not sure how a script component would help. If you want use SSIS I would suggest changing my query to output separate varchar columns (and yes COUNT would have to be 3 as well) and pass the output to a Flat File Destination.
p.s. As our resident SQL guru would say, using SSIS for this is like using a sledgehammer to crack a nut. Unless there is a good fundamental reason to use SSIS this would be easier if all done in T-SQL even using SQLCMD with the query would be more preferable.
Far away is close at hand in the images of elsewhere.
Anon.
June 21, 2019 at 3:49 pm
If SQL Server had a BULK EXPORT command like they have a BULK IMPORT command I'd agree it was overkill, but the only way to export results to a delimited file would either be to use BCP, SSIS, or write some custom code in .Net. I'm not sure I see all the requirements from the original post as currently edited, but SSIS can call a stored procedure for a data source, and write the output to a text file. That may make more sense than trying to format text in T-SQL.
June 21, 2019 at 4:04 pm
... but SSIS can call a stored procedure for a data source, and write the output to a text file. That may make more sense than trying to format text in T-SQL.
True but as I stated the input datatypes and output layout are not consistent and you would still have to convert the data in SSIS to make it useful.
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply