SSIS - Project Versioning (Project Deployment in SSIS)

Project Versioning in Denali:

Was exploring Denali CTP1, now we can deploy Project to SSISDB Catalog.
Each time we will deploy the project to SSISDB, the project will be saved as new version.
We can see which version we are using currently by checking the properties of project and we can restore any previous version that we want.

How to check version number or project deployed to SSISDB

How to check and restore to previous versions of SSIS Project in SSISDB

**If we don't have Team Foundation Server(TFS) or VSS then this feature is good for version controlling.
**Could not find how to label each of the version or insert some details about project or comments during deployment.


link :

http://social.technet.microsoft.com/wiki/contents/articles/projects-in-sql-server-denali-ctp1-ssis.aspx

SSIS - How to Write Expressions on Data Flow Task for Derived Column Transformation Expressions for derived column

Scenario:

We are developing an SSIS Package and writing business logic by using Derived Column Transformation in Data Flow Transformation.
This business logic is subjected to change. We don't want to open the package every time the business logic changes, Instead of that we want to use configuration to handle the situation. If the logic changes, we will update the value in configuration table or file and package should read the value and apply in Derived Column Transformation.


Solution:


Let’s say we are loading data from flat file to SQL Server table, and flat file has two columns ID and Name.  We need to derive initials from Name column and business logic say that always take the first character from Name column as Initials.


Step1:
You went ahead and used the Flat file source to read the data, then used the Derived Column Transformation and wrote expressions to get the first Characters as shown below.
How to write expression on Data Flow Task to Change business Logic in Derived Column Transformation in SSIS Package


Step2:
Now consider that business decieded to change the logic for Intials. They want you to take two characters from Name column as Initials instead of first single character. That can be easy, you will open the SSIS Package and then make change in Derived Column expressions for DER_Name (Initials Column), save the package, test in QA,UAT and finally deploy to production.

This is long process. You want to take different approach , where you don't have to make any changes to SSIS Package, rather handle the logic change by using variable in SSIS Package and change the value of the variable in Configuration table or file.

To implement this, let's create a variable called VarBusinessLogic as shown below
Create Variable in SSIS Package and use in Data Flow Task to Change Expressions in Derived Column Transformation


Step 3:
As our variable is ready now and it has the value that we would like to use in Derived Column Expressions, Go to Properties of Data Flow Task, click on expressions and use VarBusinessLogic variable as shown below.

How to change expressions in Derived Column Transformation by using Variable value in SSIS Package


By using friendly expression on Data flow task we were able to write expression(Business logic) in derived column.

Step 4:
The final step is to add the VarBusinessLogic variable in Package Configuration. Now if the logic changes, you don't have to make any change to SSIS Package, instead of that you will update the value for VarBusinessLogic variable in Configuration table or file and Package will read and implement in Derived Column Expressions.

This can be helpful when business logic often change and you don't want to make change to package every time for small change.


SSIS - How to Remove Leading,Trailing OR Both Zeros from String in SSIS Package

Scenario:

Sometimes we get the data from flat file and we want to remove Leading Zero, Trailing Zeros OR Leading and trailing both before insert into destination.


Solution:

I assumed that data we are getting is string type, coming from the source (Excel, Flat file etc.)
Use the Derived column Transformation to remove Leading/Trailing OR Both zero from the string. After removing Zeros you can
Cast to Any data type you want such as Numeric, Integer, float etc.


Here are expressions to remove Leading Zeros, Trailing Zeros or Both from string column.


Leading Zeros:
(DT_WSTR,50)(DT_I8)[YourInputColumn]


Trailing Zeros:
REVERSE((DT_WSTR,50)(DT_I8)REVERSE([YourInputColumn]))


Leading and Trailing Zeros:
REVERSE((DT_WSTR,50)(DT_I8)REVERSE((DT_WSTR,50)(DT_I8)[YourInputColumn]))

SSIS - MonthName and Day Name in SSIS

Scenario:

Some time we have requirement to send email with MonthName or dayName in the subject in SSIS Package or need to create folder with Month Name and then Archive files e.g. Archive_Dec_2011. How we can create folder or have subject in email with Month Name or Day Name by using SSIS Package?


Solution:

We don't have Month-Name or Day-Name in SSIS Package.We can create two variables in our package and write expressions on them to use them wherever we need Month Name and Day Name.


Step 1:
Let's create VarDayName and VarMonthName variables in our SSIS Package so we can write expressions on them and use in our Package where we need Month Name an Day Name.

