Dynamic Flat File Destination with a Variable in SSIS

A nice little code snippet for your cookbook πŸ™‚

In case you would like to export data via SSIS to a dynamic Flat File Destination (which includes the current date and time), like for example:

C:\Temp\CSV-File-Example-201309061121.csv

You might want to use this expression for a Variable value or directly for your ConnectionString (Flat File Connection):


"C:\\Temp\\CSV-File-Example-" +
(DT_STR,4,1252) DatePart("Year",getdate()) +
Right("0" + (DT_STR,4,1252) DatePart("Month",getdate()),2) +
Right("0" + (DT_STR,4,1252) DatePart("Day",getdate()),2) +
Right("0" + (DT_STR,4,1252) DatePart("Hour",getdate()),2) +
Right("0" + (DT_STR,4,1252) DatePart("Minute",getdate()),2) + ".csv"

SSIS-Dynamic-Flat-File-Destination-FileName

SSIS Excel Source error -”Text was truncated or one or more characters had no match in the target code page.”

How to increase the number of rows in the Excel file that SSIS validates on metadata:

The Jet OLE DB provider reads a registry key to determine how many rows are to be read to guess the type of the source column. The registry setting is:

HKLM\Software\Microsoft\Jet\4.0\Engines\Excel\TypeGuessRows

By default, the value for this key is 8. Hence, the provider scans the first 8 rows of the source data to determine the data types for the columns. If any field looks like text and the length of data is more than 255 characters, the column is typed as a memo field. So, if there is no data with a length greater than 255 characters in the first 8 rows of the source, Jet cannot accurately determine the nature of the data type.

The valid range of values for the TypeGuessRows key is 0 to 16. However, if the value is 0, the number of source rows scanned is 16384. Keep in mind that a value of zero may cause a small performance hit if the source file is very large.

Source

How to:

To change the value of TypeGuessRows, use these steps:
On the Start menu, click Run. In the Run dialog box, type Regedt32, and then click OK.
Open the following key in the Registry editor:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel
Note For 64-bit systems, the corresponding key is as follows:

HKLM\SOFTWARE\wow6432node\microsoft\jet\4.0\engines\excel
Double-click TypeGuessRows.
In the DWORD editor dialog box, click Decimal under Base. Type a value between 0 and 16, inclusive, for Value data.
Click OK, and then exit the Registry Editor.
A second way to work around this problem (without modifying the registry) is to make sure that rows with fields, which have data 255 characters or greater, are present in the first 8 rows of the source data file.

This solution may also be used for ‘Mixed data types in Excel column’.