ssisvisual studio 2010visual studio 2012
I am having the following issue of SSIS AND XLSM load time issue Visual Studio 2010
I want to speed this up
I am reading a .xlsm as my excel source
I have noticed its loading / reading every row of the excel…even though they are blank
e.g I only have 300 + rows of data at the moment, but it loading every row even though they are blank e.g going pass the 400 row where there is no data yet
I don't want it reading 200,000+ rows before it actually loads the 300
how do I stop this please team
My original answer was working (changing int to nvarchar) but I ran into another column which contained dates in the excel source file where some cells contained the string "NULL" (I did not want to have dates in a NVARCHAR column.) When SSIS got to this date column it was generating an error because it could not convert the string "NULL" to a date. It was not properly reading the "NULL" as a NULL value, instead it was reading it as a string. The way I was able to resolve this issue is by adding a derived column component to the package that replaced the date field by using the following expression
(DT_WSTR,255)Auth_from_date == "NULL" ? NULL(DT_WSTR,50) : (DT_WSTR,255)Auth_from_date
this is just an IF statement that will change the string "NULL" to an actual NULL value. The date is then passed thru a data conversion component and converted into DATETIME format.
And so to force the same session to be used, in your connection manager, under the Properties settings, you can set the RetainSameConnection to True to force a single connection for your package versus using a pool.
Best Answer
I have resolved this by using an sql command in the excel properties, and only import where ID IS NOT NULL x
thank team for ideas x