How to Zip the files in SSIS 2012

How to Zip the files in SSIS 2012

• We are going to use the desktop as location to doing these operation.
• First step is to create one folder in the desktop as “SampleFolder”
• Create 2 sample text files in the folder (Give whatever name as your wish)
• Now in the SSIS package drag and drop the Execute Process Task
• In the Execute process Task properties give three properties as like below
• Executable : C:\Program Files (x86)\WinRAR\WinRAR.exe (configure the WINRAR installed path)
• Arguments: A SamplesZip3 *.txt

                A indicates the Archives folder

                Give space

                Give the Zip Folder Name as you like

                Give Space

                Put *

                .txt

       Note: - In Argument property is the main place to give the file format that Argument should                            write as it is given above.

• Working Directory :- C:\Users\LOKESH\Desktop\ SampleFolder

Please follow the below image

Regards,

LOKESH ANNAM

9492179390

Read More »

Save CSV file to SQL server and Retrieve back to folder

public void Main()

              {

                     // TODO: Add your code here

            //databaseFilePut("C:\\Users\\LOKESH\\Desktop\\Code Sample\\sample.csv");

            databaseFileRead("1", "C:\\Users\\LOKESH\\Desktop\\Code Sample\\sample1.txt");

                     Dts.TaskResult = (int)ScriptResults.Success;

              }

        public static void databaseFilePut(string varFilePath)

        {

            byte[] file;

            using (var stream = new FileStream(varFilePath, FileMode.Open, FileAccess.Read))

            {

                using (var reader = new BinaryReader(stream))

                {

                    file = reader.ReadBytes((int)stream.Length);

                }

            }

            SqlConnection con = new SqlConnection("Data Source=localhost;Initial Catalog=Database name;Persist Security Info=True;User ID=XXXXX;Password=XXXXXXXX");

            SqlCommand cmd = new SqlCommand();

            con.Open();

            //cmd.Connection = con;

            //cmd.CommandType = CommandType.Text;

            //cmd.CommandText = "Select * from V_Table";

            //SqlDataAdapter da = new SqlDataAdapter();

            //da.SelectCommand = cmd;

            //DataTable dt = new DataTable();

            //da.Fill(dt);

            using (var sqlWrite = new SqlCommand("INSERT INTO V_Table Values(@File)", con))

            {

                sqlWrite.Parameters.Add("@File", SqlDbType.VarBinary, file.Length).Value = file;

                sqlWrite.ExecuteNonQuery();

            }

        }

        public static void databaseFileRead(string varID, string varPathToNewLocation)

        {

            SqlConnection con = new SqlConnection("Data Source=localhost;Initial Catalog=Database name;Persist Security Info=True;User ID=XXXXX;Password=XXXXXXXX");

            SqlCommand cmd = new SqlCommand();

            con.Open();

            using (var sqlQuery = new SqlCommand(@"SELECT [Data] FROM [dbo].[V_Table] WHERE [Sno] = @varID", con))

            {

                sqlQuery.Parameters.AddWithValue("@varID", varID);

                using (var sqlQueryResult = sqlQuery.ExecuteReader())

                    if (sqlQueryResult != null)

                    {

                        sqlQueryResult.Read();

                        var blob = new Byte[(sqlQueryResult.GetBytes(0, 0, null, 0, int.MaxValue))];

                        sqlQueryResult.GetBytes(0, 0, blob, 0, blob.Length);

                        using (var fs = new FileStream(varPathToNewLocation, FileMode.Create, FileAccess.Write))

                            fs.Write(blob, 0, blob.Length);

                    }

            }

        }

Read More »