Hello I have a data task that imports a file into a database. However the file name and location may change but the type of contents won't really. I was hoping to automate this almost fully to a Windows Form but to do that I have to call the SSIS task dynamically to run. I was hoping to do DTUtil.exe to call an SSIS task to do the work by basically having a user put in a text in a form field to pass into the SSIS task. To be more clear: 1. The Flat File's name may change but it will always just contain two column types. This connection I already have an the task works, but I want to automate it with a different name. EG: Today I might have file: 2-7-11test.txt, tomorrow it will be 2-8-11test.txt. 2. I do not have access to SQL Agent on the system I am using so I want to call the task once I got it set with possibly the DTUtil.exe command. I figured if I could pass in a string to the package from a command line tool I can automate it from C# then to pass a form field text to the console command line. 3. I am not opposed to C#, ADO.NET, or Linq to do this either. I just know SQL the most and was learning SSIS so I thought I would start here. 4. I am really really am struggling with understand how you use and define variables in SSIS, and that is the root of my problem. I saw a great article here: http://social.msdn.microsoft.com/forums/en-US/sqlintegrationservices/thread/730ceb63-6fb8-4bc8-970a-d4f3daf3a0e7/; however I think I would do better to see a working example of where you place the variables and how they are called. Just telling me the code expressions makes sense to me in theory but I am not seeing how they are placed and where to achieve the end goal. In general I am having a hard time with SSIS on a dynamic use level. It appears there is not a lot I am finding of good stuff for beginners on it. It's like you are born knowing it and if anyone knows a good beginner book or site to learn it from real world example tutorials I would appreciate it. Any help is much appreciated thanks.Medium to Advanced on some things, but always wanting to know more

Hi, you can enable package configurations and pick up your file name from xml file, database and etc http://msdn.microsoft.com/en-us/library/ms141682(v=SQL.90).aspx or let's say you have SSIS variable FileName - string, set EvaluateAsExpression - true assuming you save/edit FileName value on database, add simple Execute SQL Task task on your SSIS to get value likes select VarValue from Table where VarName='FileName' set ResultSet=SingleRow, got ot tab ResultSet and map variable FileName after that you can build connection string on fly, go to properties of connection string - expressions and map/create connection from variable something likes that

have a look at my post Dynamically Set flat file connection. My Blog | Ask Me

I don't really want to use an xml package configuration though, that just adds yet another layer to it. I want to a variable configuration on the flat file source that can be populated by a user or program calling the task. My package configuration for the database won't change, just the flat file source. If someone, anyone can do a simple 1,2,3,4 example or a link to a piece of code where this is done it would be appreciated. I can understand you can set a variable, I can understand you can call variables from other tasks, I can understand you can apply their scope level. However I need to see where you set the variables at and how they are implemented. I know you can do it but I have not yet seen a working example of it. How do you present the value of the file path and name together with the expression and WHERE is that done? I know you can do it, I just don't see how you accomplish it.Medium to Advanced on some things, but always wanting to know more

This assumes I want to place the name in the database, I do not. I just want it as an input to run on. Do you know how instead of setting a dynamic value up in a database I could just pass it to the task? Also you are listing Demo.txt specifically in your variable and it is not altered anywhere so that get's passed to the data task and it will fail because it adds that reference on top of your existing one in the database: EG: if I leave the SQL task to "demo.txt" and then set my variable expression to "C:\\Test\\" + @[User::FileName] it will equate the string connection to: C:\Test\demo.txt<Whateverisinthedatabase>. So to do this I assume you need to leave one thing blank or another. It worked but this seems more when I am describing sql to an object. All I want to do is say: "Hey file A map to new table creation B". I don't know why I have to do all these complex things I would think it would just be an expression somehow mapped somehow to a user input. Is that not possible in SSIS? If it's not I will just go to ADO.Net or C# methods completely. Much the same as if I build a stored procedure in TSQL in SQL Management Studio I can make it required the user to pass a value. That's all I want is this equivalent of SQL in SSIS: Use tempdb GO if object_id('ex') is not null drop proc ex GO Create procedure ex ( @input varchar(64) ) as begin select Case when @input = 'A' then 'Create A import' when @input = 'B' then 'Create B import' else 'You don''t want to import?' end as response End GO exec ex 'A' -- gives first example exec ex 'B' -- gives second example exec ex 'Brett' -- gives default Medium to Advanced on some things, but always wanting to know more

I think I figured part of it out based on this thread: http://www.mssqltips.com/tip.asp?tip=1395 You basically can create a Script Task or SQL Task to manipulate a variable. However I just wanted to know how you pass a variable to an SSIS task to run it with a user decided import name. This seems to be achieved by using a variable and then changing that variable at run time with the DTEXEC command and the switch: /SET \Package.Variables[User::fileName].Properties[Value];"<YOURVALUE> Now that's the command line way to do this but programmatically you need to do this maneuvering in C# or VB.NET to make a user be able to really put something in a form and then have it go off. However an extra bonus if you could tell me how the C# assembly Microsoft.SqlServer.Dts.Runtime runs with variables. I got it last night to run a package but have yet to see the variable input part of it. Man this assembly was a b#@#$@# to find online where it stores at on your PC. MS and people write about it but don't tell you if you have Windows 7 64 bit and a different default SQL Directory it is int SQL's x86 Program Files directory and is not even named the same (it's like ManagedDTS.dll or something). Anyways after going far down this rabbit hole I will safely say in the future I will probably just use C# or Linq in the future as learning SSIS is like a secret ninja art that no two people can agree on what you mean. After reading a ton most people like to do scripting or SQL tasks to change variables.Medium to Advanced on some things, but always wanting to know more

I think I got what I wanted completely with a mix of different sites. Basically I created a few test files for testing an import and have the path known. I then create an SSIS task: I made a variable for the destination as a string. I made a variable for a SQL Creation statement. I made a variable for a destination. I made a SQL task that references the SQL Creation statement. I set this to link to a data flow task. In the data flow task I set a flat file source. I set up the transfer for the file columns to the right lengths and such. Then on the flat file properties I set an expression to the connection. Then I create a destination, I set it up to create from the variable. Now the fun part. You can reference nearly all these variables to change them programmatically with the assembly above. I did something like this (you need the Microsoft.SQLServer.DTS.Runtime). // create the package Application app = new Application(); // find the location of the package to run Package package = app.LoadPackage(@"D:\Brett\Docs\LookAtIt.dtsx", null); // map the variables Variables vars = package.Variables; vars[@"User::FileName"].Value = @"D:\Test\names1.txt"; vars[@"User::SQLStatement"].Value = @"Create table dbo.names2 (column1 varchar(50),column2 varchar(50),column3 varchar(50))"; vars[@"User::Destination"].Value = @"dbo.names2"; // execute DTSExecResult result = package.Execute(); // give the user the results Console.WriteLine("Package Execution results: {0}", result.ToString()); Console.ReadLine(); This does everything I want programmatically, case closed. Medium to Advanced on some things, but always wanting to know more