March 27, 2008 at 10:28 am
Hello, wondering if this is possible in ssis. In SSIS you can connect to one of the data sources using the tools in the the "Data Flow Sources" section. Can you create your own dataflow source? Consider this example:
Using the script task lets say I used vb.net to get all the files in a directory, initially they're in an array of course, but lets say I loaded an ado.net datatable with these. Now I would like to use this datatable as the source component in my package.
Note: I know I can take the datatable and load a SQL table (or create a text file) then connect to this as the data flow source, but I'm looking to see if I can create my data flow source myself.
Also, my example is not what I'm looking to do, but I wanted to use this as an example since it shows that I created what I would want to use as a source. My point is, I would love to in some cases create my own data flow source and use this in my packages. Can this be done?
Thanks,
Strick
March 27, 2008 at 11:12 am
You have two options.
First, the Script Task can act as a control flow component, a data flow source, a transformation, and a data flow destination. So for your example, you could use a script task to output to a buffer and attach your subsequent data flow tasks to it.
In addition to this, using any of your .Net programming languages, you can create any SSIS components you want. There are some sample components and source code here: http://www.sqlbi.com and also on the MSDN website.
March 27, 2008 at 11:29 am
Hmmm this is very interesting. Can you elaborate a little more on the "output to a buffer" comment you made? How would I go about doing this? So would this be done inside the data flow task to create the data flow source? So in my datatable example how would I output this to a buffer?
Thanks!
Strick
March 27, 2008 at 11:32 am
Also, do you know of any really good articles that talk about
using Script Task can act as a control flow component, a data flow source, a transformation, and a data flow destination?
I'm a programmer by trade and just knowing I can code any of these four things would open up alot of options for me in terms of creating packages.
Thanks!
Strick
March 27, 2008 at 1:02 pm
If you have any experience with a .Net language, the script component will be easy for you to use this way.
Just try it, drop a data flow task into your control flow, go into the data flow and drag out a script transformation.
You will be asked how you want to use it (source, transformation, or destination).
When you open the component, go to the script group and click the "Design Script" button.
You will have to use VB.Net (SQL 2005 does not support any other .Net languages), but there are instructions already in the script that will tell you how to add data to an output buffer.
If you go back to the component editor and go to the Inputs and Outputs group, you can configure an output buffer - then go back into the script editor and you can see what was generated in the Buffer Wrapper classes.
Like I said, SQLBI has some sample code and there are examples on MSDN that are pretty good. I'm sure if you search these forums you will find some sample code also (I think I have posted some in the past). In addition to all of that, I believe the sample projects and code for SQL 2005 includes some samples for this.
March 27, 2008 at 2:41 pm
Sweet thanks! I've extensively used the script compont in the control flow using vb.net, but not inside the dataflow task as a source dataflow, transformation, or destination data flow. I'll look into it in the dataflow task.
Thanks,
Strick
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply