hi, i need to use,following provider , native oledb\microsoft jet 4.0 oledb provider. now here in connection string, i have date source = c:\users:\.txt i need to make this dynamic,like need to give variable name,so whatever file comes ,it wil pick it up if i put data source -user::file1 it give sme error not valid file format. i cannot use connection string,because it involves other things please help me

Hi tsql_new Try creating the connection string the first time with the excel file. After this use the same data source string but put this in a variable. At this point the package should still work. Now you should be able to dynamically change this variable. If the excel wont exist at the beginning of the package start make sure to turn off validation for this task. (When creating a package never turn off validation of tasks! unless for example you know the reason) In this case you know the package works but are aware that at the start of its execution it shouldn't expect to find this file.Prez

hi thansk for your reply, i need more clarification. i have .dat file, which has connection string Data Source=C:\Users\Desktop\DEA\n2.dat;Provider=Microsoft.Jet.OLEDB.4.0; now if i go to expression->conncetion string - user::file1 now file1 has just path for C:\Users\Desktop\DEA\n2.dat, so its not working. if i put Data Source=user::file1;Provider=Microsoft.Jet.OLEDB.4.0; gives me error like not valid format please help me

You need to create a package variable where you build your new connection string. Set the variable's Evauluate as Expression to True. Then on the connection manager, refer to the variable in question for the connection string.MCITP:DBA, MCDBA, MCSA

Hi tsql_new Hi please try: http://geekepisodes.com/sqlbi/2011/creating-excel-files-xls-dynamically-from-ssis/ There might be another article which is better but I truely searched for about 1 minute on this. I was going to write it all out from my brain but if it is already there I figured I'd give you a link. Prez

i do knwo how to make falt file source dynamic but here its jet provider. please ss the image below, the third one is where i am not using any variable.i cannot put just connectionstring -variable. coz actual connection string has data source(which has file path) and some other things i am using oledb source,in which native oledb\microsoft 4.0 jet oledb provider.

third image

Hi tsql_new It looks like you variable file1 contains just the file path. Please try to set ExcelFilePath parameter for the connection string parameter and not the whole connection string. Remember to set the EvaluateAsExpression property to true for the variable. Prez

Hi tsql_new It looks like you variable file1 contains just the file path. Please try to set ExcelFilePath parameter for the connection string parameter and not the whole connection string. Remember to set the EvaluateAsExpression property to true for the variable. Prez

tsql_new, Can you please tell us what expression you're using to set your variable? If you don't know, please give us two bits of information. 1) The current path and filename (which I assume is C:\Users\Desktop\DEA\n2.dat) and 2) the new path and filename. With these two bits of information we can help you write your expression if you're having trouble.MCITP:DBA, MCDBA, MCSA