Script task recompilation Error

A package that has a script task runs fine in visual studio, but the package fails as a job with following error on DEV Server.

"Script could not be recompiled or run: Attempted to read or write protected memory. This is often an indication that other memory is corrupt"

OR

“Error 1 Validation error. Script Task : The task is configured to pre-compile the script, but binary code is not found. Please visit the IDE in Script Task Editor by clicking Design Script button to cause binary code to be generated.”

Answer:

• Check the pre-complied property of script task.
• Change it to TRUE
• Add the following code in your script, before imports statement.

                    Option Strict Off

                    Option Explicit On
                    Imports System

                    .

                    . 

Checking for the file?

Question:

Many times I observed on MSDN SSIS forum that developers are facing problem for checking the file in a particular folder. There are 2 types of checking:

1> Is any file exist in the folder?

OR

Count number of files in a directory through SSIS

2> A particular file is exist [for example c:\Manish\demo.txt]

Answer:

1> Is any file exist in the folder? OR Count number of files in a directory through SSIS?

For checking the file existence in a directory, there are 2 ways

1: File Property task

File Properties Task .  It will detect file existence. Check the link.

2: Script task

Step1: Take a ForEachLoop container.

Step2: Map to the particular directory.

Step3: Inside that you have to use a script task and there you can write following code to count the no of files.

Step4: Define the following variable @ package level 

User::Count

---------------------------
|   ForEachLoop             |
---------------------------
|
|            --------------
|            |Script Task |
|            --------------
|
---------------------------

Step5:

Public Sub Main()

    Dts.Variables("User::Count").Value = Dts.Variables("User::Count").Value + 1

    MsgBox(Dts.Variables("User::Count").Value.ToString())

    Dts.TaskResult = ScriptResults.Success

End Sub

    
Now you can access the count variable

2> A particular file is exist [for example c:\Manish\demo.txt]

You can use a script task to check if the file exists or not. Suppose I have demo.txt in C:\Manish then I will use a script task with code as:

Public Class ScriptMain

    Dim flag As Boolean

    Public Sub Main()

        flag = File.Exists("C:\MyFolder\demo.txt")

        If flag = True Then

            System.Windows.Forms.MessageBox.Show("File Exists")

        End If

    Dts.TaskResult = Dts.Results.Success

    End Sub

End Clas

Hope this will help you. Give your suggestion to improve this post.

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