Hi I get the excel files as sources, so i have to load them to sql server data base. In excel file it may contain any values (Like date columns can have (-)'s hyphens so while executing it may fail,To resolve this i can make error out put to ignore failure so it will give null, BUT , In some cases our testers will give some alphabets to as test case so it hase to redirect those rows to error file, in that case ignore row will not work for me) To make it work i want to convert each and every column in the source to uni code string , so I can use script component to check the values are containing the correct data type values, IS this good IDEA, IF Yes,For Excel source , i have tried to change the data type date type to unicode string but it is showing error in run time why? For single time purpose package Excel source is fine, but for long term use In many cases excel is not working well for me when i use it as source. is it good to work with Excel as source for long term packages Thanks RaviKiran.M

Dear Please use derived column between excel source and oledb destination and replace valu by – or hyphen and then use function in expression like date time int or string what ever you like ThanksRamesh Rathod

Hi ,Ramesh Rathod In the Excel source it self it is failing,the data is not coming up to derived column so i can't do it, I have used the Imex=1 property in the connection string, so it is returning nulls in that (-)'s hyphens , but for non date like alphabets also it is giving null, it is not what i require, i need to report those alphabet characters as error, so i want to convert the meta data in excel source adapter it self, how can I?, I have changed in the output columns to Unicode string for all columns still in the data source it self it is null as out put. my data is like Strnum currency 1111 11213 312 21312 34 234 3242 24 23423 24 42342 24 54245 24 25423 24 5234 24 252 24 52 24 542 24 ads asdf asd aesdf asd asdf asd asdf asdf asdf sadf asdf asdf sadf the character data it is showing nulls, I have tried by putting Imex=1 property in connectino string and removing Imex=1 property from connection string , in both cases it is showing nulls , in the data viewer which i have placed after Excel source. How can i achieve my task now? Thanks RaviKiran.M

Hi From long time i have this doubt can i get any help ? thanks

Dear so sorry I am busy with some other work so I can not give you ans Take one Sctipt Component task as Transformation type after Excel source Take your desired column with (- hypen ) as input parameter in script component Read this column in script task and replace your by hyphen with blank space and convert it by desired type cast . Eg. yourcolumnname.ToString.Replace("-", "") Then edit scriptcomponent task Select input and outputs then select output0 and add one output columns as you required then ok. Then edit script and give your variable value to this output column Row.youroutputcolumn = yourcolumnname Use this new out put column in your mapping or destination colun Again sorry for late ans ThanksRamesh Rathod

Hi ,Jian kang Thank you, that sloved my problem for now I have a doubt, when the meta data for the excel got created, I mean for the flat file source adapter, the meta data for the source will get created in the connection manager connection. we can directly change the meta data for the connection manager to reflect in the flat file source. I mean when we change the data type in flat file connection manager the it will get reflected in flat file source, But in case of excel, In excel connection manager it is not like so, why there is the deference in between flat file source, excel source? If i know this concept, it will be more helpful for my development. once again thank you.

Hi ,Jian kang Thank you, that sloved my problem for now I have a doubt, when the meta data for the excel got created, I mean for the flat file source adapter, the meta data for the source will get created in the connection manager connection. we can directly change the meta data for the connection manager to reflect in the flat file source. I mean when we change the data type in flat file connection manager the it will get reflected in flat file source, But in case of excel, In excel connection manager it is not like so, why there is the deference in between flat file source, excel source? If i know this concept, it will be more helpful for my development. once again thank you.

HI my data is like Strnum currency 1111 11213 312 21312 34 234 3242 24 23423 24 42342 24 54245 24 25423 24 5234 24 252 24 52 24 542 24 ads asdf asd aesdf asd asdf asd asdf asdf asdf sadf asdf asdf sadf the character data it is showing nulls, I have tried by putting Imex=1 property in connectino string and removing Imex=1 property from connection string , in both cases it is showing nulls , in the data viewer which i have placed after Excel source. How can i achieve my task now? TRY This and suggest me how to archive same data in data viewer. Thanks RaviKiran.M

HI my data is like Strnum currency 1111 11213 312 21312 34 234 3242 24 23423 24 42342 24 54245 24 25423 24 5234 24 252 24 52 24 542 24 ads asdf asd aesdf asd asdf asd asdf asdf asdf sadf asdf asdf sadf the character data it is showing nulls, I have tried by putting Imex=1 property in connectino string and removing Imex=1 property from connection string , in both cases it is showing nulls , in the data viewer which i have placed after Excel source. How can i achieve my task now? TRY This and suggest me how to archive same data in data viewer. Thanks RaviKiran.M Hi, Could you please elaborate a bit? Which version of Excel file do you have? If it’s Excel 97-2003, SSIS uses Jet to access the Excel files. By default, Jet scans the first 8 rows of your data to determine the type of each column. To fix it, please edit the registry to increase the TypeGuessRows DWORD value of one of the following registry keys to determine how many rows to scan in your data: For 32-bit Windows Excel 97 HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\3.5\Engines\Excel Excel 2000 and later versions HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel For 64-bit Windows Excel 97 HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Jet\3.5\Engines\Excel Excel 2000 and later versions HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Jet\4.0\Engines\Excel After that, please specify “IMEX=1” in the connection string like: Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\abc.xls;Extended Properties="EXCEL 8.0;HDR=YES;IMEX=1"; For more information, please see: http://support.microsoft.com/kb/189897/Please remember to mark the replies as answers if they help and unmark them if they provide no help. Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.

HI my data is like Strnum currency 1111 11213 312 21312 34 234 3242 24 23423 24 42342 24 54245 24 25423 24 5234 24 252 24 52 24 542 24 ads asdf asd aesdf asd asdf asd asdf asdf asdf sadf asdf asdf sadf the character data it is showing nulls, I have tried by putting Imex=1 property in connectino string and removing Imex=1 property from connection string , in both cases it is showing nulls , in the data viewer which i have placed after Excel source. How can i achieve my task now? TRY This and suggest me how to archive same data in data viewer. Thanks RaviKiran.M Hi, Could you please elaborate a bit? Which version of Excel file do you have? If it’s Excel 97-2003, SSIS uses Jet to access the Excel files. By default, Jet scans the first 8 rows of your data to determine the type of each column. To fix it, please edit the registry to increase the TypeGuessRows DWORD value of one of the following registry keys to determine how many rows to scan in your data: For 32-bit Windows Excel 97 HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\3.5\Engines\Excel Excel 2000 and later versions HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel For 64-bit Windows Excel 97 HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Jet\3.5\Engines\Excel Excel 2000 and later versions HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Jet\4.0\Engines\Excel After that, please specify “IMEX=1” in the connection string like: Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\abc.xls;Extended Properties="EXCEL 8.0;HDR=YES;IMEX=1"; For more information, please see: http://support.microsoft.com/kb/189897/Please remember to mark the replies as answers if they help and unmark them if they provide no help. Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.

Hi ,Jian kang My scenario is, If I use excel source adapter and i have connected to one excel file say File1 which has data based on the data it will define data types, I am using BIDS directly to and my File1 Data is like EmpId currency 1111 11213 312 21312 34 234 3242 24 23423 24 42342 24 54245 24 25423 24 5234 24 252 24 52 24 542 24 ads asdf asd aesdf asd asdf asd asdf asdf asdf sadf asdf asdf sadf so the columns data type will be decided as Unicode 255 for both Empid,currency columns , if I have change the data in the excel file like this as EmpId currency 1111 11213 312 21312 34 234 3242 24 23423 24 42342 24 54245 24 25423 24 5234 24 252 24 52 24 542 24 25423 asdf 252 aesdf 5234 asdf 34 asdf 3242 asdf 52 asd Now, My meta data is getting changed for the column Empid to numeric type in design mode How can I avoid this because my data may contain all numericsor some of them are charcters. Thanks

The Flat File connection manager provides the file connection, provides information about the file and columns, and specifies how to handle the data in the file. However, the Excel Connection Manager only enables a package to connect to an Excel file. I think it’s because that flat files have little or none information describes the data in them. For example, we do not need to specify row and column delimiters for an Excel file. But we need to use the Flat File connection manager to specify them to make data in a flat file is retrieved as we expected. Please remember to mark the replies as answers if they help and unmark them if they provide no help. Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.

The Jet driver needs to scan several rows of the data source to guess the data types of the source columns. We can specify how many rows are to be read but cannot specify data types. The driver’s behavior cannot be changed. I think we have to convert the data type after retrieving data with a Data Conversion or Derived Column transformation.Please remember to mark the replies as answers if they help and unmark them if they provide no help. Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.

Then it seems to be better to not use Excel as source I think, let me know if I am wrong. Is it recommended to use excel as data source in production environment for long term projects, am i write? Thanks

It depends on your requirements. Using Excel files can have more functionality, like sorting, formulas and functions. If you just want to store data in files, using flat files has a better performance. Please remember to mark the replies as answers if they help and unmark them if they provide no help. Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.