To make a Script Component to dynamically assign a data type based on the value from a text file

Question

I have a task of loading data from a .txt file to SQL Server table using Script Component in SSIS. I have loaded that successfully using a script.

 public void Main()
    {

            //Declare Variables
            string SourceFolderPath = Dts.Variables["$Project::Landing_Zone"].Value.ToString();
            string FileExtension = Dts.Variables["User::FileExtension"].Value.ToString();
            string FileDelimiter = Dts.Variables["User::FileDelimiter"].Value.ToString();
            string ArchiveFolder = Dts.Variables["User::ArchiveFolder"].Value.ToString();
            string ColumnsDataType = Dts.Variables["User::ColumnsDataType"].Value.ToString();
            string SchemaName = Dts.Variables["$Project::SchemaName"].Value.ToString();

            //Reading file names one by one

            string[] fileEntries = Directory.GetFiles(SourceFolderPath, "*" + FileExtension);
            foreach (string fileName in fileEntries)
            {

                SqlConnection myADONETConnection = new SqlConnection();
                myADONETConnection = (SqlConnection)(Dts.Connections["DBConn"].AcquireConnection(Dts.Transaction) as SqlConnection);

                //Writing Data of File Into Table
                string TableName = "";
                int counter = 0;
                string line;
                string ColumnList = "";
                //MessageBox.Show(fileName);

                System.IO.StreamReader SourceFile =
                new System.IO.StreamReader(fileName);
                while ((line = SourceFile.ReadLine()) != null)
                {
                    if (counter == 0)
                    {
                        ColumnList = "[" + line.Replace(FileDelimiter, "],[") + "]";
                        TableName = (((fileName.Replace(SourceFolderPath, "")).Replace(FileExtension, "")).Replace("\\", ""));
                        string CreateTableStatement = "IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[" + SchemaName + "].";
                        CreateTableStatement += "[" + TableName + "]')";
                        CreateTableStatement += " AND type in (N'U'))DROP TABLE [" + SchemaName + "].";
                        CreateTableStatement += "[" + TableName + "]  Create Table " + SchemaName + ".[" + TableName + "]";
                        CreateTableStatement += "([" + line.Replace(FileDelimiter, "] " + ColumnsDataType + ",[") + "] " + ColumnsDataType + ")";
                        SqlCommand CreateTableCmd = new SqlCommand(CreateTableStatement, myADONETConnection);
                        CreateTableCmd.ExecuteNonQuery();

                        //MessageBox.Show(CreateTableStatement);

                    }
                    else
                    {
                        string query = "Insert into " + SchemaName + ".[" + TableName + "] (" + ColumnList + ") ";
                        query += "VALUES('" + line.Replace(FileDelimiter, "','") + "')";

                        // MessageBox.Show(query.ToString());
                        SqlCommand myCommand1 = new SqlCommand(query, myADONETConnection);
                        myCommand1.ExecuteNonQuery();
                    }

                    counter++;
                }


                SourceFile.Close();

            }
        }

Package Variable Details

But the problem is that I could load all the values from the text file to the table only with a single data type(I used nvarchar(200) as a common data type for all values). I have passed nvarchar(200) as a value in one of the package variable and I have called that in the script.

I am expecting the script component to assign a DataType to the columns by understanding the values available in the text file.

Example: The text file which I loaded into the table has got the following data.

Id,Name,Age
1,Arun,25
2,Ramesh,26
3,Anish,28

As a result a table with the name dbo.File_1 is created with columns Id, Name and Age. But all the these columns are created with the same datatype nvarchar(200) as mentioned previously.

But my requirement is that the script component should assign data types based on the values, say for example, The Column Id should be assigned with datatype int, Name should be assigned with datatype nvarchar(50) and so on. Likewise the script should assign datatypes based on any kind of values(Like Decimal, Date etc)

Is there any possible way to achieve this? Thanks in advance.

Additional Hint:

This script normally Drop and Create a new table everytime during the execution. The table name is based on the FileName.

Is there a reason you are using a script component to import data from a text file? Why not use the flat file source task? You could try using a derived column task to convert your variables to their appropriate data type. However, if you can use a flat file source task then you can set the data type in the flat file connection or use the data conversion task. — tonyd, Jan 03 '18 at 15:31
Why not using a `Foreach loop container` to loop over files? in the way you are working you are just writing a C# code without any benefit from the SSIS components and tools — Hadi, Jan 03 '18 at 15:55
tonyd and Hadi, thanks for the comments. I like to explain you the intention of this script a bit further. It's all about making the script to assign the appropriate data type to the value by itself. For example while reading the text file, the data would be like 1,Harvey,America. But there is no data type associated with those values. So I want the script to analyse the values(1,Harvey,America) and to assign the appropriate data type. Say for example after analyzing the value 1 or any number, the script should set tinyint/smallint/int as the datatype. Similarily for all kinds of values. — Krishna, Jan 04 '18 at 04:12

score 1 · Accepted Answer · answered Jan 03 '18 at 17:09

1

This is shooting from the hip on this one, but maybe using tryparse:

col += int.TryParse(ID, out num)? "int" : "nvarchar(200)";

answered Jan 03 '18 at 17:09

KeithL

5,348
3
19
25

To make a Script Component to dynamically assign a data type based on the value from a text file

1 Answers1