SSIS - What is the best practice for creating a fixed length flat file.

Question

SSIS - What is the best practice for creating a fixed length flat file.

Viewing 4 posts - 16 through 18 (of 18 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 1

MWise (6/5/2013)
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

Understood and thank you for the thoughtful feedback. But that's also why SSIS isn't my first choice for such things. Since this was actually posted in a GENERAL forum, I thought I'd mention the possibility. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

MWise SSCarpal Tunnel Points: 4074 More actions · Answer 2

LOL! Jeff have you been listening in to my meetings? None of our SQL people would choose SSIS. Which is exactly how I felt when I first started using it. It's grown on me in the past 5years. But I'll always hold a special place in my heart for bcp. SSIS tries to be so much to so many people while bcp is focused and gives amazing performance and there's no fiddly GUI to muck around with.

MWise

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 3

Sigerson (6/5/2013)
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.

I absolutely agree about learning something new. Now, if I could just convince people that "something new" isn't always better... just different with a heavy dose of "It Depends". 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 4

MWise (6/5/2013)
LOL! Jeff have you been listening in to my meetings? None of our SQL people would choose SSIS. Which is exactly how I felt when I first started using it. It's grown on me in the past 5years. But I'll always hold a special place in my heart for bcp. SSIS tries to be so much to so many people while bcp is focused and gives amazing performance and there's no fiddly GUI to muck around with.
MWise

BWAAA-HAAA!!!! I'm not sure how I missed the post above! 🙂

I'd love to be the proverbial fly-on-the-wall for your meetings. If they're "User Group" meetings, send me an invitation. I'd love to hear folks cut from the same cloth discussing such a thing.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)