SSIS export to flat file - how to configure all DataTypes for columns w/o manual entry?

Question

In my OLE DB Source to Flat File Destination Data Flow, I have quite a few columns and it's excruciating to manually set each one to decimal with a scale of 4.

I tried adjusting my SQL code in the OLE DB Source to CAST to decimal(14,4) to see if SSIS would pick that up in Suggest Types, but it likes single-byte signed integer. I don't see any option to set default column DataTypes. Is there another way - either by adjusting my SQL code, or within SSIS options - to get all of my columns to decimal, scale 4?

Answer 1

If your goal is simply to create a Flat File have you considered using BCP.EXE to Bulk Copy your data out. Bulk Import and Export of Data (SQL Server) gives a link to the bcp utility.

You do need to give directions on how to interpret the data. Here at Create a Format File (SQL Server) you can examine the two Format File type used to define the data format. (Naturally.)

The format file that I use is a Non-XML Format File. This is just a text file and is fairly easy to type and make changes within. For me, this is a quick way to export a flat file. (Of course, if you read further down the page you will see an XML Format File, which might be more your style.)

It is also possible to use BCP to copy data back into a SQL Server and it is pretty fast. (However BULK INSERT tends to be a bit faster for importing data.)

So BCP is fast, fairly easy, and comes in two flavors.