SSIS CSV File Failed To Load
-
Hi everyone
I am working on a SSIS package that outputs a SS table into a CSV file. The package works (ie the CSV is produced). However, the CSV file that gets created isn't being recognized by the application that is consuming the CSV. Here is what I did:
Sample 1 - Produced by SSIS -> failed to load
Sample 2 - I modified Sample 1 by removing a bunch of records -> file loaded successfully
The first thought that comes to my mind is that Sample 1 contained invisible characters that the consuming application did not like hence rejected the load. As soon as I deleted a bunch of records I also deleted those invisible characters.
My question...Have others had issues with CSV files produced by SSIS? If yes, how did you resolve it?
Another question...Does anyone know what is going on with my files?
Thank you
-
just to be sure you are indeed producing a Valid CSV
when you define your output file do you have the "text qualifier" set to " (double quote)?
on example below it is not set so the file will not be considered a valid CSV if any of their text fields contain particular characters.
-
This was removed by the editor as SPAM
-
thank you for the reply. I tried that and the consuming app still didn't recognize the CSV. I figured out what the problem is but I do not know how to fix it. The problem is as follows...
The consuming app cannot import ".000" b/c that isn't a proper number. If I replace ".000" with "0.000" then the import works. This is super weird. The SS table shows "0" which is correct value for that field. However, the CSV shows ".000". I am not sure why the 0 to the left of the decimal is gone. Below is a table definition:
CREATE TABLE [dbo].[StockTable](
[SYMBOL] [nchar](10) NOT NULL,
[INTERVAL] [nchar](1) NOT NULL,
[DATE] [date] NOT NULL,
[NAME] [nchar](40) NOT NULL,
[OPEN] [numeric](8, 4) NOT NULL,
[HIGH] [numeric](8, 4) NOT NULL,
[LOW] [numeric](8, 4) NOT NULL,
[CLOSE] [numeric](8, 4) NOT NULL,
[VOLUME] [numeric](8, 4) NOT NULL
) ON [PRIMARY]
GOThe problem is the Volume field. Sometimes the value can be 0 and that is totally fine. Below is the SP that copies the table from main table into a one that is formatted to meet the needs of the consuming app.
INSERT INTO DBO.AppStockTable
SELECT'SPX' AS SYMBOL,
'D' AS INTERVAL,
T1.TRADE_DATE AS [DATE],
'S&P 500' AS [NAME],
0 AS [OPEN],
9999 AS [HIGH],
0 AS [LOW],
T1.Calc1 AS [CLOSE],
T1.Calc2 AS VOLUME
FROMDBO.StockTable AS T1From this point on the SSIS takes over. I have a an OLE DB Source to connect to the table and then a Flat File Destination to output to a CSV.
How can I force the value of 0 to be 0 in the CSV and not be .000? The zeros to the right of the decimal are fine. The problem is there is no 0 to the left of the zero.
Any help you can offer is really appreciated.
Thank you
-
here is a picture of what I am seeing the preview option. It has the leading zero missing:
Yet, the OLE DB preview is showing the leading zero:
How do I fix this problem?
-
when generating csv files in SSIS (which I avoid every time I can - powershell easier for it) I normally do the conversion/formatting on the extract sql so that for SSIS all columns come as strings.
I also impose a strict date format to avoid regional settings putting the date on a format that the destination may not recognize.
so your select within your dataflow would be come the following (and never use a table as a source, always a sql statement - that will save you hassle down the line.
-- replace #stocktable with your own table
select SYMBOL
, INTERVAL
, convert(varchar(10), DATE, 121) as [DATE] -- user varchar(26) if its a datetime field
, NAME
, convert(varchar(10), [OPEN]) as [OPEN]
, convert(varchar(10), HIGH) as HIGH
, convert(varchar(10), LOW) as LOW
, convert(varchar(10), [close]) as [close]
, convert(varchar(10), VOLUME) as Volume
from #StockTable
-- if the application receiving the file requires the sign to be on the right of the value (some do) then the following will do it.
select SYMBOL
, INTERVAL
, convert(varchar(10), DATE, 121) as [DATE] -- user varchar(26) if its a datetime field
, NAME
, convert(varchar(10), abs([OPEN])) + iif([OPEN] >= 0, '','-') as [OPEN]
, convert(varchar(10), abs(HIGH)) + iif(HIGH >= 0, '','-') as HIGH
, convert(varchar(10), abs(LOW)) + iif(LOW >= 0, '','-') as LOW
, convert(varchar(10), abs([close])) + iif([close] >= 0, '','-') as [close]
, convert(varchar(10), abs(VOLUME)) + iif(VOLUME >= 0, '','-') as Volume
from #StockTableon a diff note - if your destination application is Excel then before changing code open the file with notepad and see if the leading zero is there or not - Excel will do its own formatting and it may seem wrong without that being the case
-
Thank you so much for the solution. It works perfectly.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply