Converting Access database to SQL Server w/ flat files

I recently received a large Access database that I needed to do a lot
of analysis on. Since I prefer working with SQL Server (to put things
mildly) and I dislike writing VB/VBA code, I thought I'd share my
solution. I know someone will find this useful when searching the
archives.
There are probably much more efficient ways of doing this, but this
solution works well enough for me. There are three functions here that
simplify converting an Access schema to SQL Server and loading the data
into that schema. The first function generates all of the CREATE TABLE
statements for SQL Server. There may be some data types off the list,
since I only used the ones I needed for this conversion. Also, there
are no data contraints or indexes defined. The second dumps all the
data in all the tables to pipe-delimitted flat files and the third
generates all the BULK INSERT statements to load the data into SQL
Server. While I could've left the table names as they were and used
brackets or quotes, I hate writing them in my SQL, so I decided to
replace bad characters with underscores. I hope I've saved someone some
time here.
-Alan
'By Alan Samet 3/8/2006
'Use this code at your own risk. I claim no liability. If it works for
you and you share it
'please give me credit. If it messes things up, take the credit
yourself.
'
'There are three functions you need to use:
' GenerateSqlServerSchema -- creates the schema for SQL Server
' ExportFlatFiles -- creates tab-delimitted flat files to bulk insert
' GenerateBulkInsertStatements -- generates the BULK INSERT
statements
'
'All paths need to be fully-qualified. Where a directory is an
argument,
'don't append the ending backslash (\)
Public Function GetSqlServerDataType(accessDataType As Integer, size As
Integer) As String
Select Case accessDataType
Case dbInteger: '3
GetSqlServerDataType = "INT"
Case dbLong: '4
GetSqlServerDataType = "BIGINT"
Case dbSingle, dbDouble: '6, 7
GetSqlServerDataType = "FLOAT"
Case dbCurrency: '5
GetSqlServerDataType = "MONEY"
Case dbDate: '8
GetSqlServerDataType = "DATETIME"
Case dbText: '10
GetSqlServerDataType = "VARCHAR(" & size & ")"
Case Else
Err.Raise 0, , "Unrecognized Data Type: " & accessDataType
End Select
End Function
Function CleanName(name As String)
'Dim c As New Collection
'c.Add "_", "-"
'c.Add "_", " "
'c.Add "", "(R)"
'c.Add "_", "/"
'Dim s As Variant
CleanName = name
'For Each s In c
' CleanName = Replace(CleanName, s, c(s))
'Next
CleanName = Replace(CleanName, "-", "_")
CleanName = Replace(CleanName, " ", "_")
CleanName = Replace(CleanName, "(R)", "")
CleanName = Replace(CleanName, "/", "_")
CleanName = Replace(CleanName, "_&", "_And")
CleanName = Replace(CleanName, "1st", "First")
CleanName = Replace(CleanName, "2nd", "Second")
CleanName = Replace(CleanName, "3rd", "Third")
CleanName = Replace(CleanName, "4th", "Fourth")
End Function
Public Function GenerateSqlServerSchema(databasePath As String) As
String
Dim db As Database
Set db = OpenDatabase(databasePath)
'Dim types(50) As Boolean
Dim t As TableDef
Dim c As Field
Dim createTableSql As String
Dim fullScript As String
For Each t In db.TableDefs
If Left(t.name, 4) <> "MSys" Then
'Debug.Print t.Name
createTableSql = "CREATE TABLE " & CleanName(t.name) &
vbCrLf & "(" & vbCrLf
Dim bIsFirst As Boolean
bIsFirst = True
For Each c In t.Fields
If Not bIsFirst Then createTableSql = createTableSql &
","
bIsFirst = False
createTableSql = createTableSql & vbTab &
CleanName(c.name) & vbTab & GetSqlServerDataType(c.Type, c.size) &
vbCrLf
'types(c.Type) = True
Next
createTableSql = createTableSql & ")"
fullScript = fullScript & vbCrLf & vbCrLf & createTableSql
& vbCrLf & vbCrLf & "GO"
End If
Next
GenerateSqlServerSchema = fullScript
' Dim i As Integer
' For i = 0 To UBound(types)
' If types(i) Then Debug.Print i
' Next
End Function
Public Sub ExportFlatFiles(databasePath As String, rootExportPath As
String)
Dim db As Database
Set db = OpenDatabase(databasePath)
Dim t As TableDef
Dim i As Integer
For Each t In db.TableDefs
If Left(t.name, 4) <> "MSys" Then
Open rootExportPath & "/" & CleanName(t.name) & ".pipe" For
Output As #1
Dim values() As String
ReDim values(t.Fields.Count - 1) As String
With t.OpenRecordset(dbOpenForwardOnly)
While Not .EOF
For i = 0 To .Fields.Count - 1
values(i) = Nz(.Fields(i).Value, "")
Next
Print #1, Join(values, "|")
.MoveNext
Wend
End With
Close #1
End If
Next
db.Close
End Sub
Public Function GenerateBulkInsertStatements(databasePat
h As String,
rootExportPath As String) As String
Dim db As Database
Set db = OpenDatabase(databasePath)
Dim t As TableDef
For Each t In db.TableDefs
If Left(t.name, 4) <> "MSys" Then
GenerateBulkInsertStatements = GenerateBulkInsertStatements
& _
"RAISERROR('Loading " & CleanName(t.name) & "', 10,
10)" & vbCrLf & _
"BULK INSERT " & CleanName(t.name) & " FROM '" & _
rootExportPath & "\" & CleanName(t.name) & _
".pipe' WITH (ROWTERMINATOR='\n',FIELDTERMINATOR='|')
"
& vbCrLf
End If
Next
db.Close
End FunctionIs there a reason you didn't just use Data Transformation Services?
You could just create the package and if necessary even run
the package from your vbscript.
Why go through the trouble of doing all this manually?
Robbe Morris - 2004-2006 Microsoft MVP C#
Earn money answering .NET questions
http://www.eggheadcafe.com/forums/merit.asp
"Alan Samet" <alansamet@.gmail.com> wrote in message
news:1141871152.130312.288310@.i39g2000cwa.googlegroups.com...
>I recently received a large Access database that I needed to do a lot
> of analysis on. Since I prefer working with SQL Server (to put things
> mildly) and I dislike writing VB/VBA code, I thought I'd share my
> solution. I know someone will find this useful when searching the
> archives.
> There are probably much more efficient ways of doing this, but this
> solution works well enough for me. There are three functions here that
> simplify converting an Access schema to SQL Server and loading the data
> into that schema. The first function generates all of the CREATE TABLE
> statements for SQL Server. There may be some data types off the list,
> since I only used the ones I needed for this conversion. Also, there
> are no data contraints or indexes defined. The second dumps all the
> data in all the tables to pipe-delimitted flat files and the third
> generates all the BULK INSERT statements to load the data into SQL
> Server. While I could've left the table names as they were and used
> brackets or quotes, I hate writing them in my SQL, so I decided to
> replace bad characters with underscores. I hope I've saved someone some
> time here.
> -Alan
> 'By Alan Samet 3/8/2006
> 'Use this code at your own risk. I claim no liability. If it works for
> you and you share it
> 'please give me credit. If it messes things up, take the credit
> yourself.
> '
> 'There are three functions you need to use:
> ' GenerateSqlServerSchema -- creates the schema for SQL Server
> ' ExportFlatFiles -- creates tab-delimitted flat files to bulk insert
> ' GenerateBulkInsertStatements -- generates the BULK INSERT
> statements
> '
> 'All paths need to be fully-qualified. Where a directory is an
> argument,
> 'don't append the ending backslash (\)
> Public Function GetSqlServerDataType(accessDataType As Integer, size As
> Integer) As String
> Select Case accessDataType
> Case dbInteger: '3
> GetSqlServerDataType = "INT"
> Case dbLong: '4
> GetSqlServerDataType = "BIGINT"
> Case dbSingle, dbDouble: '6, 7
> GetSqlServerDataType = "FLOAT"
> Case dbCurrency: '5
> GetSqlServerDataType = "MONEY"
> Case dbDate: '8
> GetSqlServerDataType = "DATETIME"
> Case dbText: '10
> GetSqlServerDataType = "VARCHAR(" & size & ")"
> Case Else
> Err.Raise 0, , "Unrecognized Data Type: " & accessDataType
> End Select
> End Function
> Function CleanName(name As String)
> 'Dim c As New Collection
> 'c.Add "_", "-"
> 'c.Add "_", " "
> 'c.Add "", "(R)"
> 'c.Add "_", "/"
> 'Dim s As Variant
> CleanName = name
> 'For Each s In c
> ' CleanName = Replace(CleanName, s, c(s))
> 'Next
> CleanName = Replace(CleanName, "-", "_")
> CleanName = Replace(CleanName, " ", "_")
> CleanName = Replace(CleanName, "(R)", "")
> CleanName = Replace(CleanName, "/", "_")
> CleanName = Replace(CleanName, "_&", "_And")
> CleanName = Replace(CleanName, "1st", "First")
> CleanName = Replace(CleanName, "2nd", "Second")
> CleanName = Replace(CleanName, "3rd", "Third")
> CleanName = Replace(CleanName, "4th", "Fourth")
> End Function
> Public Function GenerateSqlServerSchema(databasePath As String) As
> String
> Dim db As Database
> Set db = OpenDatabase(databasePath)
> 'Dim types(50) As Boolean
> Dim t As TableDef
> Dim c As Field
> Dim createTableSql As String
> Dim fullScript As String
> For Each t In db.TableDefs
> If Left(t.name, 4) <> "MSys" Then
> 'Debug.Print t.Name
> createTableSql = "CREATE TABLE " & CleanName(t.name) &
> vbCrLf & "(" & vbCrLf
> Dim bIsFirst As Boolean
> bIsFirst = True
> For Each c In t.Fields
> If Not bIsFirst Then createTableSql = createTableSql &
> ","
> bIsFirst = False
> createTableSql = createTableSql & vbTab &
> CleanName(c.name) & vbTab & GetSqlServerDataType(c.Type, c.size) &
> vbCrLf
> 'types(c.Type) = True
> Next
> createTableSql = createTableSql & ")"
> fullScript = fullScript & vbCrLf & vbCrLf & createTableSql
> & vbCrLf & vbCrLf & "GO"
> End If
> Next
> GenerateSqlServerSchema = fullScript
> ' Dim i As Integer
> ' For i = 0 To UBound(types)
> ' If types(i) Then Debug.Print i
> ' Next
> End Function
> Public Sub ExportFlatFiles(databasePath As String, rootExportPath As
> String)
> Dim db As Database
> Set db = OpenDatabase(databasePath)
> Dim t As TableDef
> Dim i As Integer
> For Each t In db.TableDefs
> If Left(t.name, 4) <> "MSys" Then
> Open rootExportPath & "/" & CleanName(t.name) & ".pipe" For
> Output As #1
> Dim values() As String
> ReDim values(t.Fields.Count - 1) As String
> With t.OpenRecordset(dbOpenForwardOnly)
> While Not .EOF
> For i = 0 To .Fields.Count - 1
> values(i) = Nz(.Fields(i).Value, "")
> Next
> Print #1, Join(values, "|")
> .MoveNext
> Wend
> End With
> Close #1
> End If
> Next
> db.Close
> End Sub
> Public Function GenerateBulkInsertStatements(databasePat
h As String,
> rootExportPath As String) As String
> Dim db As Database
> Set db = OpenDatabase(databasePath)
> Dim t As TableDef
> For Each t In db.TableDefs
> If Left(t.name, 4) <> "MSys" Then
> GenerateBulkInsertStatements = GenerateBulkInsertStatements
> & _
> "RAISERROR('Loading " & CleanName(t.name) & "', 10,
> 10)" & vbCrLf & _
> "BULK INSERT " & CleanName(t.name) & " FROM '" & _
> rootExportPath & "\" & CleanName(t.name) & _
> ".pipe' WITH (ROWTERMINATOR='\n',FIELDTERMINATOR='|')
"
> & vbCrLf
> End If
> Next
> db.Close
> End Function
>|||Well, for one, I've never had any use for DTS. From my understanding,
you have to use that cumbersome GUI to make DTS packages. With the
exception of well-done tools like Interface Builder on OSX, I don't
like using a GUI to develop my applications -- especially when I can't
work with code. I've been able to do anything that DTS can do using SQL
Server Agent and script (preferably Python). The code that I just
posted only took a couple hours to write, so it really wasn't that much
work, aside from the unpleasantness of working with Access and VBA.
Also, in this situation, I don't want my SQL Server touching this
Access database. Since there are a lot of objects that I haven't looked
at in this database, I was running it on a virtual machine, pulling the
data out and then loading the data onto my development machine. This
method totally isolates the two.
-Alan|||No offense, but I have to agree with Robbe - DTS may have been a better
choice. The GUI tools reduce the time it takes to build stuff and
encapsulates all sorts of error checking etc. While it took a couple
of hours for you to write this manually, someone familiar with DTS
would probably accomplish the same result as the code posted in about 5
to 10 minutes. (And with fewer debugging steps - unless like most of
us you write perfect code all the time. LOL!)|||The Access Upsizing Wizard is also a real easy to use tool. The last
time I used it was probably 6 or 8 yrs ago... surely it is even better
now though. Some documentation here:
http://support.microsoft.com/defaul...B;EN-US;Q325017|||I do write perfect code all the time. It's why I dislike being forced
to use Microsoft's development tools so much.
-Alan

Convert Table in Stored Procedure

I have a flat table from 1 of the clients with a lot of fields (more then 100) like this

Item F1 F2 F3 ... F(N)
--------------
100 X X
101 X

There are more 10000 records , X is the data inside the field.

I need to quickly convert it to this table

Item FieldNumber Value
---------
100 1 X
100 2 X
101 1 X

Any ideas ?

Thanks

MikhailHi,

My suggestion would be export the table into Excel, in Excel using formula, you can change either from horizontal to vertical or likewise.

After that, you can import back to database.

Regards
Ravi|||I am not sure what are you proposing..how will I do it in Excel ?

Any other ideas ?

Mikhail|||You can self join for each column

Select
Item,
FieldNumber.F1,
MyValue.F2
from
Table FieldNumber inner join Table MyValue on FieldNumber.Item = MyValue.Item

get the idea?

convert from char(6) to datetime

Hi there
I receive a very much unstructured flat file, which I import into a SQL
Server database using DTS. After the import, one of the flat file columns is
in the format char(6) rather than datetime. The problem seems to be that the
text file column is in the format 'ddmmyy'. When I use the CONVERT statement
(ie. SELECT CONVERT(datetime, WEDate) As Expr1) I get the error message that
"The conversion of char data type to smalldatetime data type resulted in an
out-of-range smalldatetime value".
Any ideas?
Best regards
Loane
Loane,
Try this (untested), using one of the format codes
from the Books Online article CAST and CONVERT:
CONVERT(datetime,RIGHT(WEDate,2)+SUBSTRING(WEDate, 3,2)+LEFT(WEDate,2),12)
Loane Sharp wrote:

