Data Developers

When having table with large number of columns, it may be helpful to find if there are columns that doesn't contains any value. You can use the following query to achieve this (Just replace  'dbo.MyTable'  with your table name) Query steps: 1- Getting all columns count using one select query and giving an alias the original column name for each count column 2- Pivoting Result Table into a Temp table 3- Selecting Columns that have a Count = 0 from Temp Table DECLARE @ strTablename varchar ( 100 ) = 'dbo.MyTable' DECLARE @ strQuery varchar ( max ) = '' DECLARE @ strUnPivot as varchar ( max ) = ' UNPIVOT ([Count] for [Column] IN (' CREATE TABLE ## tblTemp ([ Column ] varchar ( 50 ), [ Count ] Int ) SELECT @ strQuery = ISNULL (@ strQuery , '' ) + 'Count([' + name + ']) as [' + name + '] ,' from sys . columns where object_id = object_id (@ strTablename ) and is_nullable

There are a lot of limitations when exporting data to an Excel File using SSIS. One of them that it is not allowed that the excel worksheet contains more then 255 columns. More excel limitations can be found in this  MSDN article To solve this issue we can do some workaround: 1. Create a DataFlow Task that export your data into a Flat File (csv, txt, ...) 2. Store your Destination FileName in a Variable 3. Create another Dataflow Task that convert your Flat File to an Excel File using a script task with a Function like the following Imports Microsoft . Office . Interop Public Sub ConvertCSVToExcel ( Fromcsv As String , Toxlsx As String ) Dim Exl As New Excel . Application () Try Dim wb1 As Excel . Workbook = Exl . Workbooks . Open ( Fromcsv , Format := 4 ) wb1 . SaveAs ( Toxlsx , FileFormat := XlFileFormat . xlOpenXMLWorkbook ) wb1 . Close () Exl . Quit () Catch ex As Except

General Info These are the default formats of datetimes datatypes (when converting from string) DT_DBDATE yyyy-mm-dd DT_FILETIME yyyy-mm-dd hh:mm:ss:fff DT_DBTIME hh:mm:ss DT_DBTIME2 hh:mm:ss[.fffffff] DT_DBTIMESTAMP yyyy-mm-dd hh:mm:ss[.fff] DT_DBTIMESTAMP2 yyyy-mm-dd hh:mm:ss[.fffffff] DT_DBTIMESTAMPOFFSET yyyy-mm-dd hh:mm:ss[.fffffff] [{+|-} hh:mm] Note: DT_DATE and DT_DBTIMESTAMP has the same SET method And i think that converting string to date is depending also on your current culture info More detailed information are found here https://msdn.microsoft.com/en-us/library/ms141036.aspx http://www.bidn.com/blogs/DevinKnight/ssis/1387/ssis-to-sql-server-data-type-translations Experiments : I didn't find any related article on this issue so i made the following experiments: SSIS Implicit datetime Conversion i created a SSIS package with a Dataflowtask. in this dataflowtask i created a Script Component (as a Source) and a Flat File Destin