Exported CSV file from SSIS has empty first row when open in Excel

Question

Hello everyone,

I read the suggested questions while posting and most are talking about the opposite of what I am trying to do.

Every time I export a flat file (CSV) using SSIS, the first row is empty. When I open the file in NotePad I noticed that the first row has a space or empty.

This is killing me, I tested the mapping, I have APPEND checked because I need the file to update. So why is there an empty first row?

Finally, what if I need the file to export everyday, lets say samplefile_currentdate.csv, should I use BID instead? This way the file is not appending but rather creating different versions.

Thanks for reading!

Synth

Answer 1

There is an option check box in Import/Export menu to specify first row as column name , have you tried it?

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Answer 2

Your last question is to use a dynamic file name:  http://www.sqlnerd.com/ssis_dynamic_dates.htm

---

Russel Loski, MCT, MCITP Business Intelligence Developer and Database Developer 2008 Blog: http://www.bidn.com/blogs/RussLoski/ Twitter: @sqlmovers

Answer 3

There is an option check box in Import/Export menu to specify first row as column name , have you tried it?

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Yes, I checked that box. I think the problem is when you select APPEND, the data is placed beginning on the next(new) line. However, when I select DELETE, the data starts from the top. There are three settings in the MAPPING option, Create File, Delete file and Append file.

Thanks

Answer 4

Your last question is to use a dynamic file name:  http://www.sqlnerd.com/ssis_dynamic_dates.htm

---

Russel Loski, MCT, MCITP Business Intelligence Developer and Database Developer 2008 Blog: http://www.bidn.com/blogs/RussLoski/ Twitter: @sqlmovers

Which version of BID does the link reference?

Just curious how did you managed to change the scope of the variable? I cannot rename the scope.

I am also confused by the dynamic file naming because you are asked to provide a file name when setting connection properties for the destination.

Thanks!

Answer 5

I believe that this principle will work with all versions of SSIS (it works for 2005 to 2008 R2).

You have to create the variable with none of the tasks selected, otherwise the scope of the variable is set to the task, not the package.  In SSIS 2012 this has been fixed so that the package is the default scope for variables.

You create the flat file connection normally as part of creating the flat file destination.  You assign a specific file name to the file.  After you are done mapping columns, you can then go back and change the properties of the flat file connection manager.  You go to the properties window for the connection manager and find the expressions property.  Click on the elipsis button and you can then choose the property that you want to be dynamic.  In this case you select the Connection String.  You set the value to the expression that you would like.

---

Russel Loski, MCT, MCITP Business Intelligence Developer and Database Developer 2008 Blog: http://www.bidn.com/blogs/RussLoski/ Twitter: @sqlmovers

Answer 6

I believe that this principle will work with all versions of SSIS (it works for 2005 to 2008 R2).

You have to create the variable with none of the tasks selected, otherwise the scope of the variable is set to the task, not the package.  In SSIS 2012 this has been fixed so that the package is the default scope for variables.

You create the flat file connection normally as part of creating the flat file destination.  You assign a specific file name to the file.  After you are done mapping columns, you can then go back and change the properties of the flat file connection manager.  You go to the properties window for the connection manager and find the expressions property.  Click on the elipsis button and you can then choose the property that you want to be dynamic.  In this case you select the Connection String.  You set the value to the expression that you would like.

---

Russel Loski, MCT, MCITP Business Intelligence Developer and Database Developer 2008 Blog: http://www.bidn.com/blogs/RussLoski/ Twitter: @sqlmovers

I am lost right here:

You go to the properties window for the connection manager and find the expressions property.  Click on the elipsis button and you can then choose the property that you want to be dynamic.  In this case you select the Connection String.  You set the value to the expression that you would like.

I have already added the expression(code) to the variable, what value am I setting? Where do I change the file name? When I click the Expressions, the connectionString string already contained the variable and the expression.

I get this error when I run the package:

Task failed: Data Flow Task
Warning: 0x80019002 at Package: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED.  The Execution method succeeded, but the number of errors raised (2) 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 "Package.dtsx" finished: Failure.

I am sure I am missing something.

Thanks for your patience!

Answer 7

There is normally another error associated with this error that gives more information.  What is that error?

---

Russel Loski, MCT, MCITP Business Intelligence Developer and Database Developer 2008 Blog: http://www.bidn.com/blogs/RussLoski/ Twitter: @sqlmovers

Answer 8

There is normally another error associated with this error that gives more information.  What is that error?

---

Russel Loski, MCT, MCITP Business Intelligence Developer and Database Developer 2008 Blog: http://www.bidn.com/blogs/RussLoski/ Twitter: @sqlmovers

This is the entire error message:

SSIS package "Package.dtsx" starting.
Information: 0x4004300A at Data Flow Task, SSIS.Pipeline: Validation phase is beginning.
Information: 0x4004300A at Data Flow Task, SSIS.Pipeline: Validation phase is beginning.
Warning: 0x80049304 at Data Flow Task, SSIS.Pipeline: Warning: Could not open global shared memory to communicate with performance DLL; data flow performance counters are not available.  To resolve, run this package as an administrator, or on the system's console.
Warning: 0x80047076 at Data Flow Task, SSIS.Pipeline: The output column "USER_ID" (23) on output "OLE DB Source Output" (11) and component "OLE DB Source" (1) is not subsequently used in the Data Flow task. Removing this unused output column can increase Data Flow task performance.
Information: 0x40043006 at Data Flow Task, SSIS.Pipeline: Prepare for Execute phase is beginning.
Information: 0x40043007 at Data Flow Task, SSIS.Pipeline: Pre-Execute phase is beginning.
Information: 0x402090DC at Data Flow Task, Flat File Destination [16]: The processing of file "20120506.csv" has started.
Warning: 0x80070005 at Data Flow Task, Flat File Destination [16]: Access is denied.
Error: 0xC020200E at Data Flow Task, Flat File Destination [16]: Cannot open the datafile "20120506.csv".
Error: 0xC004701A at Data Flow Task, SSIS.Pipeline: component "Flat File Destination" (16) failed the pre-execute phase and returned error code 0xC020200E.
Information: 0x40043008 at Data Flow Task, SSIS.Pipeline: Post Execute phase is beginning.
Information: 0x402090DD at Data Flow Task, Flat File Destination [16]: The processing of file "20120506.csv" has ended.
Information: 0x4004300B at Data Flow Task, SSIS.Pipeline: "component "Flat File Destination" (16)" wrote 0 rows.
Information: 0x40043009 at Data Flow Task, SSIS.Pipeline: Cleanup phase is beginning.
Task failed: Data Flow Task
Warning: 0x80019002 at Package: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED.  The Execution method succeeded, but the number of errors raised (2) 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 "Package.dtsx" finished: Failure.

Answer 9

You will need to set the full path for the file.  Otherwise it is uncertain where the file 20120506.csv is being created.  There really is no certainty as to where the file is created.  I would put the path in one variable @Path (say "C:\Files\") and then use the expression "@Path + "20120506.csv" (or however you create the date based name) in the expression for your ConnectionString.

---

Russel Loski, MCT, MCITP Business Intelligence Developer and Database Developer 2008 Blog: http://www.bidn.com/blogs/RussLoski/ Twitter: @sqlmovers

Answer 10

You will need to set the full path for the file.  Otherwise it is uncertain where the file 20120506.csv is being created.  There really is no certainty as to where the file is created.  I would put the path in one variable @Path (say "C:\Files\") and then use the expression "@Path + "20120506.csv" (or however you create the date based name) in the expression for your ConnectionString.

---

Russel Loski, MCT, MCITP Business Intelligence Developer and Database Developer 2008 Blog: http://www.bidn.com/blogs/RussLoski/ Twitter: @sqlmovers

Okay I got the file part by modifying your reference like this:

@[User::PATH] + "C:\\Users\\Workstation\\Desktop\\main folder\\file folder\\file_name_" +
Right("0" + (DT_STR,4,1252) DatePart("m",getdate()),2) +
Right("0" + (DT_STR,4,1252) DatePart("d",getdate()),2) + 
(DT_STR,4,1252) DatePart("yyyy",getdate()) + ".csv"

I placed the entire directory in the expression:

Now I need to figure why the file is missing the fist column, the column is coming out as column zero (0).

And just for the sake of knowledge, what if I wanted to use the date and time on the file name? This way I do not have to overwrite the file if more than one export occurs in a day.

Thanks again. You have been extremely helpful!

Answer 11

My favorite expression:

(DT_WSTR,8)((10000 * YEAR( GETDATE())) 
+ (100 * MONTH( GETDATE())) 
+ DAY( GETDATE())) 
+ "_" + RIGHT(REPLICATE("0",2) 
+ (DT_WSTR,6)((10000 * DATEPART("Hh", GETDATE())) 
+ (100 * DATEPART("mi", GETDATE())) 
+ DATEPART("ss", GETDATE())),6) 
+ "." + (DT_WSTR,3)DATEPART("Ms", GETDATE())

---

Russel Loski, MCT, MCITP Business Intelligence Developer and Database Developer 2008 Blog: http://www.bidn.com/blogs/RussLoski/ Twitter: @sqlmovers

Answer 12

My favorite expression:

(DT_WSTR,8)((10000 * YEAR( GETDATE())) 
+ (100 * MONTH( GETDATE())) 
+ DAY( GETDATE())) 
+ "_" + RIGHT(REPLICATE("0",2) 
+ (DT_WSTR,6)((10000 * DATEPART("Hh", GETDATE())) 
+ (100 * DATEPART("mi", GETDATE())) 
+ DATEPART("ss", GETDATE())),6) 
+ "." + (DT_WSTR,3)DATEPART("Ms", GETDATE())

---

Russel Loski, MCT, MCITP Business Intelligence Developer and Database Developer 2008 Blog: http://www.bidn.com/blogs/RussLoski/ Twitter: @sqlmovers

GREAT! Thanks, if this is not too much to ask how can I make this a regular time instead of GMT or Military?

Answer 13

1) The scope of a SSIS varaible cannot be changed directly. You'll have to delete the mis-scoped variable and create it with the same name in the required scope.

2) Can you check that your data does not have a blank row i.e. all row values are NULL?

---

Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers.
Thanks!
Aalam | Blog (http://aalamrangi.wordpress.com)

Answer 14

1) The scope of a SSIS varaible cannot be changed directly. You'll have to delete the mis-scoped variable and create it with the same name in the required scope.

2) Can you check that your data does not have a blank row i.e. all row values are NULL?

---

Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers.
Thanks!
Aalam | Blog (http://aalamrangi.wordpress.com)

Thank you as well!

Synth