How to get Month Name and Day Name in SSIS Package - SSIS Tutorial for Beginners




Step 2: 
Go to the properties of VarDayName variable and then configure as shown below.


Write expression to get Day Name in SSIS Package - SSIS Tutorial

Expressions for Month Name:
DATEPART("dw",GETDATE())==1? "Sunday":
DATEPART("dw",GETDATE())==2?"Monday":
DATEPART("dw",GETDATE())==3?"Tuesday":
DATEPART("dw",GETDATE())==4?"Wednesday":
DATEPART("dw",GETDATE())==5?"Thursday":
DATEPART("dw",GETDATE())==6?"Friday":
DATEPART("dw",GETDATE())==7?"Saturday":""


Step 3:

Go to the properties of DayMonthName variable and write expressions as shown below.



Expressions for Month Name:
(MONTH(getdate()) == 1 ? "Jan" :
MONTH(getdate()) == 2 ? "Feb" :
MONTH(getdate()) == 3 ? "Mar" :
MONTH(getdate()) == 4 ? "Apr" :
MONTH(getdate()) == 5 ? "May" :
MONTH(getdate()) == 6 ? "Jun" :
MONTH(getdate()) == 7 ? "Jul" :
MONTH(getdate()) == 8 ? "Aug" :
MONTH(getdate()) == 9 ? "Sep" :
MONTH(getdate()) == 10 ? "Oct" :
MONTH(getdate()) == 11 ? "Nov" :
MONTH(getdate()) == 12? "Dec":"")



Now you have Month Name and DayName available through these two variables ,
Those can be used in expression with different transformations as required.


Related Posts
How To Use Expression Task To Get Day Name and Month Name In SSIS Package
Video Demo : How to use Expression Task to get Month Name in SSIS Package

How to Create flat file dynamically from SQL Server Table or View in SSIS Package - SSIS Tutorial

Scenario: Download Script

We have a requirement to create an SSIS Package that should create an output file dynamically from a Table or View.
The columns of View or Table can change anytime but we don't want to open package and remap column/columns. In short our SSIS Package should not care about the number of columns returned by Table or view.

Solution:

As the definition of Table or View can change anytime, It will be good idea to use Scirpt Task and handle the situation. If we use builtin source and destination Transformation, they will fail in case name or number of columns will change in source table or view.


Let's processed with step by step approach

Step 1:
Create the following variables as shown below, the values for the variables can be configured by using SSIS configuration,
This will give us freedom to change the name of file, directory path, delimiter ("," or "" etc.) and file extension (txt or csv etc.) without making modification to SSIS Package.

VarDelimiter :  Provide the delimiter that you want for your flat file
VarDirectoryPath :  Provide the directory path where you would like to create flat file dynamically
VarFileExtension : Provide the extension of file you like to create
VarFileName : Provide the name of file that you would like to create dynamically from SQL Server Table
VarLogFolder: Provide the path for Log folder in case error occur in Script Task
VarObject :  Create Object type variable to save records from SQL Table or View.



Create variables in SSIS Package to create flat file dynamically from SQL Server Table or View


Step 2:
Create OLE DB Connection manager by going to Connection Manager pane. You have to provide SQL Server Instance Name and then choose the database. After creating, I have renamed to DB_Conn_TechBrothersIT as shown below.

Create OLE DB Connection manager in SSIS Package to generate flat file dynamically


Step 3:

After creating variable, Drag Execute SQL Task in control flow pane and configure as show below. Please notice that we are using Select * here. that means get all the columns data from table or view.



Get data from SQL Server Table or View and save to Object type variable in SSIS Package to create flat file dynamically


Map the full result set to Object type variable in Execute SQL Task in SSIS Package as shown below.


Save the query results in Object type variable by using Execute SQL Task in SSIS Package


Step 4:

Bring Script Task to Control Flow Pane and then open by double clicking. We will be using C# as scripting language in our demo, you can use visual basic if you like.
Go to ReadOnlyVariable and then add the user variables those we have created above in Step 1.



Add variables to Script Task in SSIS Package to create flat file dynamically in SSIS Package from SQL Server Table or View


Step 5:

Click on Edit Script button, paste following code and save the the changes and Finally OK button.


Under #region Namespaces, I have added below code

using System.IO;
using System.Data.OleDb;


