First lets create a simple package with a Data Flow Task. This Task will transfer data from Flat file to a table in Database.
File Employee.txt is read and copied in table called Employee as below. Assume that the Employee.txt file contains three columns ID, Firstname and Lastname.
I am not going in details as these steps are simple.
So the package is ready to run. Now lets configure the package.
First, Configuration is done using SQL Server. Thus SQL Server contains a table which will store all the configuration values.
Then Environment Variable is used to store the path where the configuration is stored.
This step creates a SQL Server Configuration. Connection specifies the server where the table will be stored. And Configuration table is the table in which configuration will be stored. Configuration filter is kind of predicate which you will use in WHERE clause to separate this from other configurations stored in same table.
Here I have selected values of connection string for both the flat file and OLEDB connection in my configuration.
Microsoft SQL Server 2012 T-SQL Fundamentals By Ben-gan, Itzik (Google Affiliate Ad)
Next step is to create an environment variable configuration. So add a new configuration. This time we will select another option which specifies that Configuration Location is stored in Environment Variable. As seen in below figure
I used MY_SSIS_CONFIG_VARIABLE as Environment Variable. Remember that I have not yet created that environment variable.
So now lets create an environment variable which will store the path where configuration is stored which in our case is the SQL Server connection manager. So first lets copy that connection string.
Then create an environment variable as below.
Now just run the package and it will run successfully.
Now change the name of text file from employee.txt to employee2.txt and then try to run the package. The package will fail.
Now, go to SSMS and change the connection string of flat file which will point to employee2.txt
Now try runnig the package and it will run successfully. You can see the results in the table.
File Employee.txt is read and copied in table called Employee as below. Assume that the Employee.txt file contains three columns ID, Firstname and Lastname.
I am not going in details as these steps are simple.
So the package is ready to run. Now lets configure the package.
First, Configuration is done using SQL Server. Thus SQL Server contains a table which will store all the configuration values.
Then Environment Variable is used to store the path where the configuration is stored.
This step creates a SQL Server Configuration. Connection specifies the server where the table will be stored. And Configuration table is the table in which configuration will be stored. Configuration filter is kind of predicate which you will use in WHERE clause to separate this from other configurations stored in same table.
Here I have selected values of connection string for both the flat file and OLEDB connection in my configuration.
Microsoft SQL Server 2012 T-SQL Fundamentals By Ben-gan, Itzik (Google Affiliate Ad)
Next step is to create an environment variable configuration. So add a new configuration. This time we will select another option which specifies that Configuration Location is stored in Environment Variable. As seen in below figure
I used MY_SSIS_CONFIG_VARIABLE as Environment Variable. Remember that I have not yet created that environment variable.
So now lets create an environment variable which will store the path where configuration is stored which in our case is the SQL Server connection manager. So first lets copy that connection string.
Then create an environment variable as below.
Now just run the package and it will run successfully.
Now change the name of text file from employee.txt to employee2.txt and then try to run the package. The package will fail.
Now, go to SSMS and change the connection string of flat file which will point to employee2.txt
Now try runnig the package and it will run successfully. You can see the results in the table.
No comments:
Post a Comment