SSIS - Script Task, when to use them? - Sudeep's Domain

SSIS - Script Task, when to use them? - Sudeep's Domain

Read on to know when you need to use Script Task or Script Component.

SSIS - Reading object variable in Script task - Sudeep's Domain

SSIS - Reading object variable in Script task - Sudeep's Domain

BI Quiz - Select distinct files from a list of files in a folder - Sudeep's Domain

Test your SSIS knowledge by solving this scenario. Gain a chance to win goodies and know SSIS..

SSIS – Process files in Sequence - Sudeep's Domain

I got a very interesting scenario in Ask Sudeep section of BeyondRelational.com. Let me share the scenario first and then will explain how to go about it.
Scenario:
There is a folder which contains a number of files. The file name follows the following naming convention . Alphanumeric could be any valid alphanumeric characters and the Number part would have just numeric values and both would be separated by a period [.]. Examples of file name file1Name.1, file1Name.2, file2Name.5 etc. Now the requirement says that for a given group of Alphanumeric part which are same we need to start processing the files from the file having the minimum numeric part and keep on processing the file till we get the numeric part incrementing by one for the files. Then move to the next set of files with another Alphanumeric part. Better to see an example, will give a clear picture.
List of files in the folder:



a.1
a.2
a.4
a.5
b.10
b.11
b.15
b.16



There can be 2 sets of files based on the alphanumeric part once with a and the other with b. From the above list we need to process just the following files:
a.1
a.2
b.10
b.11


As there is a break in the sequence for the file group “a” as a.3 is missing and similarly b.12 is missing so no files with larger numeric part will be processed.


Read on....

SSIS - Script Component, add missing column or ignore extra columns

Today I got a query asking me if we can have a script component which reads data from a text file which is supposed to have 5 input columns. However the data that they receive has at time extra columns or less columns for certain records. So the question arises how do we go about this.
Input:

1.Col1,Col2,Col3,Col4,Col5

2.1,2,3,4,5

3.1,2,3,4,5,6,7,

4.1,2,3

Output:

1.Col4    Col3    Col2    Col1    Col

2.5   4   3   2   1

3.5   4   3   2   1

4.NULL    NULL    3   2   1

There are 3 ways that come to my mind straight away.

1. Script Component
2. Flat file source which reads all data into one column and then splits them based on index of the comma in a derived column
3. Flat file source with delimited settings to read just 5 columns.

READ ON

SSIS - Upload data to Oracle using SQL Loader - Sudeep's Domain

SSIS - Upload data to Oracle using SQL Loader - Sudeep's Domain