SSIS Recordset Destination as Source?

Ever wanted to put some data in a Recordset Destination and then pull the data out at a later time in your package?

Sorry.  No can do.

You can foreach through the record set and act on it in that way, but if you want to use it as if it were a source, no luck.

A Recordset Destination gets it metadata at design time from its predecesor.  A recordset source wouldn't have that opportunity to set metadata.

You'll have to use raw files instead.  Dump the data out to a raw file and then use a Raw File Source later in your package.

Seems like a workaround, but that's the way it is.

UPDATED:

I just read a blog entry by Jamie Thomson (click here) and was interested to hear him say that the raw file adapter was designed for passing data between two different data flow tasks.  He also talks about a way to create a script task as a source that loops through the recordset in memory and plops each row in the pipeline.  Very clever.

Advertisements

9 Responses to SSIS Recordset Destination as Source?

  1. Nir says:

    Do you have a reference for using Raw File Source ?
    I’m using it, but I have problem when the file is not exist yet.
    There is a task which creating the file and latter on another task using the file.
    The problem is the file is not exist yet and in design time it’s prompt for error.

  2. Mark Garner says:

    Nir,
    The Raw File Source contains the metadata needed for flowing data through the pipeline. There isn’t any way to use it without creating the file first. You’ll have to create an empty file at design time that you can use for development.
    Mark

  3. Petro says:

    hi, hi, hi! Beautiful site.

  4. thiru says:

    How do u create the dummy file for Raw File Source manually?

  5. Mark Garner says:

    It can only be created by a raw data destination. You’ll just need to write the task that puts data into the RawFile first and then build the task that uses the RawFile as a source.

  6. You don’t really need to do it in the script task, you can just right click on the folder where you are expecting the file to be generated and just create a blank text file and rename it. It’s merely a place holder for when the integration really runs.

  7. MGS66 says:

    Mark – Is it just me or does SSIS need a lot of improvement? I have no choice but to use it but it is not intuitive. I’m still struggling to figure out how to do a simple thing like insert records from tableA on server1 where those records do not already exist on server2 in tableA.

  8. CyNETT says:

    YES You can do it !!!

    1) (temporary) create normal table eg. TMP_TABLE in your destination database with the same structure as your (destination) #TempTable. At the end of your work, you can drop your TMP_TABLE

    2) in SSIS create variable (string) eg. MyDest and asociate it with “TMP_TABLE”

    3) create standart Data Flow Task with OLE DB Source as you wish, BUT in OLE DB Destination use “Table name or view name variable” and in DropDown select your MyDest. Do all mappings with your TMP_TABLE (alias MyDest).

    4) Click properties of Data Flow Task and change DelayValidation to True

    5) Change value of variable MyDest to your “#TempTable”

    6) That’s it !

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: