Dynamic selection of flat file

I am writing a package where the user uploads a flat file to a web folder. I need to automate this package to run everytime it sees a new file.

How can I implement this?

Can I make a call to a package or a sql server job to run from .net 2.0?

Do I need to use a service broker to look for a new file and run the package or a stored proc....I am looking for an async process where user doesnt have to wait for the package to run as it involves data validation of flat file and its huge...

Please help!!

How about a scheduled package that runs every minute, and if it finds a file it runs the load task, otherwise it just ends.

You could do something event driven with the WMI event task, but I dislike that since it does not tell you what file has been found, or try the File Watcher Task (http://www.sqlis.com/default.aspx?23)

|||

Appreciate your response...

I was wondering about creating a config file in the database and update the Connection string value everytime a new file is uploaded with the new file name

And then call the package to run by adding dts assembly to visual studio and doing package.load....

Any suggestion or comments on this as I really dont want to run this every min....

Dynamic connection manager file name

Hi There

This should be an easy one i hope.

I need to dynamically change the file name of my destination flat file connection everytime the package runs, obviously i do not want to edit the config file everytime.

I think the best way to do this is by a script taks that sets the connection manager filename property with a variable that i dynamically populate.

Is this the right way?

I just want to be sure that there is not an easier or better way to do this?

Thanx

Yes, that sounds like a good approach. Don't use a script task though (because its not possible to do it with a script task), use a property expression (http://www.google.co.uk/search?hl=en&q=ssis+expressions&meta=).

You can parameterise the package execution by passing values in from the command-line using the /SET option.

-Jamie

|||Thats is exactly what i was looking for, thanx a million Jamie|||Sorry to be daft, but how to do you get to the setting, when I click on the flat file source, I pull up the connection manager, which doesn't have an expressions section like the others. Could someone give me a general walk through as to how I can set the expressions property for the flat file source file name?

Thank you
|||If you select the connection for the flat file in the connection managers section of the package, the properties window will display for the properties for that connection. One of the properties is Expressions. You can probably figure it out from there.|||Right...Thanks a lot

Dynamic connection manager file name

Hi There

This should be an easy one i hope.

I need to dynamically change the file name of my destination flat file connection everytime the package runs, obviously i do not want to edit the config file everytime.

I think the best way to do this is by a script taks that sets the connection manager filename property with a variable that i dynamically populate.

Is this the right way?

I just want to be sure that there is not an easier or better way to do this?

Thanx

Yes, that sounds like a good approach. Don't use a script task though (because its not possible to do it with a script task), use a property expression (http://www.google.co.uk/search?hl=en&q=ssis+expressions&meta=).

You can parameterise the package execution by passing values in from the command-line using the /SET option.

-Jamie

|||Thats is exactly what i was looking for, thanx a million Jamie|||Sorry to be daft, but how to do you get to the setting, when I click on the flat file source, I pull up the connection manager, which doesn't have an expressions section like the others. Could someone give me a general walk through as to how I can set the expressions property for the flat file source file name?

Thank you|||If you select the connection for the flat file in the connection managers section of the package, the properties window will display for the properties for that connection. One of the properties is Expressions. You can probably figure it out from there.|||Right...Thanks a lot