May 29, 2013 at 8:36 am
I am running into the problem of SSIS putting the CRLF in random places at the end of the row. If we assume I am going to start from scratch, can I fix this issue in the Stored Procedure I use in my OLE source, or can I fix it on the SSIS side?
I did see the following at MSDN but it did not make any sense to me since I don't see where to do this in the connection manager.
To make this work so that each row is exactly the same length, you may need to add a zero length column as the last one in the connection manager, and set the row delimiter on that one.
I did read as possibly using the advanced editor on the OLE adapter and adding a column there but how do I create a zero length column. If I send the header in the first row it is going to be at least 1 character.
Any help would be greatly appreciated.
FYI, I am using ragged right.
May 31, 2013 at 2:00 pm
Of course, bcp is the better choice just to spit out a lot of rows into a text file, but I don't think it can do a fixed-length file.
However, you can use Windows' system object FileSystemObject and manipulate it from within T-SQL to open and write text files. This would give you the control you need to make sure each line is of fixed length.
Here's the basic framework of commands to use in a stored procedure that will let you write a flat file out to a network drive without using SSIS. Obviously, I'm leaving out a lot of code: variable definitions, the data SELECTion code, etc. @token is the handle to the FSO, @FileID is the handle to your text file, @OutputPathFile is the full path and name of the file you want to create.
EXECUTE master.dbo.sp_configure 'show advanced options', 1;
RECONFIGURE
-- Turn on permission for OLE automation temporarily
EXECUTE sp_configure 'Ole Automation Procedures', 1;
RECONFIGURE
-- Create the FSO
EXECUTE @Result = sp_OACreate 'Scripting.FileSystemObject', @token out;
IF @Result<>0
PRINT 'Error ' + cast(@Result as varchar) + ': on FSO create';
-- Open/Create the text file: (2 = create, 8 = append),1 = true
EXECUTE @Result = sp_OAMethod @token, 'OpenTextFile', @FileID out, @OutputPathFile, 2,1
IF @Result<>0
PRINT 'Error ' + cast(@Result as varchar) + ': on FSO file open';
-- Create the CSV file header row
SET @WriteText = 'Date,Client,Matter,ExpCd,Units,Amount,Narrative,MatterTaskID';
EXECUTE @Result = sp_oaMethod @FileID, 'WriteLine', NULL, @WriteText;
IF @Result<>0
PRINT 'Error ' + cast(@Result as varchar) + ' writing header line ';
At this point, you can start to cycle through your query using a cursor or however you want. The variable @WriteText (note the 'CSV file header row' section above) is just a string that contains the line you want to write out including the commas between fields and the double quotes around each field if you need them. When each line is built, WRITE it out to your text file. You don't need to add CRLF or anything to the line ends; that's handled for you by the FileSystemObject.
After you're done, BE SURE to close the file and destroy the file object, and re-set the configuration options to clean things up. These can be the very last lines in the s/p:
EXECUTE @Result = sp_oaMethod @FileID, 'Close';
IF @Result<>0
PRINT 'Error ' + CAST(@Result as varchar) + ' closing data file: ' + @OutputFile;
EXECUTE @Result = sp_OADestroy @FileID;
EXECUTE @Result = sp_OADestroy @token;
-- turn off permission for OLE Automation
EXECUTE sp_configure 'Ole Automation Procedures', 0;
RECONFIGURE;
EXECUTE master.dbo.sp_configure 'show advanced options', 0;
RECONFIGURE;
Hth
Sigerson
"No pressure, no diamonds." - Thomas Carlyle
May 31, 2013 at 2:13 pm
That is some pretty impressive code. I had no idea I could do that kind of code inside a Stored Procedure. i did finally get the package to work. Come to find out the Stored procedure was returning nulls and I had to use CAST(ISNULL(Name, Space(30)) as CHAR(30))for about 100 some fields. Once I had those spaces instead of a null value SSIS started working correctly. The flat file connection manager should havea setting to over ride nulls with spaces to fill in to the define column width. I do prefer the column delimited files though.
Thanks for the response. I'll keep this link in my library for future reference.
May 31, 2013 at 2:18 pm
Thank you. It isn't elegant, but it works. I prefer elegant, but I'll settle for brute force if I have to. :laugh:
Sigerson
"No pressure, no diamonds." - Thomas Carlyle
May 31, 2013 at 3:08 pm
Sigerson (5/31/2013)
Of course, bcp is the better choice just to spit out a lot of rows into a text file, but I don't think it can do a fixed-length file.
It's super easy with BCP if you use a BCP format file.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 3, 2013 at 8:13 am
I stand corrected. I checked BOL briefly but I am not an expert with bcp by any means and haven't had any need to use a format file; I mostly use it to make output CSVs. I'll look around for an article, someone's bound to have written this up at some point.
Thanks,
Sigerson
"No pressure, no diamonds." - Thomas Carlyle
June 3, 2013 at 10:53 am
Sigerson (6/3/2013)
I stand corrected. I checked BOL briefly but I am not an expert with bcp by any means and haven't had any need to use a format file; I mostly use it to make output CSVs. I'll look around for an article, someone's bound to have written this up at some point.Thanks,
There are two other ways to pull this off using BCP... you can create a VIEW with CHAR columns and then set BCP to not include a delimiter (doesn't work with Temp Tables, obviously) or by creating a stored proc that would do the same as the view and then use the BCP "Query OUT" mode without delimiters.
The reason why I'm so "pro" on BCP is that you don't have to get another system (SSIS) and the related nuances involved. It can all be done using T-SQL and xp_CmdShell (which ISN'T a security risk, BTW).
--Jeff Moden
Change is inevitable... Change for the better is not.
June 3, 2013 at 1:15 pm
I would not recommend rolling your own code to use FSO to create a fixed width file - that is definitely NOT a best practice. Either use BCP or in SSIS fix your SELECT statement to pad and handle nulls or as the article says, set up your connection manager correctly. You can edit the connection manager in BIDS by Right Clicking and then selecting Edit. Go to the Advanced Tab and then the last column in which you can set the OutPut column width and the ColumnDelimiter. Sometimes you do need to add a "dummy" column that is 0 OutputColumn width and then set the ColumnDelimiter on this last column as your row delimiter. To add a column, just use the New button at the bottom of the Advanced properties window. You may need to play with setting your file as Fixed Width or Ragged Right type on the General settings tab.
MWise
June 3, 2013 at 1:20 pm
Why would you say it is not a best practice? It is, admittedly, a brute force approach, but it does give good control. What makes it a bad practice in your view?
Sigerson
"No pressure, no diamonds." - Thomas Carlyle
June 3, 2013 at 4:33 pm
MWise (6/3/2013)
I would not recommend rolling your own code to use FSO to create a fixed width file - that is definitely NOT a best practice. Either use BCP or in SSIS fix your SELECT statement to pad and handle nulls or as the article says, set up your connection manager correctly. You can edit the connection manager in BIDS by Right Clicking and then selecting Edit. Go to the Advanced Tab and then the last column in which you can set the OutPut column width and the ColumnDelimiter. Sometimes you do need to add a "dummy" column that is 0 OutputColumn width and then set the ColumnDelimiter on this last column as your row delimiter. To add a column, just use the New button at the bottom of the Advanced properties window. You may need to play with setting your file as Fixed Width or Ragged Right type on the General settings tab.MWise
Sigerson (6/3/2013)
Why would you say it is not a best practice? It is, admittedly, a brute force approach, but it does give good control. What makes it a bad practice in your view?
I agree with that question. WHAT makes it "NOT a best practice"? It might be a slower than some of the alternatives but it is a tried and true method that works very reliably and, as Sigerson states, provides a huge amount of good control.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 3, 2013 at 6:24 pm
Its not an SSIS best practice because SSIS has built in tasks that already do this without having to write or maintain a single line of code. The data flow task comes with error handling, logging, and is optimized for this specific task of creating flat files. You can configure easily for movement between environments with having to change the package. Any SSIS developer knows how to use a dataflow task and flat file connection manager to create an ouput file, while most developers wouldnt know how to use FSO within tsql. So there ease of use and maintajnability. Plus you don't have to change SQL settings to get it to run nor deal with specialized permissions which in many organizations is not allowed. So if you are already using SSIS Why would you recreate the wheel?
MWise
June 3, 2013 at 11:01 pm
MWise (6/3/2013)
Its not an SSIS best practice because SSIS has built in tasks that already do this without having to write or maintain a single line of code. The data flow task comes with error handling, logging, and is optimized for this specific task of creating flat files. You can configure easily for movement between environments with having to change the package. Any SSIS developer knows how to use a dataflow task and flat file connection manager to create an ouput file, while most developers wouldnt know how to use FSO within tsql. So there ease of use and maintajnability. Plus you don't have to change SQL settings to get it to run nor deal with specialized permissions which in many organizations is not allowed. So if you are already using SSIS Why would you recreate the wheel?MWise
Then "Best Practice" is only in the eyes of the beholder. I know just as many organizations that don't allow SSIS for many reasons.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 5, 2013 at 7:20 am
I agree with both of you. I've used BCP and SSIS repeatedly. Each technique has its good uses and I probably should have mentioned that most of my export projects don't exceed 200 rows, which makes speed much less of an issue. We are lucky that SQL Server gives us such a big tool kit and so many ways to attack problems. This is such a great site because I get so many tips and can count on getting many different points of view about how to do things.
As far as maintainability, we should all be adaptable and ready to learn something new, or we're definitely in the wrong business. I'll leave the next guy with readable, self-explanatory code and lots of comments, and that should be enough.
I'd like to know how the OP is getting along okay with his fixed length file problem.
Sigerson
"No pressure, no diamonds." - Thomas Carlyle
June 5, 2013 at 11:55 am
Once I put the ISNULL expression on every column and replaced nulls with the spaces with the SPACE() function everything has worked fine.
I have bookmarked this thread though for future packages.
June 5, 2013 at 1:58 pm
Jeff, I hear what you are saying but the original question was about using SSIS. So that choice has already been made. If one of my developers put in a SQL Task in their package to create a flat file using the code provided in this thread, they'd never get it past code review. Our standard is to use the MS tasks provided in SSIS. If there isn't one that can do the job then go and see if we have a task in our 3rd party provider kit or in our in house lib. Only then if you can't get the job done would you roll your own code in this manner. And honestly if we were still in SSIS, there'd be a good case that the file creation should happen in a .NET script task rather than TSQL out to FSO. Now if I don't have access to SSIS and I only have SQL then the code would be appropriate. Though I still would probably stick with bcp as a personal preference plus I know it would raise no question in our org's code review/enterprise change management process.
MWise
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply