SSIS Support importing CSV(Comma Separated Values) files into SQL Server. Using the Data flow task we can achieve this.
Drag the Data Flow in Control Panel. Double Click on Data Flow Task. It will go to Data Flow task tab.
Now in Data Flow Task. Right Click ----Select SSIS ToolBox---Select Source Assistant
Click New. Select Flat File as Source Type and Click New.
Select Format as delimited. Select the check box Column names in the first data row if the csv file contains the columns names in the first row. For changing Column names ,Data Type and OutputColumnWidth select the Columns tab as shown below.
Run the Package as administrator by pressing F5 or Green button.
Select Input and output Properties tab.
Expand the Flat File Source Output . Expand the Output Columns .
Select respective column and Go to Data Type Properties section then change the Length.
Drag the Data Flow in Control Panel. Double Click on Data Flow Task. It will go to Data Flow task tab.
Now in Data Flow Task. Right Click ----Select SSIS ToolBox---Select Source Assistant
Select Format as delimited. Select the check box Column names in the first data row if the csv file contains the columns names in the first row. For changing Column names ,Data Type and OutputColumnWidth select the Columns tab as shown below.
Some time you may get error as below.
[Flat File Source [2]] Error: Data conversion failed. The
data conversion for column "Folder Path" returned status value 4 and
status text "Text was truncated or one or more characters had no match in
the target code page.".
[Flat File Source [2]] Error: The "Flat File
Source.Outputs[Flat File Source Output].Columns[Folder Path]" failed
because truncation occurred, and the truncation row disposition on "Flat
File Source.Outputs[Flat File Source Output].Columns[Folder Path]"
specifies failure on truncation. A truncation error occurred on the specified
object of the specified component.
[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on Flat File Source
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.
The above error can be solved by changing the outputcolumnwidth in Flat File Connection Manager Editor or using the below steps.
Right Click on the Flat File Source ---- Click on Show Advanced Editor
Select Input and output Properties tab.
Expand the Flat File Source Output . Expand the Output Columns .
Select respective column and Go to Data Type Properties section then change the Length.
Build and run the project. The data export from CSV to SQL Server Destination.
No comments:
Post a Comment