Ssis Pass Datasource Between Control Flow Tasks

SSIS Pass Datasource Between Control Flow Tasks

To pass data flowing in one dataflow to another, You have to have a temporary location.

This means that You have to put data in destination in one dataflow and then read that data in another dataflow.

You can put data in number of destinations:

  • database table
  • raw file
  • flat file
  • dataset variable (recordset destination)
  • any other destination component that you can read from with corresponding source component or by writing script or whatever

Raw files are meant to be used for cases like this. They are binary and as such they are extremely fast to write to and read from.

In case You insist to use recordset destination take a look at http://consultingblogs.emc.com/jamiethomson/archive/2006/01/04/SSIS_3A00_-Recordsets-instead-of-raw-files.aspx because there is no recordset source component.

Use table variable in control and data flow

I believe your question is directly related to this one: SSIS Pass Datasource Between Control Flow Tasks
You can indeed pass data from one data flow to another in a dataset, or through raw files. It is up to you to check which is better in your case though

SSIS Parameterize Mappings

Actually upon further research I found that it is possible to do this using the CozyRoc Data Flow Plus product as explained in this blog post.

Basically it allows you to create a data flow with dynamic column mapping. You can't use an ODBC data source, but you can use an ADO.NET data source with the ODBC driver, and you can also dynamically map the Data Conversion task to account for how the ADO.NET source forces you into Unicode columns.

It appears that I don't even need a Parent package, but just a ForEach loop that I can pass a RecordSet containing the table information to for all the tables I want to load.

The key is marking everything as "DelayValidation" and all data flow tasks as "ValidateExternalMetadata" to false when using the CozyRoc product.

Just using trial version for now, and will have to determine if the cost is worth it.

SSIS : how to run stored procedure in Data Flow Task

To execute a stored procedure with parameters in OLEDB Command, all you need write the SQL Command and use ? to specify a parameters.

Example:

Consider the following stored procedure:

CREATE PROCEDURE dbo.SpInputOutput

@input as int,

@output as datetime output

AS

BEGIN

SET NOCOUNT ON;

set @output=DATEADD(Day,@input,getdate())

END

You should write the following command:

exec SpInputOutput ? , ? output

And in the Columns Mapping dialog, Map the input columns to the parameters specified.


For more details, check the following links:

  • Output Parameter of Stored Procedure In OLE DB Command - SSIS
  • SSIS OLEDB COMMAND AND PROCEDURE OUTPUT PARAMS

How to create a temporary table in SSIS control flow task and then use it in data flow task?

Solution:

Set the property RetainSameConnection on the Connection Manager to True so that temporary table created in one Control Flow task can be retained in another task.

Here is a sample SSIS package written in SSIS 2008 R2 that illustrates using temporary tables.

Walkthrough:

Create a stored procedure that will create a temporary table named ##tmpStateProvince and populate with few records. The sample SSIS package will first call the stored procedure and then will fetch the temporary table data to populate the records into another database table. The sample package will use the database named Sora Use the below create stored procedure script.

USE Sora;
GO

CREATE PROCEDURE dbo.PopulateTempTable
AS
BEGIN

SET NOCOUNT ON;

IF OBJECT_ID('TempDB..##tmpStateProvince') IS NOT NULL
DROP TABLE ##tmpStateProvince;

CREATE TABLE ##tmpStateProvince
(
CountryCode nvarchar(3) NOT NULL
, StateCode nvarchar(3) NOT NULL
, Name nvarchar(30) NOT NULL
);

INSERT INTO ##tmpStateProvince
(CountryCode, StateCode, Name)
VALUES
('CA', 'AB', 'Alberta'),
('US', 'CA', 'California'),
('DE', 'HH', 'Hamburg'),
('FR', '86', 'Vienne'),
('AU', 'SA', 'South Australia'),
('VI', 'VI', 'Virgin Islands');
END
GO

Create a table named dbo.StateProvince that will be used as the destination table to populate the records from temporary table. Use the below create table script to create the destination table.

USE Sora;
GO

CREATE TABLE dbo.StateProvince
(
StateProvinceID int IDENTITY(1,1) NOT NULL
, CountryCode nvarchar(3) NOT NULL
, StateCode nvarchar(3) NOT NULL
, Name nvarchar(30) NOT NULL
CONSTRAINT [PK_StateProvinceID] PRIMARY KEY CLUSTERED
([StateProvinceID] ASC)
) ON [PRIMARY];
GO

Create an SSIS package using Business Intelligence Development Studio (BIDS). Right-click on the Connection Managers tab at the bottom of the package and click New OLE DB Connection... to create a new connection to access SQL Server 2008 R2 database.

Connection Managers - New OLE DB Connection

Click New... on Configure OLE DB Connection Manager.

Configure OLE DB Connection Manager - New

Perform the following actions on the Connection Manager dialog.

  • Select Native OLE DB\SQL Server Native Client 10.0 from Provider since the package will connect to SQL Server 2008 R2 database
  • Enter the Server name, like MACHINENAME\INSTANCE
  • Select Use Windows Authentication from Log on to the server section or whichever you prefer.
  • Select the database from Select or enter a database name, the sample uses the database name Sora.
  • Click Test Connection
  • Click OK on the Test connection succeeded message.
  • Click OK on Connection Manager

Connection Manager

The newly created data connection will appear on Configure OLE DB Connection Manager. Click OK.

Configure OLE DB Connection Manager - Created

OLE DB connection manager KIWI\SQLSERVER2008R2.Sora will appear under the Connection Manager tab at the bottom of the package. Right-click the connection manager and click Properties

Connection Manager Properties

Set the property RetainSameConnection on the connection KIWI\SQLSERVER2008R2.Sora to the value True.

RetainSameConnection Property on Connection Manager

Right-click anywhere inside the package and then click Variables to view the variables pane. Create the following variables.

  • A new variable named PopulateTempTable of data type String in the package scope SO_5631010 and set the variable with the value EXEC dbo.PopulateTempTable.

  • A new variable named FetchTempData of data type String in the package scope SO_5631010 and set the variable with the value SELECT CountryCode, StateCode, Name FROM ##tmpStateProvince

Variables

Drag and drop an Execute SQL Task on to the Control Flow tab. Double-click the Execute SQL Task to view the Execute SQL Task Editor.

On the General page of the Execute SQL Task Editor, perform the following actions.

  • Set the Name to Create and populate temp table
  • Set the Connection Type to OLE DB
  • Set the Connection to KIWI\SQLSERVER2008R2.Sora
  • Select Variable from SQLSourceType
  • Select User::PopulateTempTable from SourceVariable
  • Click OK

Execute SQL Task Editor

Drag and drop a Data Flow Task onto the Control Flow tab. Rename the Data Flow Task as Transfer temp data to database table. Connect the green arrow from the Execute SQL Task to the Data Flow Task.

Control Flow Tab

Double-click the Data Flow Task to switch to Data Flow tab. Drag and drop an OLE DB Source onto the Data Flow tab. Double-click OLE DB Source to view the OLE DB Source Editor.

On the Connection Manager page of the OLE DB Source Editor, perform the following actions.

  • Select KIWI\SQLSERVER2008R2.Sora from OLE DB Connection Manager
  • Select SQL command from variable from Data access mode
  • Select User::FetchTempData from Variable name
  • Click Columns page

OLE DB Source Editor - Connection Manager

Clicking Columns page on OLE DB Source Editor will display the following error because the table ##tmpStateProvince specified in the source command variable does not exist and SSIS is unable to read the column definition.

Error message

To fix the error, execute the statement EXEC dbo.PopulateTempTable using SQL Server Management Studio (SSMS) on the database Sora so that the stored procedure will create the temporary table. After executing the stored procedure, click Columns page on OLE DB Source Editor, you will see the column information. Click OK.

OLE DB Source Editor - Columns

Drag and drop OLE DB Destination onto the Data Flow tab. Connect the green arrow from OLE DB Source to OLE DB Destination. Double-click OLE DB Destination to open OLE DB Destination Editor.

On the Connection Manager page of the OLE DB Destination Editor, perform the following actions.

  • Select KIWI\SQLSERVER2008R2.Sora from OLE DB Connection Manager
  • Select Table or view - fast load from Data access mode
  • Select [dbo].[StateProvince] from Name of the table or the view
  • Click Mappings page

OLE DB Destination Editor - Connection Manager

Click Mappings page on the OLE DB Destination Editor would automatically map the columns if the input and output column names are same. Click OK. Column StateProvinceID does not have a matching input column and it is defined as an IDENTITY column in database. Hence, no mapping is required.

OLE DB Destination Editor - Mappings

Data Flow tab should look something like this after configuring all the components.

Data Flow tab

Click the OLE DB Source on Data Flow tab and press F4 to view Properties. Set the property ValidateExternalMetadata to False so that SSIS would not try to check for the existence of the temporary table during validation phase of the package execution.

Set ValidateExternalMetadata

Execute the query select * from dbo.StateProvince in the SQL Server Management Studio (SSMS) to find the number of rows in the table. It should be empty before executing the package.

Rows in table before package execution

Execute the package. Control Flow shows successful execution.

Package Execution  - Control Flow tab

In Data Flow tab, you will notice that the package successfully processed 6 rows. The stored procedure created early in this posted inserted 6 rows into the temporary table.

Package Execution  - Data Flow tab

Execute the query select * from dbo.StateProvince in the SQL Server Management Studio (SSMS) to find the 6 rows successfully inserted into the table. The data should match with rows founds in the stored procedure.

Rows in table after package execution

The above example illustrated how to create and use temporary table within a package.



Related Topics



Leave a reply



Submit