Dynamically create an SSIS Bulk Insert Package

I am looking high and low for some assistance with developing a VB .NET solution that I programmatically create a package and add tasks. I am adding a BULK INSERT task to load large FLAT TEXT files into SQL Server 2005 tables. When I execute the application I execute a package validation and it always returns FAILURE. I have been reading and searching like crazy and I have bought 2 microsoft books, TO NO AVAIL! Can anyone PLEASE help me with this. Thank you!

Cheers~

Do you have any more specific messages other than "FAILURE"?|||

No, unfortunately not. This is a new venture for me and I am not even sure that I am going about the creation process correctly. Quite honestly, I am getting 'pieces' of code from different places off of Microsofts Books Online and sort of putting them together. I have been able to successfully load a package template and execute it successfully, but creating the package from scratch has proven to be quite the challenge. I have found a plethora of samples in C#, but I have not been able to locate a full sample in VB .NET at this point.

The reason I only get a 'FAILURE' return is because I am simply saying DTSExecResult = pkg.execute() and then displaying the return value of the result.

Any suggestions, guidance or direction will be GREATLY appreciated at this point! Thanks a million!

Cheers~

|||Be sure to enable logging: http://technet.microsoft.com/en-us/library/ms136023.aspx

Also know that I'm not going to be much help with the coding. I'm learning on my own as well, but I'm just trying to gather specific information for others who will read this thread.

Also know that at some point you may be asked to share your code.|||

I will gladly share my code! Thank you so much for the replies thus far. I am hoping that I can get some great responses and learn as much as possible in the coming days about this topic. Talk to you soon!

Cheers~

|||Try saving the package (Application.SaveToXML) after creating it. Then open and run the package in BIDS. That makes it a lot easier to see what the problem is.|||

John, I am about to do this. However, what do you mean by running it in BIDS? Thanks.

Cheers~

|||Business Intelligence Development Studio -- or Visual Studio -- which is where you develop SSIS packages via the GUI.|||

OK, what I am seeing now is that the Destination connection is not available, and there are no columns mapped from the input source - (flat file) - to the ole destination table. I am assuming that this is because I am failing to do something correctly in the code to setup the package's BULK INSERT properties.

Are there any tutorials available to understand how to set all the properties for source and destinations, respectively? Any direction you might can provide is awesome!!!!

Again, I am finding bits and pieces here and there, but nothing that says "Hey, do it this way"...

Cheers~

|||

OK, I have made some progress. I now have a Flat FIle source and an OLEDB destination. My delimma at this point is mapping the SOURCE columns from the flat file to the DESTINATION columns of the OLEDB table. The Flat File does not have a header row and it is a pipe "|" delimited file. Any idea on how to map the source / destination columns?

Cheers~

|||

Do you have Output columns in the Flat FIle Source output? Save the package in code (example http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2179817&SiteID=1) and open it to check what it looks like.

If so then you should be able to connect the output to the destination's input, and select your columns. Mapping is a simple method call, but what you map to what is your logic really. In this example I just use index position becuase I "know" that my source and destination formats match.