>Hi there
>I receive a very much unstructured flat file, which I import into a SQL
>Server database using DTS. After the import, one of the flat file columns is
>in the format char(6) rather than datetime. The problem seems to be that the
>text file column is in the format 'ddmmyy'. When I use the CONVERT statement
>(ie. SELECT CONVERT(datetime, WEDate) As Expr1) I get the error message that
>"The conversion of char data type to smalldatetime data type resulted in an
>out-of-range smalldatetime value".
>Any ideas?
>Best regards
>Loane
>
>

convert from char(6) to datetime

Hi there
I receive a very much unstructured flat file, which I import into a SQL
Server database using DTS. After the import, one of the flat file columns is
in the format char(6) rather than datetime. The problem seems to be that the
text file column is in the format 'ddmmyy'. When I use the CONVERT statement
(ie. SELECT CONVERT(datetime, WEDate) As Expr1) I get the error message that
"The conversion of char data type to smalldatetime data type resulted in an
out-of-range smalldatetime value".
Any ideas?
Best regards
LoaneLoane,
Try this (untested), using one of the format codes
from the Books Online article CAST and CONVERT:
CONVERT(datetime,RIGHT(WEDate,2)+SUBSTRI
NG(WEDate,3,2)+LEFT(WEDate,2),12)
Loane Sharp wrote:

>Hi there
>I receive a very much unstructured flat file, which I import into a SQL
>Server database using DTS. After the import, one of the flat file columns i
s
>in the format char(6) rather than datetime. The problem seems to be that th
e
>text file column is in the format 'ddmmyy'. When I use the CONVERT statemen
t
>(ie. SELECT CONVERT(datetime, WEDate) As Expr1) I get the error message tha
t
>"The conversion of char data type to smalldatetime data type resulted in an
>out-of-range smalldatetime value".
>Any ideas?
>Best regards
>Loane
>
>

Convert Date Format of 00-XXX-00 to NULL

Let me start by saying that I'm brand new to SQL Server 2005 and SSIS.
I'm using the import wizard in SQL2005 to import from a flat file into a table and everything works fine except for dates. A typical date in my flat file is 01-JAN-06. 01 represents the day of the week, JAN represents the month and 06 represents the year. The flat file also contains date values of 00-XXX-00 which represent no date. For example a column containing last purchase date data would look like this:

"DateOfLastOrder"
"01-JAN-06"
"02-JAN-06"
"00-XXX-00"
"03-DEC-05"

The value of 00-XXX-00 means that there is no purchase date.

I want to bring these columns into my table and replace the 00-XXX-00 values with a NULL.

The table Data Type is datetime.

If I use the import wizard using the example above I get this error message:

- Copying to [cpstest].[dbo].[date] (Error)
Messages
Error 0xc0202009: Data Flow Task: An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Invalid character value for cast specification".
(SQL Server Import and Export Wizard)
Error 0xc020901c: Data Flow Task: There was an error with input column "DateOfLastOrder" (32) on input "Destination Input" (26). The column status returned was: "The value could not be converted because of a potential loss of data.".
(SQL Server Import and Export Wizard)
Error 0xc0209029: Data Flow Task: The "input "Destination Input" (26)" failed because error code 0xC0209077 occurred, and the error row disposition on "input "Destination Input" (26)" specifies failure on error. An error occurred on the specified object of the specified component.
(SQL Server Import and Export Wizard)
Error 0xc0047022: Data Flow Task: The ProcessInput method on component "Destination - date" (13) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.
(SQL Server Import and Export Wizard)
Error 0xc0047021: Data Flow Task: Thread "WorkThread0" has exited with error code 0xC0209029.
(SQL Server Import and Export Wizard)

If I remove the 00-XXX-00 values and import something like this:

"DateOfLastOrder"
"01-JAN-06"
"02-JAN-06"
"03-DEC-05"

The import is successfull and the dates look correct in a querry.

SELECT *
FROM date

date
--
2006-01-01 00:00:00.000
2006-01-02 00:00:00.000
2005-12-03 00:00:00.000

(3 row(s) affected)

Does anyone know how I should go about getting these date columns into a datetime table and convert the 00-XXX-00 values into NULLs?

Thank you,

Ryan

Ryan,

The import wizard gives you the option to save the package. You should do this and then open it up in Business Intelligence Development Studio (BIDS) so that you can edit it.

In there you will find a data-flow task which is the thing that does the work. It is made up of things called components which can change the data before it gets inserted to the destination.

You need to introduce a component called a Derived Column component. That can take some input data and change it in-memory. It uses an expression language to do this. In your case the expression wants to be something like:

SUBSTRING(<input-col>, 4, 3) == "XXX" ? NULL(DT_STR) : <input-col>

Hope that helps!

-Jamie

|||Could you please break down what that expression does or point me to a reference for the expression language?
|||

Don't mean to butt in here, but what Jamie has listed here:

Jamie Thomson wrote:

SUBSTRING(<input-col>, 4, 3) == "XXX" ? NULL(DT_STR) : <input-col>

-Jamie

uses the SUBSTRING function (like similar functions in VB or C#) and it returns a subtring of the string <input-col> starting at the 4th character of the string and returns 3 characters. In your situation where your specific date that was throwing an error, you would want to look for the ones with "XXX" at this position.

So if the substring of your date column equals "XXX" then return a null value (null of type string) else return your original column value.

The "?" and ":" are somewhat equivalent to "then" and "else." and in this expression language, the "if" is implied.

Hope this helps.

Mark

http://spaces.msn.com/mgarnerbi

|||

There is a very good expression reference in BOL. In fact, that is the only reference seeing as it is rather good - another one aint really needed.

-Jamie

Convert character to NULL

I am loading a flat file to a table but I also need to scrub the data a bit before the data hits the table. The main update required is converting a dot (.) character to a null value. The source file is using this character to indicate a blank. I know I can use the Dervived Column Transformation, but I have quite a few columns which will take a while to manually configure. Is there another transformation option that anyone can point me to?

Thanks

Under this scenario, is that the only value in the column? Or are you searching/replacing (.) with NULLs?|||

I am using a conditional evaluation in the derived column transformation:

[Coulmn1] == "." ? (DT_STR,50,1252)NULL(DT_STR,50,1252) : [Column1]

The columns would never have a value that contains a dot, only values or the dot

|||

crancilio wrote:

I am loading a flat file to a table but I also need to scrub the data a bit before the data hits the table. The main update required is converting a dot (.) character to a null value. The source file is using this character to indicate a blank. I know I can use the Dervived Column Transformation, but I have quite a few columns which will take a while to manually configure. Is there another transformation option that anyone can point me to?

Thanks

Not that I am awre of. I am afraid you have to do the same thing for every column affected by that logic. Perhaps, you could try to use an script component where you could use the magic of copy and paste...

|||Let's use a script component instead of a derived column. You'll be very happy with the following solution:

Instead of the derived column, add a script component, set it to be a transformation.

Select the columns you wish to work with. Set their usage types to "ReadWrite." ONLY select the columns you wish to process with this logic.

Below is the script. I don't understand it fully, and I've hacked something that our forum user, jaegd wrote:

Code Snippet

Imports System
Imports System.Data
Imports System.Math
Imports System.Text
Imports System.Collections.Generic
Imports Microsoft.SqlServer.Dts.Pipeline
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
'Note: this code was originally written/posted by the SSIS forum user, jaegd. http://forums.microsoft.com/MSDN/User/Profile.aspx?UserID=133544&SiteID=1
'Credit has been given where credit is due
'Original post: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=864401&SiteID=1

Public Class ScriptMain
Inherits UserComponent

Private inputBuffer As PipelineBuffer
Private cols As Dictionary(Of Int32, ColumnInfo) = New Dictionary(Of Int32, ColumnInfo)
Private currentColumnInfo As ColumnInfo = New ColumnInfo

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
'Setup control counter
Dim counter As Integer = 0
'Loop through segments
'MsgBox(currentColumnInfo.colIndex.ToString)
For Each currentcolumn As KeyValuePair(Of Int32, ColumnInfo) In cols
'MsgBox(inputBuffer.GetString(currentcolumn.Key))
If (inputBuffer.GetString(currentcolumn.Key)) = "." Then
inputBuffer.SetString(currentcolumn.Key, Chr(0))
End If
Next

End Sub

Public Overrides Sub ProcessInput(ByVal InputID As Integer, ByVal Buffer As Microsoft.SqlServer.Dts.Pipeline.PipelineBuffer)
' Get the Pipeline Buffer for subsequent ordinal column access
inputBuffer = Buffer
MyBase.ProcessInput(InputID, Buffer)
End Sub

Public Overrides Sub PreExecute()
BuildColumnDictionary()
End Sub

Private Sub BuildColumnDictionary()
Dim indexes() As Integer
Dim input As IDTSInput90
Dim col As IDTSInputColumn90
Dim offset As Integer = 0

input = Me.ComponentMetaData.InputCollection(0)
'presumes GetColumnIndexes order matches iterator order
'as BufferManager is not available to my knowledge in ScriptComponent
indexes = Me.GetColumnIndexes(input.ID)
For Each col In input.InputColumnCollection
Dim columnStructure As New ColumnInfo
With columnStructure
.colName = col.Name
.colLength = col.Length
.colIndex = indexes(offset)
'Normally, BufferManager would be used, but its not exposed in Script Component
.colPrecision = col.Precision
.colScale = col.Scale
.colType = col.DataType
End With
cols.Add(indexes(offset), columnStructure)
offset += 1
Next
End Sub

Public Structure ColumnInfo
Dim colName As String
Dim colType As DataType
Dim colIndex As Int32
Dim colLength As Int32
Dim colPrecision As Int32
Dim colScale As Int32
End Structure

End Class

A screenshot of the results: HERE|||Note: I would LOVE it if someone would come in and simplify the code above. I just don't have the understanding of the SSIS programming model to do anything BUT hack code together. Not yet anyway.

Also, the work happens in the Input0_ProcessInputRow sub. Periods are replaced with Chr(0) which is null. Give it a shot, tweak it to however you need, etc...

You may want to trim() the columns first, before going into this transformation.|||This is great - thanks! I will let you know how it goes|||

Phil - thank you so much for your help!

This code was exactly what I was looking for. The only tweak I had to make was to use SetNull() instead of SetString() - the Chr(0) actually added an empty string rather than a null.

Thanks again!

|||

Phil Brammer wrote:

Note: I would LOVE it if someone would come in and simplify the code above. I just don't have the understanding of the SSIS programming model to do anything BUT hack code together. Not yet anyway.

Also, the work happens in the Input0_ProcessInputRow sub. Periods are replaced with Chr(0) which is null. Give it a shot, tweak it to however you need, etc...

You may want to trim() the columns first, before going into this transformation.

How's this?

Note - this is really only useful for generically accessing each column, and has no type safety, so the code only works for string columns. jaegd's original code provided a lot more information about the columns, including type information, which would allow you to add conditional logic to use the appropriate accessor (GetString, GetInt32, etc). I've also used the System.Reflection to access the buffer, but I need to test the performance a bit more before pushing that as a solution.

Code Snippet

'Note: this code was originally written/posted by the SSIS forum user, jaegd. http://forums.microsoft.com/MSDN/User/Profile.aspx?UserID=133544&SiteID=1

'Credit has been given where credit is due

'Original post: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=864401&SiteID=1

'Trimmed to smaller set of code by jwelch

Imports Microsoft.SqlServer.Dts.Pipeline

PublicClass ScriptMain

Inherits UserComponent

Private inputBuffer As PipelineBuffer

PublicOverridesSub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

Dim counter AsInteger = 0

For counter = 0 To inputBuffer.ColumnCount - 1

If (inputBuffer.GetString(counter)) = "."Then

inputBuffer.SetString(counter, Chr(0))

EndIf

Next

EndSub

PublicOverridesSub ProcessInput(ByVal InputID AsInteger, ByVal Buffer As Microsoft.SqlServer.Dts.Pipeline.PipelineBuffer)

' Get the Pipeline Buffer for subsequent ordinal column access

inputBuffer = Buffer

MyBase.ProcessInput(InputID, Buffer)

EndSub

EndClass

|||

jwelch wrote:

How's this?

Yep, I like that MUCH better. Knew jaegd had much more stuff in there, I just didn't quite know where to begin to trim it down... Ran out of time too... The golf course beckoned.