March 26, 2018 at 12:31 pm
hi ,
I have a requirement where the user is asking me to extract 643 columns in a pipe delimited file (single file). Is it possible to create a SSIS package and generate a 640+ columns in a pipe delimited single file? will there be any cons if we are doing so? I know excel wont support 643 columns , is it?
Thanks.
March 27, 2018 at 7:09 am
komal145 - Monday, March 26, 2018 12:31 PMhi ,
I have a requirement where the user is asking me to extract 643 columns in a pipe delimited file (single file). Is it possible to create a SSIS package and generate a 640+ columns in a pipe delimited single file? will there be any cons if we are doing so? I know excel wont support 643 columns , is it?Thanks.
I don't believe there is a restriction there, but could be wrong. Obviously SQL Server may have one on the table level if extraction means going to a table with something greater than 1024 fields or whatever. But, should be able to handle it.
I wouldn't make any assumptions on the user here on whether you think they need it or not based on tools they are using. I typically export 200+ fields myself for my team, but this is so they can denormalize the data and flatten it out for predictive analytics in R, not Excel.
The con is absolutely going to be tied to how you manage such a wide dataset and the performance impacts it may have being wide too. In most of my experiences, I'm using Python outside of SSIS to do this mostly because Python can handle more of the dynamic elements of me not having to physically define all the fields when working on the ETL aspect, which is a pain to deal with constantly in SSIS. On the other hand, that's the entire point of SSIS, to have that much control over the data so you can define, convert, direct each field of data in such a way that meets the business requirements of the underlying system or user.
April 2, 2018 at 6:55 am
komal145 - Monday, March 26, 2018 12:31 PMhi ,
I have a requirement where the user is asking me to extract 643 columns in a pipe delimited file (single file). Is it possible to create a SSIS package and generate a 640+ columns in a pipe delimited single file? will there be any cons if we are doing so? I know excel wont support 643 columns , is it?Thanks.
Excel 2016 supports 16,384 columns (A through Z, AA through ZZ, and AAA through XFD), so it seems unlikely that Excel will be the limiting factor.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
April 2, 2018 at 11:10 am
sgmunson - Monday, April 2, 2018 6:55 AMkomal145 - Monday, March 26, 2018 12:31 PMhi ,
I have a requirement where the user is asking me to extract 643 columns in a pipe delimited file (single file). Is it possible to create a SSIS package and generate a 640+ columns in a pipe delimited single file? will there be any cons if we are doing so? I know excel wont support 643 columns , is it?Thanks.
Excel 2016 supports 16,384 columns (A through Z, AA through ZZ, and AAA through XFD), so it seems unlikely that Excel will be the limiting factor.
That would depend on the driver used. Older versions had the limit to 256 columns.
A text file or CSV won't have any limit.
April 2, 2018 at 11:18 am
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply