Search code examples
ssisautomationpackageexecutionbids

SSIS automatic package execution

I want to automatically execute my SSIS package once a week at a specified time. I want to clear the entire database before I load the contents of a new excel file every week. I think I can use truncate statements for clearing the entire database. However, how do I read from a file in which I do not know the name of ahead of time?

My instincts tell me I need to know the following:

How do I prompt the user for a file name in SSIS? 
Am I using VB or C# to do this prompting?

Thanks!

Solution

  • How long of an answer do you want? ;)

    How do I read from a file in which I do not know the name of ahead of time?

    You have many options in this regard. I'm listing them in order that I'd take

    1. My "go to" would be the Foreach Enumerator and use the file type to process the available file(s) in a standard folder. This reduces your problem to a training issue (User, drop file to \server\share\inbound)

    2. Assuming you have an Expression on the file's Connection Manager ConnectionString property, you can provide a run-time override of this value. Thus, SSIS variable named InputFile and has a value of \\server\share\inbound\source_2012-07-17.txt A Connection Manager named Source and has an expression on the ConnectionString property mapped to @[User::InputFile] When your package runs, as long as the value of InputFile is correct then your package will use the correct source file and all is good. You don't have to create a variable and use expressiosn to do this, you can do it directly against the connection manager's property but from a maintenance and troubleshooting perspective you'll be better positioned to address issues. When you run the package you will need to use a command-line option to provide an explicit value for the thing you are configuring. Approximately dtexec /file C:\mypackage.dtsx /set \Package.Variables[User::InputFile].Properties[Value];"\\server\share\inbound\source_2012-07-18.txt" Use the dtexecui to build this string out properly.

    3. If the user is actually running the package (double click, Execute), then it is in running in interactive mode @[System::InteractiveMode] = True There's nothing that prevents you from writing a full-fledged windows form app in a script task as the first step in your package. That said, I wouldn't bloat an ETL package with UI code but it can be done. It really depends on what you are attempting to accomplish.

    4. Move that UI code into a dedicated package. After assembling user input, pass that to a child package through parent/child configurations.

    5. This is really just 2 + 3ish. Do the good thing and make your package run based on a command-line override. That way it works in interactive mode as well as execution on the server (SQL Agent). Then use whatever technology you wish to create an interface for acquiring and validating user input and then invoke your package with those values. You can skip calling DTEXEC and just work with the object model directly.

    6. This should really be option eleventy billion. The input file will be named \\server\share\input\source_file.txt Rule with an iron fist and make your users conform to your will. If you have the power, allow the users to use any file name they wish, as long as it's the one you told them to use. Failure to do so will result in their data not being loaded.