January 25, 2016 at 3:01 am
Hi,
I am trying to create an SSIS package that creates a file in the below format -
"BEN_CLAIM" "3P_RENT"
"UPD" "810|1B00610003008" "14-SEP-2015" "" "86.51" "78.16" "14-SEP-2015" "22-NOV-2015" "SMITH" "J" "" "" "3" "STREET" "ROAD" "County" "TOWN" "NP26 ***" "SERVICES" "8.35" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" ""
"UPD" "810|1B00610003008" "14-SEP-2015" "" "85.78" "78.16" "23-NOV-2015" "31-DEC-4000" "SMITH" "J" "" "" "3" "STREET" "ROAD" "County" "TOWN" "NP26 4***" "SERVICES" "8.35" "WATERH" "-0.73" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" ""
"NEW" "810|1B00110111001" "08-DEC-2015" "" "22.12" "20.81" "08-DEC-2015" "31-DEC-4000" "SMITH" "RL" "" "" "1" "STREET" "County" "MONMOUTHSHIRE" "" "NP26 ***" "SERVICES" "1.33" "GAS" "0.70" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" ""
"NEW" "810|1B00110112001" "17-DEC-2015" "" "22.12" "20.81" "17-DEC-2015" "31-DEC-4000" "SMITH" "J" "" "" "2" "STREET" "County" "MONMOUTHSHIRE" "" "NP26 ***" "SERVICES" "1.33" "GAS" "0.70" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" ""
How do I get the header at the top of the file in a Select statement -
I tried something like below and got an error -
select '"BEN_CLAIM" "3P_RENT"'
UNION
SELECT *
FROM Lookup
The error I got was
- Msg 205, Level 16, State 1, Line 1
All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.
I understand the error, but no idea how to merge the header record which is always going to be the same and the data below, which I can derive from a Select Statement.
January 25, 2016 at 3:09 am
If you're using SSIS, you don't need to restrict yourself to T-SQL solutions. You'll probably find it easier to run a simple Powershell (or other scripting language) command to put the header at the top.
John
January 25, 2016 at 3:17 am
I don't have the skills in that though, so was just wondering if there was anything in TQL that could do it.
If not, not to worry, I'll see what I can come up with.
January 25, 2016 at 3:21 am
That's one of the fringe benefits of SSIS - it gives you the opportunity / forces you (delete according to your own experience) to learn other things. Everything I know about Powershell (not all that much, I grant you) has been learned through trying to write the best possible SSIS package.
John
January 25, 2016 at 6:07 am
Why dont you just set the flat file destination to output the header row?
January 25, 2016 at 6:18 am
Hi Dave,
That's what I did - basically in my flat file Destination, when I double click it - in the big white space under Header, I have placed - "BEN_CLAIM" "3P_RENT"
As this will always be the same.
Thanks for your help
January 25, 2016 at 8:01 am
Hi TSQL Tryer,
Not sure if this abbreviated test example helps? Unfortunately not recommended (these days) for security reasons i.e. xp_cmdShell must be enabled while SQLServerAgent will need access to the files.
DECLARE
@FLAGS varchar(50) = ' -c -t, -T -S ' + @@servername, --replace -t, with -t\0 if csv not wanted
@MYPATH nvarchar(500) = 'C:\Users\Public\Documents\', --NB: use a folder sqlserveragent can access
@HEADERFILE nvarchar(255) = 'MyHeaderFile.txt',
@BODYFILE nvarchar(255) = 'MyBodyFile.txt',
@MERGEDFILE varchar(255) = 'MyMergedFile.txt', --can also be .csv for spreadsheet retrieval by users
@sql varchar(4000),
@CMD varchar(8000)
--output header file
SET @sql = 'SELECT ''col1'',''col2'',''col3'''
SET @CMD = 'bcp "' + @sql + '" queryout ' + @MYPATH + @HEADERFILE + @FLAGS
EXEC master..xp_cmdshell @CMD --,no_output --un-comment when done debugging
--output body file
SET @sql = 'SELECT ''red'',''black'',''blue'' UNION SELECT ''soap'',''lard'',''honey'''
SET @CMD = 'bcp "' + @sql + '" queryout ' + @MYPATH + @BODYFILE + @FLAGS
EXEC master..xp_cmdshell @CMD,no_output
--merge files
SET @CMD = 'copy /b ' + @MYPATH + @HEADERFILE + '+' + @MYPATH + @BODYFILE + ' ' + @MYPATH + @MERGEDFILE
EXEC master..xp_cmdshell @CMD,no_output
--delete header file
SET @CMD = 'del ' + @MYPATH + @HEADERFILE
EXEC master..xp_cmdshell @CMD,no_output
--delete body file
SET @CMD = 'del ' + @MYPATH + @BODYFILE
EXEC master..xp_cmdshell @CMD,no_output
January 25, 2016 at 9:11 am
TSQL Tryer (1/25/2016)
Hi,How do I get the header at the top of the file in a Select statement -
select '"BEN_CLAIM" "3P_RENT"' ,'','','',0,'' -- Same number of columns and same types as in lookup
UNION
SELECT * FROM Lookup
The number of columns and the types should be the same for the header and in the select.
Be carefull with the UNION, the rows are not 'sorted' in a specific order with this construction.
The header might end up somewhere else than at the top.
ben
January 26, 2016 at 3:57 am
Following Ben's suggestion, if hidden control chars not a problem in output, set according to your environ for nulls first or last...
select '"BEN_CLAIM" "3P_RENT"' ,'','','','','',NULL as MySortCol
UNION
SELECT *,'' as MySortCol FROM Lookup
ORDER BY MySortCol --as first sort order
January 26, 2016 at 10:24 am
John Mitchell-245523 (1/25/2016)
If you're using SSIS, you don't need to restrict yourself to T-SQL solutions. You'll probably find it easier to run a simple Powershell (or other scripting language) command to put the header at the top.John
Heh... it's amazing that DBAs will allow people using SSIS to use PowerShell and not xp_CmdShell to do such things. We've actually dismantled all SSIS packages except 1 and we're working on that.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 27, 2016 at 2:09 am
Jeff Moden (1/26/2016)
John Mitchell-245523 (1/25/2016)
If you're using SSIS, you don't need to restrict yourself to T-SQL solutions. You'll probably find it easier to run a simple Powershell (or other scripting language) command to put the header at the top.John
Heh... it's amazing that DBAs will allow people using SSIS to use PowerShell and not xp_CmdShell to do such things. We've actually dismantled all SSIS packages except 1 and we're working on that.
Horses for courses. I've never advocated disallowing xp_cmdshell, by the way.
John
January 27, 2016 at 7:18 am
What you have here is a multi-record format where the header line has a different column-format than the data lines. T-SQL solutions traditionally have trouble with these as hou have found when tying to use UNION. In SSIS this is not a problem. You can do this by writing to the file two separate times: once to write the header and once to write the data lines.
Create two Connection Managers, one for tbe header and one for tbe data lines. In the one for data lines configure it to append data to the file instead of overwriting the file. Implement teo Data Flows, get the header and write it to the file and a second for the data lines. Post back if you have issues.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
January 27, 2016 at 11:19 am
Jeff's mention was interesting as I, in similar fashion, switched all intranet data integration and processing to TSQL when DTS (SSIS predecessor) became too cumbersome for me to manage the ins and outs of our busy requirement. Lucky, when DTS was dropped with no migration plan, my TSQL processes continued to migrate smoothly up through SQL 2005, 2008, 2012 and 2014.
With only two sys-admins accessing the server and it's DBs, security was less complicated and I found TSQL more enjoyable and easier to manage, re-use , search and modify.
In 5 minutes I could create a job which sent view/table name, transport method and destination to a particular sproc which would export the data accordingly. Decryptions, controlled incremental imports, source file management and granular custom error controls are also well handled by TSQL.
Not knocking SSIS, as John says, horses for courses.
January 27, 2016 at 2:26 pm
Jeff Moden (1/26/2016)
We've actually dismantled all SSIS packages except 1 and we're working on that.
Funny you mention it. I finished migrating the last process that made use of xp_cmdshell in my current environment to SSIS almost a year ago to the day.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
January 27, 2016 at 5:11 pm
Orlando Colamatteo (1/27/2016)
Jeff Moden (1/26/2016)
We've actually dismantled all SSIS packages except 1 and we're working on that.Funny you mention it. I finished migrating the last process that made use of xp_cmdshell in my current environment to SSIS almost a year ago to the day.
Heh... horses for courses. 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply