import large flat file with very long string as SSIS package

Question

I am trying to create a SSIS package through data import in SQL Server 2012.

The data looks like this (this is just 1 row of the data, removed confidential data):

35 XXXX.XXXXXXXXXXXXXXXX 2. XXXX. 6. XXXX. 1. 10.NNN 80000000.XXXXXXXXd 1001. 1001. 6.NNNNNNNNNNNNNNN 0. 0. 17.XXXXXXXX-XXXXXXXXXXX-XXINCMGFO XXXX. 0. 0.00000000000000N N XXXXXXXXXXXXXXXXXXXXXXXXXXXEVITE, INC. XXXXXXXX CA US 5734. 0.0TMP 88 12.XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX 12.XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX XXXXXXXXXXXXXXXXXXXXX -.270000 .00

Each row of this flat file does NOT share the same pattern and some may have single quote or double quote in the data. These single/double quotes may not be complete, they left in the data because the data has not been cleaned completely.

Each flat file is generated at the end of each month. What I am planning to do is to create a SSIS package, then create a job to call this SSIS package to store all the monthly raw data into 1 staging table, automatically. Later I will write a stored procedure to clean the data in this staging table and move the cleaned data into another table.

But my question here is about creating this SSIS package.

It kept giving me error like this:

Therefore, I tried to change the properties:

Change String to 5000 length, I also tried 8000, didn't work either

Change to Text Stream, since I thought this may allow longer string..

They still gave me the same error.

So I have found a solution here I guess maybe it's because my data has incomplete double quote or single quote in some rows, but I cannot parameterized queries as the solution does, because everything in a row from the flat file will be saved in 1 column....

Therefore, is there any way to resolve this problem as create SSIS package?

How are you creating the package? Import Export wizard? Don't create the staging table while you are creating the _package_, create it beforehand. Create a staging table with *one* `varchar(8000)` column. Then use SSDT to create a package to load your file into this staging table. It looks you are having an issue creating the staging table whilst defining the package. Don't do it that way. Anyway it helps if you explain exactly how you go to this error message — Nick.Mc, Aug 19 '16 at 00:10
well if there truly is no pattern such as number of columns or consistently delimited or qualified text then this method wont work you will have to clean the file before importing. Can you affect how you are recieving the export? If so I would suggest Pipe delimited | because then if text qualifiers or are messed up you can still bring in the contents then clean them up. It won't get confused with commas in the address etc. There are a few more techniques but more information such as example file formats would be needed. I suspect your MAX Length issue is due to delimiter/row delimters — Matt, Aug 19 '16 at 00:43
It might also be that 'column names' are ticked when importing the file and it thinks the first row is a column identifier..... the OP needs to provide more information. — Nick.Mc, Aug 19 '16 at 01:05
Yes, I was using Import wizard. Why should I use SSDT instead? I haven't created the staging table yet, but just got stucked when I was creating the SSIP package.. — Cherry Wu, Aug 19 '16 at 05:12
@Matt, you mean I could do Pipe delimited through Import Wizard or some other tools? — Cherry Wu, Aug 19 '16 at 05:14
In that case the issue is probably that you ticked 'column names are in first row', and it assumes the column name is a big long ugly string (of data) then it throws an error about the identifier. An identifier usually means a column name (or table name, view name etc.). So first thing is to make sure you don't have 'column names in header'. If you are serious about solving this you need to post some screenshots describing what you're doing. The import/export wizard probably does not offer the flexibility you need. SSDT is the tool used to develop packages from scratch — Nick.Mc, Aug 19 '16 at 08:45
We don'tt have enough info about your configuration or process to understand what could be wrong. It might be as simple as there not being column names in the file and you have identified that it includes them, but if your delimiters & text qualifiers are not consistent then the source of the flat file is writing bad file format. In that case if the export can be switched to Pipe delimited it should hopefully solve some of the common comma delimited issues with address data. And yes you should be able to use SSIS and maybe import wizard (haven't tried that), bulk import, or other methods — Matt, Aug 19 '16 at 14:28

Cherry Wu · Accepted Answer · 2016-08-23T05:20:44.603

The problem has been solved. It's still worthy to share here, since there are some details need to pay attention to, otherwise there will be errors.

We can still solve the problem through SQL Server Import Wizard. In my case, the flat files do not have headers. Each file is a flat file and they don't have .txt extension.

Detail 1 When choosing file from the source folder, we have to set file format as .txt when it shows all files; and need to choose all files when it shows .txt. Otherwise later may not be able to see any data from preview and cannot proceed.

Detail 2 In my case, each row of record is very long and each row use different dimilters. It's better to use Fixed width format, otherwise you may get the error I was showing above. In the file name I had to remove ".txt", since my real file names do not have .txt, but they should be treated as .txt here. Uncheck "Column names in the first data row" because I don't have file header.

Detail 3 After choosing Fixed width format, we need to drag the red line to the end of each row. You should see these black squares which means the end of a row

Detail 4 Meanwhile, we could click on the ruler and separate each row into columns.

After all these settings, we could just keep clicking Next and in my case, I could generate SSIS package successfully now.

Thanks for returning and providing a detailed solution for your problem - this helps other people who search for this problem. Yes the wizard will import this data for you - I was thinking it was a different issue. — Nick.Mc, Aug 22 '16 at 22:38

import large flat file with very long string as SSIS package

1 Answers1

Linked