Under public void Main() { 
I have added below code.
            string datetime = DateTime.Now.ToString("yyyyMMddHHmmss");
            try {

                //Declare Variables
           string FileDelimiter = Dts.Variables["User::VarDelimiter"].Value.ToString();
           string DirectoryPath = Dts.Variables["User::VarDirectoryPath"].Value.ToString();
           string FileName = Dts.Variables["User::VarFileName"].Value.ToString();
           string FileExtension=Dts.Variables["User::VarFileExtension"].Value.ToString();
                

                OleDbDataAdapter A = new OleDbDataAdapter();
                System.Data.DataTable dt = new System.Data.DataTable();
                //Get Data From Object Type variable
                A.Fill(dt, Dts.Variables["User::VarObject"].Value);
                
                //Construct File Full Path by using Varaible values
              string FileFullPath = DirectoryPath + FileName +"_"+datetime+ FileExtension;

          int i = 0;
          
           StreamWriter sw = null;
           sw = new StreamWriter(FileFullPath, false);
           
                // Write the Header Row to File
           int ColumnCount = dt.Columns.Count;
           for (int ic = 0; ic < ColumnCount; ic++)
           {
               sw.Write(dt.Columns[ic]);
               if (ic < ColumnCount - 1)
               {
                   sw.Write(FileDelimiter);
               }
           }
           sw.Write(sw.NewLine);

           // Write All Rows to the File
           foreach (DataRow dr in dt.Rows)
           {
               for (int ir = 0; ir < ColumnCount; ir++)
               {
                   if (!Convert.IsDBNull(dr[ir]))
                   {
                       sw.Write(dr[ir].ToString());
                   }
                   if (ir < ColumnCount - 1)
                   {
                       sw.Write(FileDelimiter);
                   }
               }
               sw.Write(sw.NewLine);
           }
           sw.Close();
                Dts.TaskResult = (int)ScriptResults.Success;
        }
             catch (Exception exception)
            {
                // Create Log File for Errors
                using (StreamWriter sw = 
File.CreateText(Dts.Variables["User::VarLogFolder"].Value.ToString()
                    + "\\" + "ErrorLog_" + datetime + ".log"))
                {
                    sw.WriteLine(exception.ToString());
                    Dts.TaskResult = (int)ScriptResults.Failure;
                }

            }



Step 6:
Save the script and then Run your SSIS Package, it should create flat file in destination folder with date-time. You can change the type of extension , delimiter and can test by adding or dropping/adding columns to source table or view if all works fine.

I executed with by provide extension csv, delimiter as , and it created below flat file from my table.
How to create flat file dynamically from SQL Server Table or View in SSIS Package


Check out our other posts/ Videos on related topics

SSIS - How to Pass Parameter values to Sub Query in Execute SQL Task in SSIS Package

Scenario:
We want to execute SQL Query in Execute SQL Task but before we do that we need to pass
parameter/parameters values to Inner Query or Sub Query.

Solution:
Lets say here is the query we want to run in Execute SQL Task


SELECT id1
FROM table1
WHERE id1 = (
        SELECT id2
        FROM table2
        WHERE col1 = @VarParamter1
        )
    AND id3 = (
        SELECT id4
        FROM table3
        WHERE col2 = @VarParameter2
        )


Step 1:
Create a new SSIS Package, After creating Package, create two variables as shown below.
How to create variables in SSIS Package



Step 2:

Drag Execute SQL Task and configure as show below. Select 1 is just dummy value. It will be replaced with value evaluated by expressions at run time.
Provide Connection Manager, Connection Type and SQL Statement "Select 1" in Execute SQL Task in SSIS Package



Step 3:
In this step, you need to go to Expressions in Execute SQL Task. Here we are going to build our SQL Query. We will use two variables those we have created to use in sub query.


Write Expression on Execute SQL Task for SQL statement and use variable values to pass to sub query in SSIS Package
You are all set. The query is going to use the variable's values at run time. 

SSIS - Load File's information to SQL Server Table

Scenario:

You are working as SQL Server Integration Services(SSIS) developer, You need to create an SSIS Package that should be able to load file's information form a folder to SQL Server table. The SSIS Package should load file name, file created date-time and file size in kilo bytes.

Solution:

First of all we need to create a table in which we would like to save file's information from a folder.

use YourDatabaseName
go
CREATE TABLE dbo.FileInformation
(
Filename VARCHAR(100),
FileCreatedDateTime DATETIME,
FileSize INT
)

Step 1:
Open BIDS or SSDT and create new SSIS Package, In your SSIS Package, create a variable VarDirectoryPath of string type. Provide value to this variable C:\MyTestFolder as you would like to create the file's information from this folder. You will be providing the path of folder in which you have the files.
Create variable in SSIS Package to load file's information to SQL Server Table


Step 2:
Create an ADO.NET Connection to your Database and rename to DBConn.
Create an ADO.NET connection in SSIS Package to use in Script Task to load File's information to SQL Server Table

Step3: 
Drag Script task to Control Flow Pane and add  VarDirectortyPath as input variable as shown below
Add user variable to Script Task in SSIS Package to load file's information to SQL Server Table



Step 4: 
Click on Edit Script Button and then copy below code to your Script Task Editor.

Under #Region NameSpaces, paste below namespaces.

using System.IO;
using System.Data.SqlClient;


Got to public void Main()
{
and paste below code.

SqlConnection myADONETConnection = new SqlConnection();
myADONETConnection = (SqlConnection)(Dts.Connections["DBConn"].AcquireConnection(Dts.Transaction) as SqlConnection);
//MessageBox.Show(myADONETConnection.ConnectionString, "ADO.NET Connection");

string DirPath = Dts.Variables["User::VarDirectoryPath"].Value.ToString();
//get all files from directory
string[] files = Directory.GetFiles(DirPath);
SqlCommand sqlCmd= new SqlCommand();
sqlCmd.Connection = myADONETConnection;

//loop through files
foreach (string filename in files)
{

FileInfo file = new FileInfo(filename);

sqlCmd.CommandText = "Insert into dbo.FileInformation Values('" +
 file.Name + "','" + file.CreationTime + "','" + file.Length/1024 + "')";
//MessageBox.Show(sqlCmd.CommandText);

sqlCmd.ExecuteNonQuery();
}

Dts.TaskResult = (int)ScriptResults.Success;
}


Step 5:

Save the script and close the script task editor window. Run your SSIS Package, it should load the file's information from folder to Test.dbo.FileInformation table.

How to Purge Old Files (Delete Old Files From Folders By using SSIS Script Task) - SSIS Tutorial

Scenario: 

You want to create an SSIS Package that will read the folder Path, File Type, Filename like and Retention Period from a Table and then loop through each Folder path and delete required files those retention period is more than defined.


Solution:

We will be using Script Task to delete the files from different folders depending upon the retention period which we will define in our table.

Step 1:

Create create the PurgeInformation table so we can insert different entries for folder from which we would like to delete the files.

CREATE TABLE [dbo].[PurgeInformation] (
    [ID] [INT] IDENTITY(1, 1) NOT NULL
    ,[FolderPath] [VARCHAR](300) NULL
    ,[FileType] [VARCHAR](20) NULL
    ,[RetentionPeriod] [INT] NULL
    ,[FileNameLike] [VARCHAR](100) NULL
    ) ON [PRIMARY]
GO

SET ANSI_PADDING OFF
GO

SET IDENTITY_INSERT [dbo].[PurgeInformation] ON
GO

INSERT [dbo].[PurgeInformation] (
    [ID]
    ,[FolderPath]
    ,[FileType]
    ,[RetentionPeriod]
    ,[FileNameLike]
    )
VALUES (
    1
    ,N'C:\SSISTraining\HDW'
    ,N'.txt'
    ,5
    ,N'Log'
    )
GO

INSERT [dbo].[PurgeInformation] (
    [ID]
    ,[FolderPath]
    ,[FileType]
    ,[RetentionPeriod]
    ,[FileNameLike]
    )
VALUES (
    2
    ,N'C:\SSISTraining\SourceFiles'
    ,N'.xls'
    ,4
    ,N'Pkg_Log'
    )
GO

SET IDENTITY_INSERT [dbo].[PurgeInformation] OFF
GO


Step 2: 
Create an SSIS Package by using BIDS or SSDT and Create a variable VarObject of type object.

Step 3:
Use Execute SQL Task to read the values from dbo.PurgeInformation table and load into VarObject variable as shown below.

Query :

SELECT Folderpath
    ,FileType
    ,RetentionPeriod
    ,FileNameLike
FROM dbo.PurgeInformation

Read records from SQL Server Table and Save to Object Type variable in SSIS to Purge old Files



Save query results to Object type variable in SSIS Package for Delete old files from folders 


Step 4:
Bring For-each loop container on Control Flow Pane. We need to use Object type variable in For-each Loop so can read one record on each iteration.


How to use Object type variable in Foreach Loop Container to read all the records one by one in SSIS Package


Map the row columns to variables in For-each Loop Container in SSIS Package

Step 5:
After configuring For-each Loop, Drag Script task inside For-each Loop Container and add all user variables we have created above.

How to add variables to Script Task in SSIS Package to delete old files from folders

Step 6:
Click Edit Button and it will open Script Task Editor.
Under #region Namespaces, I have added below code
using System.IO;

Under public void Main() { 
I have added below code.
string FileDirectory, FileNamePattern, FileType;
int RetentionPeriod;

FileDirectory=Dts.Variables["User::VarFolderPath"].Value.ToString();
FileType=Dts.Variables["User::VarFileType"].Value.ToString();
FileNamePattern = Dts.Variables["User::VarFileNameLike"].Value.ToString();
RetentionPeriod =Convert.ToInt32(Dts.Variables["User::VarRetentionPeriod"].Value.ToString());
var files = new DirectoryInfo(FileDirectory).GetFiles(FileNamePattern + "*" + FileType);
// MessageBox.Show("OUT of Loop");
foreach (var file in files)
{

// MessageBox.Show(" I AM IN LOOP");

if (file.CreationTime < DateTime.Today.AddDays(-RetentionPeriod))
    {
        MessageBox.Show(file.Name.ToString());
        file.Delete();
    }
}


SSIS Package to delete old files from different folders

Your SSIS Package is ready, You can schedule your package by SQL Server Agent nightly or can run on demand to delete files from different folder with different retention period.

You can add new folder path in table to delete required files.

SSIS - [Create table for each flat file and load data]

Senario:
This Code can be used to load multiple files in multiple tables, each file will be loaded into separate table. The table will be created by reading the first line(Hearder row) of flat file and Name of table will be same like flat file.


Solution:
The package reads the file from folder and then create the table on fly as FileName and then inserts data into table. If Table Already exists with the same name it will drop the table and re-Create table

You need to create ADO.Net Connection to the databse where you want to load the files into tables. Drag the script task to the control flow and paste below code.


Here is C# Code

/*
Microsoft SQL Server Integration Services Script Task
Write scripts using Microsoft Visual C# 2008.
The ScriptMain is the entry point class of the script.
*/

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.IO;
using System.Data.SqlClient;


namespace ST_da3127cebb85407989456cc583a3f9e4.csproj
{
[System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{

#region VSTA generated code
enum ScriptResults
{
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
};
#endregion

/*
The execution engine calls this method when the task executes.
To access the object model, use the Dts property. Connections, variables, events,
and logging features are available as members of the Dts property as shown in the following examples.

To reference a variable, call Dts.Variables["MyCaseSensitiveVariableName"].Value;
To post a log entry, call Dts.Log("This is my log text", 999, null);
To fire an event, do something like the following:
bool bFireAgain = true;
Dts.Events.FireInformation(99, "test", "hit the help message", "", 0, ref bFireAgain);

To use the connections collection use something like the following:
ConnectionManager cm = Dts.Connections.Add("OLEDB");
cm.ConnectionString = "Data Source=localhost;Initial Catalog=AdventureWorks;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=False;";

Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.

To open Help, press F1.
*/

public void Main()
{


SqlConnection myADONETConnection = new SqlConnection();
myADONETConnection = (SqlConnection)(Dts.Connections["ADOAUDIT"].AcquireConnection(Dts.Transaction) as SqlConnection);
MessageBox.Show(myADONETConnection.ConnectionString, "ADOAUDIT");


string line1 = "";
//Reading file names one by one
string SourceDirectory = @"C:\SSISTraining\SourceFiles\DynamicFiles\";
// TODO: Add your code here
string[] fileEntries = Directory.GetFiles(SourceDirectory);
foreach (string fileName in fileEntries)
{
// do something with fileName
MessageBox.Show(fileName);
string columname = "";


//Reading first line of each file and assign to variable
System.IO.StreamReader file2 =
new System.IO.StreamReader(fileName);


string filenameonly=(((fileName.Replace(SourceDirectory,"")).Replace(".txt","")).Replace("\\",""));
line1 = (" IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo]."+filenameonly+"') AND type in (N'U'))DROP TABLE [dbo]."+filenameonly+" Create Table dbo."+filenameonly +"("+file2.ReadLine().Replace(","," VARCHAR(100),")+ " VARCHAR(100))").Replace(".txt","");

file2.Close();

MessageBox.Show(line1.ToString());
SqlCommand myCommand = new SqlCommand(line1, myADONETConnection);
myCommand.ExecuteNonQuery();

MessageBox.Show("TABLE IS CREATED");

//Writing Data of File Into Table
int counter = 0;
string line;

System.IO.StreamReader SourceFile =
new System.IO.StreamReader(fileName);
while ((line = SourceFile.ReadLine()) != null)
{

if (counter == 0)
{
columname = line.ToString();
MessageBox.Show("INside IF");
}

else
{
MessageBox.Show("Inside ELSE");
string query = "Insert into dbo." + filenameonly + "(" + columname + ") VALUES('" + line.Replace(",", "','") + "')";
MessageBox.Show(query.ToString());
SqlCommand myCommand1 = new SqlCommand(query, myADONETConnection);
myCommand1.ExecuteNonQuery();
}


counter++;


}

SourceFile.Close();
}
Dts.TaskResult = (int)ScriptResults.Success;
}
}
}



If you like to use VB.Net Code

'
' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual C# 2008.
' The ScriptMain is the entry point class of the script.
'


Imports System.Data
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.Windows.Forms
Imports System.IO
Imports System.Data.SqlClient


Namespace ST_da3127cebb85407989456cc583a3f9e4.csproj
_
Partial Public Class ScriptMain
Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

#Region "VSTA generated code"
Private Enum ScriptResults
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
End Enum
#End Region

'
' The execution engine calls this method when the task executes.
' To access the object model, use the Dts property. Connections, variables, events,
' and logging features are available as members of the Dts property as shown in the following examples.
'
' To reference a variable, call Dts.Variables["MyCaseSensitiveVariableName"].Value;
' To post a log entry, call Dts.Log("This is my log text", 999, null);
' To fire an event, do something like the following:
' bool bFireAgain = true;
' Dts.Events.FireInformation(99, "test", "hit the help message", "", 0, ref bFireAgain);
'
' To use the connections collection use something like the following:
' ConnectionManager cm = Dts.Connections.Add("OLEDB");
' cm.ConnectionString = "Data Source=localhost;Initial Catalog=AdventureWorks;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=False;";
'
' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
'
' To open Help, press F1.
'


Public Sub Main()


Dim myADONETConnection As New SqlConnection()
myADONETConnection = DirectCast(TryCast(Dts.Connections("ADOAUDIT").AcquireConnection(Dts.Transaction), SqlConnection), SqlConnection)
MessageBox.Show(myADONETConnection.ConnectionString, "AD.AUDIT")


Dim line1 As String = ""
'Reading file names one by one
Dim SourceDirectory As String = "C:\SSISTraining\SourceFiles\DynamicFiles\"
' TODO: Add your code here
Dim fileEntries As String() = Directory.GetFiles(SourceDirectory)
For Each fileName As String In fileEntries
' do something with fileName
MessageBox.Show(fileName)
Dim columname As String = ""


'Reading first line of each file and assign to variable
Dim file2 As New System.IO.StreamReader(fileName)

Dim filenameonly As String = (((fileName.Replace(SourceDirectory, "")).Replace(".txt", "")).Replace("\", ""))
line1 = (" IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo]." & filenameonly & "') AND type in (N'U'))DROP TABLE [dbo]." & filenameonly & " Create Table dbo." & filenameonly & "(" & file2.ReadLine().Replace(",", " VARCHAR(100),") & " VARCHAR(100))").Replace(".txt", "")

file2.Close()

MessageBox.Show(line1.ToString())
Dim myCommand As New SqlCommand(line1, myADONETConnection)
myCommand.ExecuteNonQuery()

MessageBox.Show("TABLE IS CREATED")

'Writing Data of File Into Table
Dim counter As Integer = 0
Dim line As String = ""

Dim SourceFile As New System.IO.StreamReader(fileName)
While (InlineAssignHelper(line, SourceFile.ReadLine())) IsNot Nothing

If counter = 0 Then
columname = line.ToString()
MessageBox.Show("INside IF")
Else

MessageBox.Show("Inside ELSE")
Dim query As String = "Insert into dbo." & filenameonly & "(" & columname & ") VALUES('" & line.Replace(",", "','") & "')"
MessageBox.Show(query.ToString())
Dim myCommand1 As New SqlCommand(query, myADONETConnection)
myCommand1.ExecuteNonQuery()
End If




counter += 1
End While

SourceFile.Close()
Next
Dts.TaskResult = CInt(ScriptResults.Success)
End Sub
Private Shared Function InlineAssignHelper(Of T)(ByRef target As T, ByVal value As T) As T
target = value
Return value
End Function
End Class
End Namespace



In above code I renamed ADO.NET Connection to ADOAUDIT that you can change to anything you like.

MessageBoxes are left for debugging, Please remove them after debugging.

Thanks