Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Import CSV Using SSIS - Column Format Issue

Posted on 2013-01-18
17
Medium Priority
?
1,567 Views
Last Modified: 2016-02-10
Hello All,
I am trying to import a simple CSV file into SQL Server using the SSIS. I have been unsuccessful so far. Spent hours but cant figure out. Please assist.

Please Check the attached Word Document for details and Screenshots


Step 1

Created tables with this query…

CREATE TABLE [dbo].[TestCSVUpload](
[OrderDate] [nvarchar](255) NULL,
      [Region] [nvarchar](255) NULL,
      [Rep] [nvarchar](255) NULL,
      [Item] [nvarchar](255) NULL,
      [Units] [numeric](18, 0) NULL,
      [Cost] [numeric](18, 0) NULL,
      [Total] [numeric](18, 0) NULL
) ON [PRIMARY]

GO


Step2
Placed [testIncomingTbl.csv] in a certain folder for connection string (attached the csv file in question)
C:\incomingCSVFolder


Step3
a. In SSIS, added a Flat File Source
Created connection manager for it in SSIS like this>>
 


Step4:
Create OLE DB Destination in SSIS


Step5:
Connect the green arrow from Flat File Source to this [OLE DB Destination]
 


Step6:
Under the OLE DB Destination Editor, assigned the table as the target destination
 
Now this is what I am getting now >>>>
 
Spent hours trying to change the column formats but to no avail, this keeps showing up.

Provided the source CSV File in the question and also given the Query need to create a table in server for getting this csv data set.

Not sure where I am missing anything…

Thanks
R
csvimportIssue.docx
testIncoming.csv
0
Comment
Question by:Rayne
  • 10
  • 5
  • 2
17 Comments
 
LVL 11

Expert Comment

by:Simone B
ID: 38794739
How did you try to convert the data? It seems what you need is a Data Conversion task immediately after the flat file source (see attached). You should be able to use it to convert the strings.

However, wouldn't you want your OrderDate in the SQL table to be of datetime data type?
Capture.JPG
0
 
LVL 40

Expert Comment

by:lcohan
ID: 38794751
If it is truly only a csv you could do it via SQL code directly like in example below - just adapt to your table/csv structure and keep in mind the csv path is relative to the SQL Server box not client:

--IMPORT
CREATE TABLE CSVTest
(ID INT,
FirstName VARCHAR(40),
LastName VARCHAR(40),
BirthDate SMALLDATETIME)
GO

--Create CSV file in drive C: with name csvtest.txt with following content. The location of the file is C:\csvtest.txt
1,James,Smith,19750101
2,Meggie,Smith,19790122
3,Robert,Smith,20071101
4,Alex,Smith,20040202


--Now run following script to load all the data from CSV to database table. If there is any error in any row it will be not inserted but other rows will be inserted.
BULK
INSERT CSVTest
FROM 'c:\csvtest.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO
--Check the content of the table.
SELECT *
FROM CSVTest
GO
--Drop the table to clean up database.
SELECT *
FROM CSVTest
GO

--EXPORT data to CSV
--with BCP:

exec master..xp_cmdshell N'sqlcmd -E -Q"bcp "select ''ProductID'', ''Name'', ''ProductNumber'' union all select convert(varchar(10), ProductID), Name, ProductNumber from AdventureWorks2008R2.Production.Product" queryout "c:\temp\product.csv" -c -T >nul'--, no_output

--with sqlcmd
exec master..xp_cmdshell N'sqlcmd -E -Q"set nocount on;select * from DBname.dbo.table_name" -h-1 -s"," -W -o"c:\outputTEST.csv"'--, no_output


GO
0
 

Author Comment

by:Rayne
ID: 38794791
Hello to All Experts,

Thanks for your help so far. Greatly appreciated. I am newbie to SSIS and Server stuff so please bear with me :(

Buttercup1-
Can you show me how to insert that conversion thing in SSIS?


Icohan –

“csv path is relative to the SQL Server box not client”
I am getting this error when trying to do the bulk insert….so how you do the above thing as you mentioned?
The CSV file is stored in my C drive

Error:
Cannot bulk load because the file “C:\incomingCSVFolder" could not be opened. Operating system error code 5(Access is denied.).
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

Author Comment

by:Rayne
ID: 38794795
I am an admin in the DB
0
 
LVL 40

Expert Comment

by:lcohan
ID: 38794805
"The CSV file is stored in my C drive"


Is the CSV file from C:\incomingCSVFolder ON the SQL Server computer? Or you run it against localy installed SQLServer?
If not you must put it in there...
If you run the SQL code on the SQL server computer make sure the account (sql login) running the query has access to that folder
0
 
LVL 11

Accepted Solution

by:
Simone B earned 2000 total points
ID: 38794873
See attached Word doc. I replicated your scenario on my test server, and it was successful.

Experts-Exchange---Import-CSV-us.docx
0
 

Author Comment

by:Rayne
ID: 38794901
Is the CSV file from C:\incomingCSVFolder ON the SQL Server computer? Or you run it against localy installed SQLServer?
Not sure if I unserstood. But I have a sql server installed in my computer. When I open the SSMS, I just login to the Main server using windows authentication and do my stuff there.The CSV file is my local C Drive.

If not you must put it in there...
If you run the SQL code on the SQL server computer make sure the account (sql login) running the query has access to that folder
Yes my computer login has access to that folder.
0
 

Author Comment

by:Rayne
ID: 38794908
I also created a Data conversion object but have question regarding that. Check attached,
Thanks
csvimportIssue201.docx
0
 
LVL 11

Expert Comment

by:Simone B
ID: 38794917
"I also created a Data conversion object but have question regarding that."

1. Change the data types and lengths to match what is showing in my last attachment.

2. Modify the Mappings in your destination as shown in my last attachment.
0
 

Author Comment

by:Rayne
ID: 38794935
Made chnges to conversion object but still issue is there
conversionChanges.png
0
 
LVL 11

Expert Comment

by:Simone B
ID: 38794945
Please have another look at the document I sent.
0
 

Author Comment

by:Rayne
ID: 38795045
Hello Buttercup1, i have attached my observations.
Its only writing 12 rows instead of 43 rows to the DB table
cantWriteAll.docx
0
 

Author Comment

by:Rayne
ID: 38795056
not sure whats causing it....
0
 

Author Comment

by:Rayne
ID: 38795109
is the data bad or something else?
0
 

Author Comment

by:Rayne
ID: 38795228
ok so far you helped me to create the conversion object. So I rewarded you the point. But now the table addition is happening only for few rows, not the entire set of source rows, why?
New Question:

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_28001147.html
0
 

Author Comment

by:Rayne
ID: 38795234
create the destination table like this


CREATE TABLE [dbo].[TestCSVUpload](

      [OrderDate][datetime] NULL,
      [Region][nvarchar](255) NULL,
      [Rep][nvarchar](255) NULL,
      [Item][nvarchar](255) NULL,
      [Units][numeric](18, 0) NULL,
      [Cost][numeric](18, 0) NULL,
      [Total][numeric](18, 0) NULL,

      
) ON [PRIMARY]

GO
0
 
LVL 11

Expert Comment

by:Simone B
ID: 38801199
My sincere apologies, I was away for the weekend and not checking email. But I see you had this answered in another post. I hope it's all working for you now.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

824 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question