Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««1234»»»

Dynamic ETL with SSIS Expand / Collapse
Author
Message
Posted Tuesday, October 19, 2010 10:16 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 12:57 PM
Points: 33,206, Visits: 15,361
Martyn Hughes (10/19/2010)

My comment is not meant to be 'snarky', I'm always troubled when I have to fight within an organization to get useful tools. Pervasive does cost several thousand but the amount of time saved using appropriate tools rather than paying FTEs to 'reinvent the wheel' is certainly a better use of company resources.

...


Thanks, and I would agree with you. there are many tools that are worth buying and will help you do a better job with a good ROI. It's good to hear where other tools might fill in a space.

My comment was there as it appeared you were knocking this article as a way of accomplishing a task without providing any reasoning. Thanks for the clarification.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1007111
Posted Tuesday, October 19, 2010 10:52 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 4:26 AM
Points: 514, Visits: 1,728
Its good info. Gives me some new ideas on how to handle text files....
I have always used a User Defined Function to get text files into a table.

--------------------------------------------------
-- USAGE :
-- Select line from
-- Dbo.uftReadfileAsTable('MyPath','MyFileName')
--------------------------------------------------
USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create FUNCTION [dbo].[uftReadfileAsTable]
(
@path VARCHAR(255),
@filename VARCHAR(100)
)
RETURNS
@file TABLE
(
[LineNo] int identity(1,1),
line varchar(8000))
AS
BEGIN
DECLARE @objFileSystem int,
@objTextStream int,
@objErrorObject int,
@strErrorMessage Varchar(1000),
@command varchar(1000),
@HR int,
@String VARCHAR(8000),
@YesOrNo INT
select @strErrorMessage='opening the File System Object'
EXECUTE @HR = sp_OACreate 'Scripting.FileSystemObject' , @objFileSystem OUT
if @HR=0 Select @objErrorObject=@objFileSystem, @strErrorMessage='Opening file "'+@path+'\'+@filename+'"',@command=@path+'\'+@filename
if @HR=0 execute @HR = sp_OAMethod @objFileSystem , 'OpenTextFile'
, @objTextStream OUT, @command,1,false,0--for reading, FormatASCII
WHILE @HR=0
BEGIN
if @HR=0 Select @objErrorObject=@objTextStream,
@strErrorMessage='finding out if there is more to read in "'+@filename+'"'
if @HR=0 execute @HR = sp_OAGetProperty @objTextStream, 'AtEndOfStream', @YesOrNo OUTPUT
IF @YesOrNo<>0 break
if @HR=0 Select @objErrorObject=@objTextStream,
@strErrorMessage='reading from the output file "'+@filename+'"'
if @HR=0 execute @HR = sp_OAMethod @objTextStream, 'Readline', @String OUTPUT
INSERT INTO @file(line) SELECT @String
END
if @HR=0 Select @objErrorObject=@objTextStream,
@strErrorMessage='closing the output file "'+@filename+'"'
if @HR=0 execute @HR = sp_OAMethod @objTextStream, 'Close'
if @HR<>0
begin
Declare
@source varchar(255),
@Description Varchar(255),
@Helpfile Varchar(255),
@HelpID int
EXECUTE sp_OAGetErrorInfo @objErrorObject,
@source output,@Description output,@Helpfile output,@HelpID output
Select @strErrorMessage='Error whilst '
+coalesce(@strErrorMessage,'doing something')
+', '+coalesce(@Description,'')
insert into @file(line) select @strErrorMessage
end
EXECUTE sp_OADestroy @objTextStream
-- Fill the table variable with the rows for your result set
RETURN
END

your method works well also. Thanks....

http://www.simple-talk.com/sql/t-sql-programming/reading-and-writing-files-in-sql-server-using-t-sql/
Post #1007149
Posted Tuesday, October 19, 2010 5:02 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 6:02 PM
Points: 21,657, Visits: 15,326
Thanks - I needed something like this.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1007394
Posted Tuesday, October 19, 2010 7:21 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, April 18, 2013 6:12 PM
Points: 4, Visits: 34
Hi Sarah

While testing I found that dbo.[Load_RNFile] is not defined anywhere in your script and am bit lost (or may be am totally wrong) Could you please review and profide the schema of dbo.[Load_RNFile]

Thanks
Post #1007410
Posted Tuesday, October 19, 2010 10:32 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, May 20, 2014 4:43 PM
Points: 11, Visits: 77
The format for the table would look like this:
 CREATE TABLE [dbo].[Load_RNFile](
[Id] [int] IDENTITY(1,1) NOT NULL,
[EverythingElse] [varchar](500) NULL,
CONSTRAINT [PK_Load_RN] PRIMARY KEY CLUSTERED
(
[Id] ASC
)
) ON [PRIMARY]

GO

Post #1007443
Posted Friday, October 22, 2010 7:22 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, November 21, 2013 7:31 AM
Points: 3, Visits: 40
Nice article. Any thoughts on doing the reverse, dynamically exporting data to tab-delim or csv files? I support an MDM db with application specific views. Occassionally we have to export the view results to flat files for ftp outside the organization. I'd like to develop a single SSIS pkg that would create the export files based on the view columns.
Post #1009168
Posted Friday, October 22, 2010 12:01 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 25, 2014 11:35 AM
Points: 4, Visits: 88
You can export data directly from a stored procedure by using xp_cmdshell and running a bcp command on the operating system.
Post #1009409
Posted Saturday, October 23, 2010 12:38 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 10:45 AM
Points: 536, Visits: 758
This is a great article! I am facing a very similar task right now and will certainly use many of these ideas. One request. If it's not too much trouble, please provide an SSIS leveraging this approach (the screen shots are in the article by an actual .dtsx would be helpful).

Thank you!



Post #1009634
Posted Monday, October 25, 2010 11:10 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, May 20, 2014 4:43 PM
Points: 11, Visits: 77
I believe you could use this approach for exporting as well. You would be going back the other way in terms of concatenating all your fields together and then dumping them into the flat file into the "everythingelse" field so that SSIS could pick up the flat data. You would still want to create some sort of dictionary but it might mainly be used for converting the fields to varchar/nvarchar so that they could be concatenated. You also may need to create some special functions for concatenating based on fixed length versus delimited. If you created your business tables that mapped exactly to the fields you wanted to export, getting them concatenated into the Load_* tables wouldn't be too bad. Then your SSIS package would read the criteria of which table to pick up and export that. File names could be generated using information in the database.
Post #1010197
Posted Wednesday, October 27, 2010 2:54 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 12:57 PM
Points: 33,206, Visits: 15,361
I have added the DTSX file to the article if you are interested in viewing Sarah's work.






Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1011900
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse