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.
Click New...
on Configure OLE DB Connection Manager.
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 nameSora
. - Click
Test Connection
- Click
OK
on the Test connection succeeded message. - Click
OK
on Connection Manager
The newly created data connection will appear on Configure OLE DB Connection Manager. Click OK
.
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
Set the property RetainSameConnection
on the connection KIWI\SQLSERVER2008R2.Sora
to the value True
.
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 typeString
in the package scopeSO_5631010
and set the variable with the valueEXEC dbo.PopulateTempTable
.A new variable named
FetchTempData
of data typeString
in the package scopeSO_5631010
and set the variable with the valueSELECT CountryCode, StateCode, Name FROM ##tmpStateProvince
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
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.
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
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.
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
.
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
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.
Data Flow tab should look something like this after configuring all the components.
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.
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.
Execute the package. Control Flow shows successful execution.
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.
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.
The above example illustrated how to create and use temporary table within a package.
Related Topics
How to Use a Function-Based Index on a Column That Contains Nulls in Oracle 10+
Do Clustered Index on a Column Guarantees Returning Sorted Rows According to That Column
Derby's Handling of Null Values
Running Powershell Scripts Through SQL
Is Not Null Test for a Record Does Not Return True When Variable Is Set
SQL Server Agent Job Account Issue
SQL Performance of a Lookup Table
In SQL Server, Why Is It That Null Does Not Equal Empty String and Doesn't Not Equal Empty String
MySQL Count() Multiple Columns
SQL Query to Create a Calculated Field
Reverse in Oracle This Path Z/Y/X to X/Y/Z
SQL Case: Does the Order of the When Statements Matter
Spool Command Doesnt Save Result in Query
Insert into Not Exists SQL Access
Compare Comma Separated Values in SQL
As400 SQL Query with Parameter
Update Statement in Oracle Using SQL or Pl/SQL to Update First Duplicate Row Only