Stored procedures to flat files from ssis
-
My source as multiple stored procedures names stored in a table and I need to generate a flat file for each stored procedure dynamically.
Not sure how to configure the OLEDB Source or is there any other method?
-
Do all your source stored procedures return identical recordsets? Or another way of asking this, do all you flat file outputs have the same format?
-
Yes. Same format with Pipe | as delimiter.
-
Try setting the AccessMode property of the OLE DB source to 'SQL Command from Variable'. Then try setting the SQLCommandVariable to the name of your variable.
-
I can`t get it as the stored procedure names are in a table.
Below is the SQL and attached is the SSIS pkg. The pkg runs fine for one stored procedure but I need it for multiple SP`s.
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TablesList]') AND type in (N'U'))
DROP TABLE [dbo].[TablesList]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Source1]') AND type in (N'U'))
DROP TABLE [dbo].[Source1]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Source2]') AND type in (N'U'))
DROP TABLE [dbo].[Source2]
GO
CREATE TABLE [dbo].[TablesList](
[StoredProcedureName] [varchar](50) NOT NULL,
[FilePath] [varchar](255) NOT NULL,
[FileName] [varchar](255) NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Source1](
[ID] [int] IDENTITY(1,1) NOT NULL,
[ItemNumber] [varchar](20) NOT NULL,
[ItemName] [varchar](50) NOT NULL,
[CreatedDate] [date] NOT NULL,
CONSTRAINT [PK_Source1] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Source2](
[ID] [int] IDENTITY(1,1) NOT NULL,
[ItemNumber] [varchar](20) NOT NULL,
[ItemName] [varchar](50) NOT NULL,
[CreatedDate] [date] NOT NULL,
CONSTRAINT [PK_Source2] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT [dbo].[TablesList] ([StoredProcedureName], [FilePath], [FileName]) VALUES (N'usp_SPName1', N'C:\FlatFiles\', N'SPName1.txt')
INSERT [dbo].[TablesList] ([StoredProcedureName], [FilePath], [FileName]) VALUES (N'usp_SPName2', N'C:\FlatFiles\', N'SPName2.txt')
INSERT INTO dbo.Source1(ItemNumber, ItemName, CreatedDate)
VALUES(100,'A', GETDATE())
,(100,'A', GETDATE()-1)
,(100,'A', GETDATE()-2)
,(100,'A', GETDATE()-3)
,(100,'A', GETDATE()-4)
,(100,'A', GETDATE()-5)
,(100,'A', GETDATE()-6)
,(100,'A', GETDATE()-7)
,(100,'A', GETDATE()-8)
,(100,'A', GETDATE()-9)
GO
INSERT INTO dbo.Source2(ItemNumber, ItemName, CreatedDate)
VALUES(200,'B', GETDATE())
,(200,'B', GETDATE()-1)
,(200,'B', GETDATE()-2)
,(200,'B', GETDATE()-3)
,(200,'B', GETDATE()-4)
,(200,'B', GETDATE()-5)
,(200,'B', GETDATE()-6)
,(200,'B', GETDATE()-7)
,(200,'B', GETDATE()-8)
,(200,'B', GETDATE()-9)
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_SPName1]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp_SPName1]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_SPName2]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp_SPName2]
GO
CREATE PROCEDURE [dbo].[usp_SPName1]
@StartDate datetime,
@EndDate datetime
AS
SELECT ItemNumber, ItemName, CreatedDate
FROM dbo.Source1
WHERE CreatedDate BETWEEN @StartDate AND @EndDate
GO
CREATE PROCEDURE [dbo].[usp_SPName2]
@StartDate datetime,
@EndDate datetime
AS
SELECT ItemNumber, ItemName, CreatedDate
FROM dbo.Source2
WHERE CreatedDate BETWEEN @StartDate AND @EndDate
GO
SELECT StoredProcedureName, FilePath, FileName FROM dbo.TablesList
EXEC usp_SPName1 '12/20/2013', '12/26/2013'
EXEC usp_SPName2 '12/20/2013', '12/26/2013'
-
Can you add a new variable ExecutionString and concatenate StoredProcedureName, StartDate, EndDate together to give you a string like: EXEC usp_SPName1 '12/20/2013', '12/26/2013'
Then set the SQLCommandVariable to the new variable ExecutionString.
-
But I have more than one stored procedure to get executed. How can we get another StoredProcedure name as variable to pass.
-
Your Foreach Loop container will loop once for every record in your table. Each time it should reset your variables including variable StoredProcedureName and execute the Data Flow Task. If you are setting the OLE DB Source and Flat File Destination objects dynamically a different stored procedure should be executed and a different file name should be created for every record in your table.
Review the following. You will want to see your StoredProcedureName variable update for every record in your table
http://agilebi.com/jwelch/2009/11/29/ssis-101-viewing-variable-values-at-runtime/
http://www.bidn.com/blogs/ShawnHarrison/ssis/2408/ssis-tips-watch-your-variables
Once your variables are updating correctly set the OLE DB Source and Flat File Destination objects dynamically using the variables.
-
It works and also I have to set the Delay Validation to TRUE. Thank you so much!....
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply