Divide Single column to multiple columns

Question:
I have a column as : act=TUNING; svid=629; VCN=180; TI=949290689; TO=949291183; TT=494; FPID=(586653033-494)
Now i want to take the values of svid,VCN,TI,TO,TT,FPID into seperate columns.
How can i do it in ssis.
The column sizes are not standard all the time.
In one of my table in the DB , i have the token strings like token1 => svid= and token2 => ;
So now how can i use these tokens to pull the data of 629. 

Please check the above question @msdn forum

Answer:

Please write the following expression in your derived column (C2 is column name)
(click on the image for larger view)

After this you just need to replace the following with the blank string
act =
svid =
TT=
TI=
TO=

Do let me know if you need more help on it.

Load MS Access data to SQL server

Question: How to transfer MS Access data to Sql Server 2005?

Answer:
There are 2 ways for achieving this
1) SSMS: Import & Export Wizard
2) BIDS: Create a New SSIS package

First approach is easy and gives you flexibility to modify the SSIS package(If required)
I hope you can try this your self .

Let me know if you need my help for this.

Second approach: 
Open the BIDS and create a new package

Step1:
a) Create a new OLE DB Source connection.
b) Select the provider as "Native OLE DB\ Microsoft Jet 4.0 OLE DB Provider".
c) Select the Database file name. [Check the Image Below]

Step2:
a) Take a DFT.
b) In DFT drag "OLE DB Source", "OLE DB Destination" [or take any destination as per your requirement]
[Check the Image below]

Step3:
Before executing the package please makes sure that destination table should have the correct data type. [if not use data conversion task]


I hope this will help you. If you need more assistance or clarification, please leave the comment.

What is Factless Fact table?

Today, I faced an interview for MSBI Developer. The guy asked me many questions and most of them i answered perfectly. The Guy asked me a question on factless fact table. I like to share it with you, if you are not already familiar with it.

Question: What is FACTLESS Fact table?

Answer:

The “Factless Fact Table” is a table which is similar to Fact Table except for having any measure; I mean that this table just has the links to the dimensions. These tables enable you to track events; indeed they are for recording events.


Factless fact tables are used for tracking a process or collecting stats. They are called so because, the fact table does not have aggregatable numeric values or information. They are mere key values with reference to the dimensions from which the stats can be collected

Delete bunch of dynamic files

Question: How to delete all files from a directory?


Answer: Please follow the following steps:

Step1: Add a For each loop container. & Inside it add a File system task.

Step2: Add 2 variables (Scope: Package level)

1. Var_FilePath
2. Var_InputFolder
   
   

   

Step3; Double click on Foreach loop container and in expression map the "Directory" property to Var-InputFolder. (Click on the image for larger view)

Please select the "Reterive file name" property to fully qualified.

Step4; Map the Var_FilePath in variable mapping tab.

Step5: Double click on File system task and change the operation to "Delete file" and Set IsSourcePathVariable property to TRUE. Then map your Var_FilePath to it. (Click Image)

Step6: Now select the File System Task and press F4 for property window. Set the "Delay validation" to True.

Step7: Execute your package.

Hope this will help you.

:)

Allocating a 'Set ID' & Extracting data in groups

Question:
Allocate a Set ID for a Batch of data and make a group.
OR
Retain Values from Previous Rows.

Following is the input data and desired output for that

Answer:

Drag a DFT in the control flow and add the following components.

For achieving this you need to use Script Component in DFT. and the following below steps: Before that you need to select Header as input column.

Create an output column in script component

Add the following code in the Script component.
'*******************************************************************************
Imports System
Imports System.Data
Imports Microsoft.SqlServer.Dts.Runtime
Public Class ScriptMain
Inherits UserComponent
Dim int_Sno as integer ' // this variable should be global for the script (class level)
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Try
If Row.RecType = "A" Then
int_Sno = int_Sno + 1
End If
Row.Sno = int_Sno

Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub
End Class
******************************************************************

Now execute your package and you will get the desired output (Given below)



Hope this will help you.