Error loading multiple CSV files in SSIS
-
Hi everyone
I have a bunch of CSV files that I need to bulk insert into a table. I saw this video on YouTube and implemented it. It works for some files but not for others.
https://www.youtube.com/watch?v=1vM9s7OxnTM
Basically, the approach from the video is to use a multi flat file connection manager to handle loading multiple flat CSV files from a folder. I am getting below error. How do I fix it?
Error:
Error: 0xC02020A1 at Update Table Stocks, Stock CSV Files [12]: Data conversion failed. The data conversion for column "NAME" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".
Error: 0xC020902A at Update Table Stocks, Stock CSV Files [12]: The "Stock CSV Files.Outputs[Flat File Source Output].Columns[NAME]" failed because truncation occurred, and the truncation row disposition on "Stock CSV Files.Outputs[Flat File Source Output].Columns[NAME]" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
Error: 0xC0202092 at Update Table Stocks, Stock CSV Files [12]: An error occurred while processing file "C:\Users\Me\Documents\Trading\Trading_Data\Stock_Prices\ADAP.O#Adaptimmune Therapeutics ADR Representing 6 Ord Shs (D).csv" on data row 2.
Error: 0xC0047038 at Update Table Stocks, SSIS.Pipeline: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on Stock CSV Files returned error code 0xC0202092. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.File:
SYMBOL,INTERVAL,NAME,DATE,OPEN,HIGH,LOW,CLOSE,VOLUME
ADAP.O,D,Adaptimmune Therapeutics ADR Representing 6 Ord Shs,2019-09-03 12:00:00 AM,1.6,1.6401,1.51,1.56,298735
ADAP.O,D,Adaptimmune Therapeutics ADR Representing 6 Ord Shs,2019-09-04 12:00:00 AM,1.59,1.66,1.55,1.65,395428
ADAP.O,D,Adaptimmune Therapeutics ADR Representing 6 Ord Shs,2019-09-05 12:00:00 AM,1.68,1.8,1.62,1.66,214112The SS table that gets the imported data is below (ie these are the only fields I need):
CREATE TABLE [dbo].[Stocks](
[SYMBOL] [nchar](10) NOT NULL,
[TRADE_DATE] [date] NOT NULL,
[OPEN_PRICE] [numeric](12, 5) NOT NULL,
[HIGH_PRICE] [numeric](12, 5) NOT NULL,
[LOW_PRICE] [numeric](12, 5) NOT NULL,
[CLOSE_PRICE] [numeric](12, 5) NOT NULL,
[VOLUME] [numeric](18, 0) NOT NULL
) ON [PRIMARY]
GOThank you
- This topic was modified 1 month, 2 weeks ago by water490.
-
OK, those errors are actually pretty helpful.
There must be one or more occurrences of the column NAME in file
ADAP.O#Adaptimmune Therapeutics ADR Representing 6 Ord Shs (D).csv
where the data is longer than what is being handled by the package.
Either the target table's column is too short, or the configured max length of NAME within the package is too short.
Another possibility is that the data coming in is non-VARCHAR, but you have your package and target table configured to work with VARCHAR.
The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel -
If you look at the column headers of the file and compare them to the table, they don't match. We can't see your package to know what the mapping is. That would explain why it "works for some files but not for others". What to do about it in SSIS is beyond me because I don't use SSIS. There has to be a way for SSIS to read that column header line in the file and match it up with either the correct mapping or the correct table.
--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 wrote:
If you look at the column headers of the file and compare them to the table, they don't match. We can't see your package to know what the mapping is. That would explain why it "works for some files but not for others". What to do about it in SSIS is beyond me because I don't use SSIS. There has to be a way for SSIS to read that column header line in the file and match it up with either the correct mapping or the correct table.
not quite that easy in SSIS - a flat file is defined with logical names associated to a column on the file - the HEADER of the file when its read is irrelevant - for SSIS the first column gets the name that was defined for THAT file definition. if number of columns on file is changed or if the position of a column changes on the file itself, SSIS does not know about it - so sometimes it works (e.g. does not give an error) and happily loads the data from the file onto the destination (but puts data on wrong columns)) or it fails completely (e.g. different number of columns on input file, or invalid datatype for the column definition within SSIS)
only way to process different layouts within SSIS is to either treat them as a single column and then process them in SQL (tricky) or use C# script to read file as CSV, and then do column name mapping to destination (which is easy to do), and address potential datatype issues on this load.
- Phil Parkin wrote:
OK, those errors are actually pretty helpful.
There must be one or more occurrences of the column NAME in file
ADAP.O#Adaptimmune Therapeutics ADR Representing 6 Ord Shs (D).csv
where the data is longer than what is being handled by the package.
Either the target table's column is too short, or the configured max length of NAME within the package is too short.
Another possibility is that the data coming in is non-VARCHAR, but you have your package and target table configured to work with VARCHAR.
The part that is throwing me off is that I actually don't use NAME in the final output. Below are screenshots of the columns.
Any ideas on what I can do to fix this?
- frederico_fonseca wrote:Jeff Moden wrote:
If you look at the column headers of the file and compare them to the table, they don't match. We can't see your package to know what the mapping is. That would explain why it "works for some files but not for others". What to do about it in SSIS is beyond me because I don't use SSIS. There has to be a way for SSIS to read that column header line in the file and match it up with either the correct mapping or the correct table.
not quite that easy in SSIS - a flat file is defined with logical names associated to a column on the file - the HEADER of the file when its read is irrelevant - for SSIS the first column gets the name that was defined for THAT file definition. if number of columns on file is changed or if the position of a column changes on the file itself, SSIS does not know about it - so sometimes it works (e.g. does not give an error) and happily loads the data from the file onto the destination (but puts data on wrong columns)) or it fails completely (e.g. different number of columns on input file, or invalid datatype for the column definition within SSIS)
only way to process different layouts within SSIS is to either treat them as a single column and then process them in SQL (tricky) or use C# script to read file as CSV, and then do column name mapping to destination (which is easy to do), and address potential datatype issues on this load.
the layout has not and should not change. the files are outputted from the same program. here is an example of apple stock that got loaded but not Adaptimmune Therapeutics ADR Representing 6 Ord Shs.
its almost like SSIS is importing all the fields and assumes the length of each field and then complains when it finds one file that exceeds it. Just a guess on my part.
-
If you don't need NAME at all, take it out of the data flow by going into the data source advanced editor / column mappings and setting Output Column to 'ignore' for the NAME column. That might do it.
The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel -
I thought that maybe because I am not outputting NAME into the final table that may be the issue. It didn't change anything.
Error: 0xC02020A1 at Update Table Stocks, Stock CSV Files [16]: Data conversion failed. The data conversion for column "NAME" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".
Error: 0xC020902A at Update Table Stocks, Stock CSV Files [16]: The "Stock CSV Files.Outputs[Flat File Source Output].Columns[NAME]" failed because truncation occurred, and the truncation row disposition on "Stock CSV Files.Outputs[Flat File Source Output].Columns[NAME]" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
Error: 0xC0202092 at Update Table Stocks, Stock CSV Files [16]: An error occurred while processing file "C:\Users\Me\Documents\Trading\Trading_Data\Stock_Prices\ADAP.O#Adaptimmune Therapeutics ADR Representing 6 Ord Shs (D).csv" on data row 2.
Error: 0xC0047038 at Update Table Stocks, SSIS.Pipeline: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on Stock CSV Files returned error code 0xC0202092. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.CREATE TABLE [dbo].[Stocks](
[SYMBOL] [nchar](10) NOT NULL,
[NAME] [nvarchar](100) NOT NULL,
[TRADE_DATE] [date] NOT NULL,
[OPEN_PRICE] [numeric](12, 5) NOT NULL,
[HIGH_PRICE] [numeric](12, 5) NOT NULL,
[LOW_PRICE] [numeric](12, 5) NOT NULL,
[CLOSE_PRICE] [numeric](12, 5) NOT NULL,
[VOLUME] [numeric](18, 0) NOT NULL
) ON [PRIMARY]
GOSame as before...some symbols got loaded but not the one above.
I'm super stumped. Any suggestions would be much appreciated.
-
Are you confirming that you have done as I suggested and that the same error occurs?
The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel - Phil Parkin wrote:
Are you confirming that you have done as I suggested and that the same error occurs?
Sorry for the confusion. The post you are referring to isn't related to your suggestion.
I implemented what you suggested. That NAME error is gone. Now, I am getting a new one.
Error: 0xC02020C4 at Update Table Stocks, Stock CSV Files [12]: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.
Error: 0xC0047038 at Update Table Stocks, SSIS.Pipeline: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on Stock CSV Files returned error code 0xC02020C4. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.
Information: 0x40043008 at Update Table Stocks, SSIS.Pipeline: Post Execute phase is beginning.
Information: 0x402090DD at Update Table Stocks, Stock CSV Files [12]: The processing of file "C:\Users\Me\Documents\Trading\Trading_Data\Stock_Prices\CGUS.K#Capital Group Core Equity ETF (D).csv" has ended.
Information: 0x4004300B at Update Table Stocks, SSIS.Pipeline: "Update Stocks Table" wrote 996776 rows.
Information: 0x40043009 at Update Table Stocks, SSIS.Pipeline: Cleanup phase is beginning.
Task failed: Update Table StocksAny suggestions?
-
I made a mistake. There was so much output that I missed other errors. Here is the revised list:
Error: 0xC0202009 at Update Table Stocks, Update Stocks Table [54]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "Invalid character value for cast specification".
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "Invalid character value for cast specification".
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "Invalid character value for cast specification".
Information: 0x402090DE at Update Table Stocks, Stock CSV Files [12]: The total number of data rows processed for file "C:\Users\Me\Documents\Trading\Trading_Data\Stock_Prices\CGEN.O#Compugen Ord Shs (D).csv" is 1303.
Error: 0xC020901C at Update Table Stocks, Update Stocks Table [54]: There was an error with Update Stocks Table.Inputs[OLE DB Destination Input].Columns[VOLUME] on Update Stocks Table.Inputs[OLE DB Destination Input]. The column status returned was: "The value could not be converted because of a potential loss of data.".
Information: 0x402090DD at Update Table Stocks, Stock CSV Files [12]: The processing of file "C:\Users\Me\Documents\Trading\Trading_Data\Stock_Prices\CGEN.O#Compugen Ord Shs (D).csv" has ended.
Error: 0xC0209029 at Update Table Stocks, Update Stocks Table [54]: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "Update Stocks Table.Inputs[OLE DB Destination Input]" failed because error code 0xC0209077 occurred, and the error row disposition on "Update Stocks Table.Inputs[OLE DB Destination Input]" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.
Error: 0xC0047022 at Update Table Stocks, SSIS.Pipeline: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Update Stocks Table" (54) failed with error code 0xC0209029 while processing input "OLE DB Destination Input" (67). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.
Information: 0x402090DC at Update Table Stocks, Stock CSV Files [12]: The processing of file "C:\Users\Me\Documents\Trading\Trading_Data\Stock_Prices\CGGR.K#Capital Group Growth ETF (D).csv" has started.
Information: 0x402090DE at Update Table Stocks, Stock CSV Files [12]: The total number of data rows processed for file "C:\Users\Me\Documents\Trading\Trading_Data\Stock_Prices\CGGR.K#Capital Group Growth ETF (D).csv" is 678.
Information: 0x402090DD at Update Table Stocks, Stock CSV Files [12]: The processing of file "C:\Users\Me\Documents\Trading\Trading_Data\Stock_Prices\CGGR.K#Capital Group Growth ETF (D).csv" has ended.
Information: 0x402090DC at Update Table Stocks, Stock CSV Files [12]: The processing of file "C:\Users\Me\Documents\Trading\Trading_Data\Stock_Prices\CGNT.O#Cognyte Software Ord Shs (D).csv" has started.
Information: 0x402090DE at Update Table Stocks, Stock CSV Files [12]: The total number of data rows processed for file "C:\Users\Me\Documents\Trading\Trading_Data\Stock_Prices\CGNT.O#Cognyte Software Ord Shs (D).csv" is 948.
Information: 0x402090DD at Update Table Stocks, Stock CSV Files [12]: The processing of file "C:\Users\Me\Documents\Trading\Trading_Data\Stock_Prices\CGNT.O#Cognyte Software Ord Shs (D).csv" has ended.
Information: 0x402090DC at Update Table Stocks, Stock CSV Files [12]: The processing of file "C:\Users\Me\Documents\Trading\Trading_Data\Stock_Prices\CGNX.O#Cognex Ord Shs (D).csv" has started.
Information: 0x402090DE at Update Table Stocks, Stock CSV Files [12]: The total number of data rows processed for file "C:\Users\Me\Documents\Trading\Trading_Data\Stock_Prices\CGNX.O#Cognex Ord Shs (D).csv" is 1303.
Information: 0x402090DD at Update Table Stocks, Stock CSV Files [12]: The processing of file "C:\Users\Me\Documents\Trading\Trading_Data\Stock_Prices\CGNX.O#Cognex Ord Shs (D).csv" has ended.
Information: 0x402090DC at Update Table Stocks, Stock CSV Files [12]: The processing of file "C:\Users\Me\Documents\Trading\Trading_Data\Stock_Prices\CGON.O#CG Oncology Ord Shs (D).csv" has started.
Information: 0x402090DE at Update Table Stocks, Stock CSV Files [12]: The total number of data rows processed for file "C:\Users\Me\Documents\Trading\Trading_Data\Stock_Prices\CGON.O#CG Oncology Ord Shs (D).csv" is 197.
Information: 0x402090DD at Update Table Stocks, Stock CSV Files [12]: The processing of file "C:\Users\Me\Documents\Trading\Trading_Data\Stock_Prices\CGON.O#CG Oncology Ord Shs (D).csv" has ended.
Information: 0x402090DC at Update Table Stocks, Stock CSV Files [12]: The processing of file "C:\Users\Me\Documents\Trading\Trading_Data\Stock_Prices\CGUS.K#Capital Group Core Equity ETF (D).csv" has started.
Error: 0xC02020C4 at Update Table Stocks, Stock CSV Files [12]: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.
Error: 0xC0047038 at Update Table Stocks, SSIS.Pipeline: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on Stock CSV Files returned error code 0xC02020C4. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.
Information: 0x40043008 at Update Table Stocks, SSIS.Pipeline: Post Execute phase is beginning.
Information: 0x402090DD at Update Table Stocks, Stock CSV Files [12]: The processing of file "C:\Users\Me\Documents\Trading\Trading_Data\Stock_Prices\CGUS.K#Capital Group Core Equity ETF (D).csv" has ended.
Information: 0x4004300B at Update Table Stocks, SSIS.Pipeline: "Update Stocks Table" wrote 996776 rows.
Information: 0x40043009 at Update Table Stocks, SSIS.Pipeline: Cleanup phase is beginning.
Task failed: Update Table Stocks
Warning: 0x80019002 at Update Stock Table: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (6) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
Warning: 0x80019002 at Package: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (6) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "C:\Users\Me\Documents\Trading\SSIS\ Analysis Toolkit SSIS v4\ Analysis Toolkit SSIS\Package.dtsx" finished: Failure.
The program '[13100] DtsDebugHost.exe: DTS' has exited with code 0 (0x0). -
Might be a driver issue. Are you using an OLEDB driver or something else? 32 bit or 64 bit?
-
I tried using C# to load them. Still getting errors (but different this time).
Errors:
SSIS package "C:\Users\Me\Documents\Trading\SSIS\Analysis Toolkit SSIS v4\Analysis Toolkit SSIS\Package.dtsx" starting.
Information: 0x0 at Update Table Stocks v2: Message: Incorrect syntax near 's'.
Unclosed quotation mark after the character string ' AS TIME_STAMP
FROM #TEMP_TABLE'.
Information: 0x0 at Update Table Stocks v2: Rollback successful: C:\Users\Me\Documents\Trading\Trading_Data\Stock_Prices\BCO#Brink's Ord Shs (D).csv
Information: 0x0 at Update Table Stocks v2: Message: Incorrect syntax near 's'.
Unclosed quotation mark after the character string ' AS TIME_STAMP
FROM #TEMP_TABLE'.
Information: 0x0 at Update Table Stocks v2: Rollback successful: C:\Users\Me\Documents\Trading\Trading_Data\Stock_Prices\BFOR.K#Barron's 400 ETF (D).csv
Information: 0x0 at Update Table Stocks v2: Message: Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 2, column 4 (DATE).
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".
Information: 0x0 at Update Table Stocks v2: Rollback successful: C:\Users\Me\Documents\Trading\Trading_Data\Stock_Prices\CDEI.K#Calvert US Large-Cap Diversity, Eqty & Inc Idx ETF (D).csv
Information: 0x0 at Update Table Stocks v2: Message: Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 2, column 4 (DATE).
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".
Information: 0x0 at Update Table Stocks v2: Rollback successful: C:\Users\Me\Documents\Trading\Trading_Data\Stock_Prices\HRTS.O#Tema GLP-1, Obesity & Cardiometabolic ETF (D).csv
Information: 0x0 at Update Table Stocks v2: Message: Incorrect syntax near 's'.
Unclosed quotation mark after the character string ' AS TIME_STAMP
FROM #TEMP_TABLE'.
Information: 0x0 at Update Table Stocks v2: Rollback successful: C:\Users\Me\Documents\Trading\Trading_Data\Stock_Prices\KSS#Kohl's Ord Shs (D).csv
Information: 0x0 at Update Table Stocks v2: Message: Incorrect syntax near 'Ord'.
Unclosed quotation mark after the character string ' AS TIME_STAMP
FROM #TEMP_TABLE'.
Information: 0x0 at Update Table Stocks v2: Rollback successful: C:\Users\Me\Documents\Trading\Trading_Data\Stock_Prices\LE.O#Lands' End Ord Shs (D).csv
Information: 0x0 at Update Table Stocks v2: Message: Incorrect syntax near 's'.
Unclosed quotation mark after the character string ' AS TIME_STAMP
FROM #TEMP_TABLE'.
Information: 0x0 at Update Table Stocks v2: Rollback successful: C:\Users\Me\Documents\Trading\Trading_Data\Stock_Prices\LOW#Lowe's Companies Ord Shs (D).csv
Information: 0x0 at Update Table Stocks v2: Message: Incorrect syntax near 's'.
Unclosed quotation mark after the character string ' AS TIME_STAMP
FROM #TEMP_TABLE'.
Information: 0x0 at Update Table Stocks v2: Rollback successful: C:\Users\Me\Documents\Trading\Trading_Data\Stock_Prices\MAMA.O#Mama's Creations Ord Shs (D).csv
Information: 0x0 at Update Table Stocks v2: Message: Incorrect syntax near 's'.
Unclosed quotation mark after the character string ' AS TIME_STAMP
FROM #TEMP_TABLE'.
Information: 0x0 at Update Table Stocks v2: Rollback successful: C:\Users\Me\Documents\Trading\Trading_Data\Stock_Prices\MCD#McDonald's Corp (D).csv
Information: 0x0 at Update Table Stocks v2: Message: Incorrect syntax near 's'.
Unclosed quotation mark after the character string ' AS TIME_STAMP
FROM #TEMP_TABLE'.
Information: 0x0 at Update Table Stocks v2: Rollback successful: C:\Users\Me\Documents\Trading\Trading_Data\Stock_Prices\MCO#Moody's Ord Shs (D).csv
Information: 0x0 at Update Table Stocks v2: Message: Bulk load data conversion error (truncation) for row 2, column 5 (OPEN).
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".
Information: 0x0 at Update Table Stocks v2: Rollback successful: C:\Users\Me\Documents\Trading\Trading_Data\Stock_Prices\MULN.O#Mullen Automotive Ord Shs (D).csv
Information: 0x0 at Update Table Stocks v2: Message: Incorrect syntax near 'Shares'.
Unclosed quotation mark after the character string ' AS TIME_STAMP
FROM #TEMP_TABLE'.
Information: 0x0 at Update Table Stocks v2: Rollback successful: C:\Users\Me\Documents\Trading\Trading_Data\Stock_Prices\OGIG.K#ALPS O'Shares Global Internet Giants ETF (D).csv
Information: 0x0 at Update Table Stocks v2: Message: Incorrect syntax near 's'.
Unclosed quotation mark after the character string ' AS TIME_STAMP
FROM #TEMP_TABLE'.
Information: 0x0 at Update Table Stocks v2: Rollback successful: C:\Users\Me\Documents\Trading\Trading_Data\Stock_Prices\OLLI.O#Ollie's Bargain Outlet Holdings Ord Shs (D).csv
Information: 0x0 at Update Table Stocks v2: Message: Incorrect syntax near 'Shares'.
Unclosed quotation mark after the character string ' AS TIME_STAMP
FROM #TEMP_TABLE'.
Information: 0x0 at Update Table Stocks v2: Rollback successful: C:\Users\Me\Documents\Trading\Trading_Data\Stock_Prices\OUSA.K#ALPS O'Shares US Quality Dividend ETF (D).csv
Information: 0x0 at Update Table Stocks v2: Message: Incorrect syntax near 'Shares'.
Unclosed quotation mark after the character string ' AS TIME_STAMP
FROM #TEMP_TABLE'.
Information: 0x0 at Update Table Stocks v2: Rollback successful: C:\Users\Me\Documents\Trading\Trading_Data\Stock_Prices\OUSM.K#ALPS O'Shares US Small-Cap Quality Dividend ETF (D).csv
Information: 0x0 at Update Table Stocks v2: Message: Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 2, column 4 (DATE).
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".
Information: 0x0 at Update Table Stocks v2: Rollback successful: C:\Users\Me\Documents\Trading\Trading_Data\Stock_Prices\PEO#Adams Natural Resources Fund, Inc. (D).csv
Information: 0x0 at Update Table Stocks v2: Message: Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 2, column 4 (DATE).
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".
Information: 0x0 at Update Table Stocks v2: Rollback successful: C:\Users\Me\Documents\Trading\Trading_Data\Stock_Prices\QQQ.O#Invesco QQQ Trust, Series 1 (D).csv
Information: 0x0 at Update Table Stocks v2: Message: Incorrect syntax near 's'.
Unclosed quotation mark after the character string ' AS TIME_STAMP
FROM #TEMP_TABLE'.
Information: 0x0 at Update Table Stocks v2: Rollback successful: C:\Users\Me\Documents\Trading\Trading_Data\Stock_Prices\TLYS.K#Tilly's Ord Shs Class A (D).csv
SSIS package "C:\Users\Me\Documents\Trading\SSIS\Analysis Toolkit SSIS v4\Analysis Toolkit SSIS\Package.dtsx" finished: Success.
The program '[28344] DtsDebugHost.exe: DTS' has exited with code 0 (0x0).SP to load the CSV files:
ALTER PROCEDURE [dbo].[UpdateStocks] (@FILENAME varchar(200), @RECORD_ADD_DATE datetime)
AS
DECLARE @FILEPATH VARCHAR(200)
SET @FILEPATH = @FILENAME
DECLARE @RECORD_ADD_DT varchar(26)
SET @RECORD_ADD_DT = convert(varchar(26),@RECORD_ADD_DATE,121)
DROP TABLE IF EXISTS #TEMP_TABLE;
CREATE TABLE #TEMP_TABLE
(
[SYMBOL] [nchar](10) NOT NULL,
[INTERVAL] [nchar](1) NOT NULL,
[NAME] [nvarchar](100) NOT NULL,
[DATE] [date] NOT NULL,
[OPEN] [numeric](12, 5) NOT NULL,
[HIGH] [numeric](12, 5) NOT NULL,
[LOW] [numeric](12, 5) NOT NULL,
[CLOSE] [numeric](12, 5) NOT NULL,
[VOLUME] [numeric](18, 0) NOT NULL
);
Exec ( 'BULK INSERT #TEMP_TABLE
FROM ''' +
@FILEPATH +
''' WITH (FIRSTROW = 2, FIELDTERMINATOR = '','', ROWTERMINATOR = ''\n'', MAXERRORS = 0);
INSERT INTO DBO.Stocks2
SELECT *, ''' + @RECORD_ADD_DT + ''' AS TIME_STAMP
FROM #TEMP_TABLE'
)
DROP TABLE #TEMP_TABLEMost of the files got loaded but a few didn't. I was able to manually import the failed files in a new table using Tasks -> Import Flat File wizard. The file got loaded as new table. If I try loading the file using Tasks -> Import Data then it got loaded too into Stocks2 table. One thing that looks different is the conversions. Notice how the wizard is converting:
What is wrong with the my loading logic? I am super stumped on this. Any suggestions are greatly appreciated.
-
I figured out the problem. I just don't know the best way to fix it.
The problem is that some files have an apostrophe in the name:
Information: 0x0 at Update Table Stocks v2: Message: Incorrect syntax near 's'.
Unclosed quotation mark after the character string ' AS TIME_STAMP
FROM #TEMP_TABLE'.
Information: 0x0 at Update Table Stocks v2: Rollback successful: C:\Users\Me\Documents\Trading\Trading_Data\Stock_Prices\BCO#Brink's Ord Shs (D).csvIt is somehow messing up the BULK insert command:
ALTER PROCEDURE [dbo].[UpdateStocks] (@FILENAME varchar(200), @RECORD_ADD_DATE datetime)
AS
DECLARE @FILEPATH VARCHAR(200)
SET @FILEPATH = @FILENAME
DECLARE @RECORD_ADD_DT varchar(26)
SET @RECORD_ADD_DT = convert(varchar(26),@RECORD_ADD_DATE,121)
DROP TABLE IF EXISTS #TEMP_TABLE;
/*
CREATE TABLE #TEMP_TABLE
(
[SYMBOL] [nchar](10) NOT NULL,
[INTERVAL] [nchar](1) NOT NULL,
[NAME] [nvarchar](100) NOT NULL,
[DATE] [date] NOT NULL,
[OPEN] [numeric](12, 5) NOT NULL,
[HIGH] [numeric](12, 5) NOT NULL,
[LOW] [numeric](12, 5) NOT NULL,
[CLOSE] [numeric](12, 5) NOT NULL,
[VOLUME] [numeric](18, 0) NOT NULL
);
*/
CREATE TABLE #TEMP_TABLE
(
[SYMBOL] [varchar](10) NOT NULL,
[INTERVAL] [varchar](10) NOT NULL,
[NAME] [varchar](100) NOT NULL,
[DATE] [varchar](50) NOT NULL,
[OPEN] [varchar](50) NOT NULL,
[HIGH] [varchar](50) NOT NULL,
[LOW] [varchar](50) NOT NULL,
[CLOSE] [varchar](50) NOT NULL,
[VOLUME] [varchar](50) NOT NULL
);
Exec ( 'BULK INSERT #TEMP_TABLE
FROM ''' +
@FILEPATH +
''' WITH (FIRSTROW = 2, FIELDTERMINATOR = '','', ROWTERMINATOR = ''\n'', MAXERRORS = 0);
INSERT INTO DBO.Stocks2
SELECT *, ''' + @RECORD_ADD_DT + ''' AS TIME_STAMP
FROM #TEMP_TABLE'
)
DROP TABLE #TEMP_TABLEIs there a way to fix this within the SP above? The only option I can think of is to write a PowerShell script that removes the apostrophe in the filename before SSIS loads the file. If I can somehow fix the above SP that is much preferred. I am open to all suggestions.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply