February 16, 2009 at 9:39 am
Hi all,
I'm currently trying to rewrite an old messy collection of DTS' using a much nicer and simpler SSIS package.
The background is that I currently have multiple DTS' that effectively do the same thing to multiple sets of files apart from the fact that they load to different tables.
I was wondering, is it possible in SSIS to dynamically set the text file source and database table destination based on variables if the text files have different numbers of columns in?
Ideally I would like it to loop through all the files I have and load it to the table specified for that file in the lookup table.
Am I being over optimistic here?
Any help would be greatly appreciated guys
Thanks in advance
Matt
February 17, 2009 at 9:13 am
It sounds like you are going to have to have a script task in your loop to determine which file you have. You can then set a variable and based on that variable set a conditional presidence constraint from that script task into the correct DataPump.
To set a conditional presidence constraint just double click on the Green line and set the condition
Dealing with different size rows in the same file is a very tricky task in SSIS as far as files with different column widths what I have done in the past is.
Set your file connection up and set the column delimiter to blank or something that can not appear in the file
You will get one big string then in your script task you can define your columns and put in code similar to
Dim s() As String
Dim length As Integer
s = Split(Row.InputColumn, Chr(9))
MsgBox(Chr(13) & s(0).ToString() & Chr(13) & s(1).ToString())
length = s.Length
If length >= 2 Then
Row.TransactionId = s(1).ToString()
End If
If length >= 3 Then
Row.CompanyName = s(2).ToString()
End If
If length >= 4 Then
Row.Duns = s(3).ToString()
End If
If length >= 5 Then
Row.Address1 = s(4).ToString()
End If
If length >= 6 Then
Row.City = s(5).ToString()
End If
If length >= 7 Then
Row.State = s(6).ToString()
End If
If you need a more specific answer, I can work on a live example when time permits.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply