June 25, 2008 at 4:37 pm
Let me preface this with I am a beginner with SSIS with limited knowledge of T-SQL that has been tasked with the following challenge. Essentially, be able to read multiple flat files from a directory and create a separate SQL table from each file. The flat files and SQL tables will have the same record format and the flat filenames are not known in advance.
My problem is I know vaguely what needs to be done namely using For Each Loop in the Control Flow, but being unfamiliar with the intricacies of SSIS, I need an example to guide me step by step.
Please help because I am the only person with working knowledge of SSIS in our organization and that's not saying much. 🙂
Gerald
June 26, 2008 at 9:09 pm
Do all the files have the same format? Is the format known in advance?
June 26, 2008 at 10:14 pm
gerald.duncan (6/25/2008)
and create a separate SQL table from each file
[font="Arial Black"]WHY???[/font] :blink: Especially when they all have the same file format...
--Jeff Moden
June 27, 2008 at 6:23 am
Yes the flat files and SQL tables all have the same format. I've been able to create a package which reads and loads the flat files to a single SQL table. My goal is to create a table for each flat file. The part that I haven't been able to figure out is the setting of the variable in the OLE DB destination editor. When I give the variable a default value, I don't get an evaluation error, but get a runtime Openrowset error.
If someone has experience using a variable in the OLE DB destination, I would appreciate your assistance.
June 27, 2008 at 6:44 am
gerald.duncan (6/27/2008)
My goal is to create a table for each flat file.
You still haven't explained why you want to do that, Gerald. Not trying to bust your chops... trying to figure out the best way to do things.
--Jeff Moden
June 27, 2008 at 6:51 am
Very simple. I want to iterate through a directory of flat files and create SQL tables from them. Clear enough?
June 27, 2008 at 8:57 pm
An alternate approach is to have a work table that you drop and create each time that has a standard name (wkFileData, for example). Load the data here first.
Then execute stored procedure
sp_rename 'wkFileData', 'use variables for new name'
sp_rename is in the master database.
June 28, 2008 at 7:40 am
gerald.duncan (6/27/2008)
Very simple. I want to iterate through a directory of flat files and create SQL tables from them. Clear enough?
Actually, no. This is what you are trying to do, not why you are trying to do it.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 28, 2008 at 9:57 am
rbarryyoung (6/28/2008)
gerald.duncan (6/27/2008)
Very simple. I want to iterate through a directory of flat files and create SQL tables from them. Clear enough?Actually, no. This is what you are trying to do, not why you are trying to do it.
Barry is absolutely correct... this is what you're trying to do, not why you're trying to do it.
Gerald, you need to identify WHY each file has to live in it's own table even though the format of each file and table may be identical. Looking at the problem a bit backwards, why can't these files all be loaded into the same table?
--Jeff Moden
June 29, 2008 at 1:17 pm
The "WHY" is a business requirement that is really none of your concern. I've asked about a solution for a problem and all I'm getting is why do I want to do it this way. Thanks for nothing!!
June 29, 2008 at 5:33 pm
gerald.duncan (6/29/2008)
The "WHY" is a business requirement that is really none of your concern. I've asked about a solution for a problem and all I'm getting is why do I want to do it this way. Thanks for nothing!!
Stop being a smart guy and settle down. 😉 We want to help you more than what you are asking for. Many people come on asking questions about how they can hang themselves with an SQL rope... we're trying to keep that from happening to you.
And when the hell have you known people that write business requirements to be right about how to do things in SQL?
Now, tell us why the business requirements require such a stupid thing to be done in SQL and we'll either help you with that or show you a better way.
--Jeff Moden
June 30, 2008 at 8:16 pm
I hope this copies ok from my Word document. I was able to create and load tables from flat files where the names are not known in advance.
If you have trouble with something refusing to validate and you have made sure everything is spelled correctly, check in its properties window for a DelayValidation field set to false and change it to true. Then try again.
I created the following variables:
1. FilePath, string, value is the path where the files are located
2. Server, string, value is the server where the data is to be loaded
3. Database, string, value is the database where the data is to be loaded
4. FileName, string, no value since it will be populated by the for each loop
5. TableName, string, no value since a script will build it
6. SQLRenameWorkTable, string, no value since a script will build it
7. SQLDropNewTable, string, no value since a script will build it
TableName is the FileName without the file extension. Any unwanted characters could also be dropped.
SQLRenameWorkTable executes the stored procedure sp_rename to rename the work table to the new TableName. It is built in a script.
SQLDropNewTable checks for the existence of the new table and deletes it. You may possibly want to do something different if the table already exists.
I defined the following connection managers:
1. InputFile which is a flat file connection where I specified the file layout. In my example I just defined 2 fields, field1 and field2.
In the expressions in its properties window, I specified
a. ConnectionString as
@[User::FilePath] + "\\" + @[User::FileName]
2. FileTables is an OLE DB connection. For its expressions, I entered the following:
a. InitialCatalog is @[User::Database]
b. ServerName is @[User::Server]
On the Control Flow I placed the following:
A for each loop where the expressions of Directory is set to FilePath and the variable mapping is set to FileName. In other words, it returns the files in FilePath and puts their names (without the path but with the extension) in FileName.
Inside the for each loop I placed the following in this order and connected them:
SCR_CleanFileName is a script task that builds Tablename, SQLRenameWorkTable, and SQLDropNewTable from FileName.
On the script page, ReadOnlyVariables = FileName
ReadWriteVariables = Tablename,SQLRenameWorkTable,SQLDropNewTable
The script contains:
Public Sub Main()
Dim wFileName As String
Dim wDotPosition As Integer = 0
Dim wTableName As String
'Save variable FileName in work field wFileName
wFileName = Dts.Variables("FileName").Value.ToString
'Find dot and assume everything after it is file extension
wDotPosition = InStr(wFileName, ".")
If wDotPosition > 0 Then
'Omit file extension
wTableName = Left(wFileName, wDotPosition - 1)
Else
wTableName = wFileName
End If
'The next statement could be used to remove an invalid character
'wTableName = Replace(wTableName, "?", "")
Dts.Variables("TableName").Value = wTableName
Dts.Variables("SQLDropNewTable").Value = "IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'" + wTableName + "') AND type in (N'U')) drop table " + wTableName
Dts.Variables("SQLRenameWorkTable").Value = "exec sp_rename 'wkFileData', '" + wTableName + "'"
Dts.TaskResult = Dts.Results.Success
End Sub
SQL_RebuildWorkTable is an Execute SQL task.
Connection = FileTables (This can also be set in Expressions)
SQLSourceType = Direct input
SQLStatement =
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[wkFileData]') AND type in (N'U'))
DROP TABLE [dbo].[wkFileData]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[wkFileData](
[field1] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[field2] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
DFT_LoadToWorkTable is a data flow task.
It has a flat file source that uses the InputFile connection manager and an OLE destination that uses the FileTables connection manager and specifies table wkFileData.
SQL_DropNewTable is an Execute SQL task where
Connection = FileTables
SQLSourceType = Variable
SourceVariable = User::SQLDropNewTable
SQL_RenameTable is and Execute SQL task where
Connection = FileTables
SQLSourceType = Variable
SourceVariable = User::SQLRenameWorkTable
Script Task documentation:
To find a list of available commands when in the Design Script screen,
1. In the lower left there are 2 tabs, Project Explorer and Class View. Click on Class View.
2. Expand ScriptTask_(whatever) in the upper left window.
3. Expand References.
4. Expand Microsoft.VisualBasic.
5. Expand Microsoft.
6. Expand VisualBasic
7. You will see a list of available functions and function groups.
8. Scroll down to Strings and click on it. You now see a list of string functions in the lower window.
9. To get a definition, right click on the function.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply