How to configure single Source and destination in Data flow to be used by multiple databases and flat files?

Hi Friends,

A few days back I came across a scenario where there were multiple flat files and the data were to be loaded into different databases. The good thing about the whole process was that the files were named in format

[db_name]_data.txt

And the table structures were same in all the databases, also data structure in all the flat files was same.

Now suppose I have 3 databases viz.

1. MyDB
2. TestDb
3. SchemaTest

And my file names are

1. MyDB_data.txt
2. TestDb_data.txt
3. SchemaTest_data.txt

So, data from MyDB_data.txt, TestDb_data.txt, SchemaTest_data.txt would go in MyDB, TestDb, SchemaTest databases respectively.

Let’s start by creating tables

Execute the below scripts for all the three databases

1 2 3 4 5 6

CREATE TABLE [dbo].[multipledbinserts](     [Id] [smallint] NULL,     [Fname] [varchar](50) NULL,     [Lname] [varchar](50) NULL ) GO

Now create all the text files with data

Once we are done with creating tables in databases and data in text files. Let’s start with configuring the Controls in SSIS. First take for each loop container and select for each file enumerator which will iterate over all the files.

Now create a variable of string type with scope of package. Set the initial value of variable as one of the flat file name. This variable will store the file names.

Now drag and drop a dataflow task inside the for each loop container and edit it.

Now add a flat file connection manager and set the path as one of the flat file.

Now, Select the flat file Conn. Manager and set its properties by pressing F4. Select expressions and select property Connection string. In the expression builder write the path concatenated with file name variable we set initially.

I am concatenating the extension also because while configuring for each loop container I checked on the option retrieve file name only.

Now, let’s add an ole db connection and configure it for multiple databases. Now select the Conn. Mngr. and press F4. In the properties select expression and then select Initial Catalog and get the database name by writing the expression as shown in image below.

So, we are done with configuration part. Now just add a flat file source and an ole db destination and set them with connection managers we just configured above.

Check the tables before executing the package.

Verify the table data after executing the package.

Again check for the data in tables.

Happy Learning!!!

Sequentially Inserting rows into Excel Spreadsheets from sql server table

Hi Friends,

Here’s an interesting scenario on SQL Server Integration Services.

How to Sequentialy Insert specific number of rows into excel spreadsheet and dynamically generate a new spreadsheet for next set of sequential insert?

Suppose, I have 10000 records in my table and i want to export  a specific number of rows, let’s say 900 records in excel ‘s 1^st spreadsheet and then next 900 to next spreadsheet and so on. In this process, the spreadsheets wil be created dynamically on the basis of row count in the source table.

My Table has an Identity column and the data looks something like below.

Here, In the table Column “Id” is of type “bigint” and “Name” is of type “Char(1)”.

So, lets start by creating an OLE DB connection to sql server as Source.

Now, Create an Excel conection manager for destination.

Now, In control flow of SSIS drag and drop an Execute SQL task and type the below query in the task.

1 2	Select Cast(CEILING(COUNT(1)/900.00) as int) As TabCount from chkrows

The query determines the number of spreadsheets to be created.

Now create a vaiable named “TabCount” of type “Int32” and pass the output of query to the variable.

Create another variable “Counter” of type “Int32”.

Now, drag and drop a for loop container in the control flow, connect it after the Execute SQL Task  and configure as below.

Now, Create a variable named “TabCreate” of type “String”. In the expression for the variable type the create table statement as below.

The spreadsheets to be created dynamically will be named in the format “ExcelDest_”@User::Counter.

Now, Drag and drop an Execute SQL Task inside the for loop container and edit it as below.

Note that the control has “SQLSourceType” set to “Variable” and “SourceVariable” is set to “TabCreate”.

Now, Drag and drop a data flow task inside the for loop container and edit it.

Drag and drop an OLE DB source and edit it to extract the rows from the table you want to export to excel.

Connect the source output to data conversion transformation.

Now, create a variable named “TabName” of type “String” and edit its expression as below.

Edit the excel destination transformation as below.

Rename the Table as “ExcelDest_0” and remove the column “Copy of Name” as below.

Now edit the mappings as below.

Click OK.

Now, edit the excel destination transformation and change the Data Access Mode to “Table name or view name variable”  and give the variable name as “TabName” as below.

Hence, the spreadsheet will be created dynamically in the Execute SQL task and will be used as destination table passed as Variable “TabName” here.

Now, Depending upon the row count of the table, If I have TabCount >1 then in that case it would be required to check the maximum ID inserted in the previous spreadsheet.

So, we create another variable named “MaxIDQuery” of type “String” and edit its expression as below.

Hence, one more Execute SQL Task is required to check the Maximum ID for previous spreadsheet and for the first spreadsheet we can simply load data in excel’s first spreadsheet after the creation of first spreadsheet.

Now, We create another variable named “MaxId” of type “Double” which will store the maximum ID from previous Spreadsheet.

So, instead of using table/query for OLE DB source in Data flow a SQL Command from vaiable is passed to check if its for the first spreadsheet or for any number of spreadsheet created after first.

So, Lets create another variable named “FetchData” of type “String” and edit its expression to extract data to be inserted into first spreadsheet and for any number of shpreadsheets after the first.

Now, In the for loop container in Control flow, Connect the Data flow task to the “Create excel spreadsheet”  Execute SQL Task and edit the precedence constraint to contain the expression.

Now, connect the “Max Id from Previous Spreadsheet” Execute SQL Task to the “Create excel spreadsheet”  Execute SQL Task and edit the precedence constraint to contain the expression.

Now, connect the the Data flow task to the “Max Id from Previous Spreadsheet” Execute SQL Task and edit the precedence constraint and check the “Logical OR, one constraint must evaluate to True“ option.

So, my final package looks like below.

So, if the package is supposed to create ‘N’ number of spreadsheets then the data load for 1^stspreadsheet,

the control will flow from Step1 ->Step3 and

for subsequent data loads the control will flow from Step1-Step2-Step3.

Happy Learning!!!

Performing Audit in SSIS

Hi Friends,

In this blog post, let’s have an insight on how Auditing can be done in SQL Server Integration Services packages?

The basic auditing in package includes measures like

How many rows were inserted, updated or deleted, package name, status, etc. The information to be captured depends on personal choice or project needs.

So, Let’s create a simple SSIS package. In the post,  much of the focus is on audit instaed of transformations.

Let’s create a source and destination connection managers.

Now, drag and drop a Data flow task in Control flow and edit it.

In the Data flow tab, drag and drop an OLE DB source and extract the data from required table.

Perform all the necessary transformations. In the example i have taken “RowCount” transformation and simply mapped the source columns and destination columns. You can use “RowCount” transformation after using lookup tranformation to get the number of rows inserted / updated.

Create a Variable “intRowCount” of type Int32 of scope of Package. This variable is used to store number of rows extracted.

Now, take a destination transformation. In this case, since its my local machine and destination is SQL Server. Hence, I have taken SQL Server destination. Make sure if you use SQL Server destionation, then the destination should be your local system only and you create Target connection manager specifying (localhost or . (dot )).

Now, In Control flow drag and drop an Execute SQL task and perform some updates while connecting it to the Data Flow task.

Note: The Execute SQL task taken here is just for reference. You can take any task as per your requirement. The basic purpose here is to show Auditing.

Now comes the important part. Let’s create a table in SQL Server to hold the Audit information.

In this case i have created a separate schema “Audit” in my target database.

Now, Create the below variables of scope package.

1. “StringStatusSuccess” of type String and Value “Success”.
2. “StringStatusFailure” of type String and Value “Failure”.

3. “StringPackageVersion” of type string and set expression as below.

In the expression concatenate the System Variable “PackageName” and Syatem Variable “VersionBuild”.

Now, drag and drop an Execure SQL task in control flow, rename it as “Audit Info On Success” and edit it.

So, the information captured here in package would go infor my Audit table I created above.

Notice that, In the Parameter Mapping tab of my Execute SQL Task I have mapped some of the parameters to system variables and rest to the variables I created above.

In the Parameter mapping I have taken variable “StringStatusSuccess” as this indicates the status of package as Success.

Now, drag and drop another Execute SQL task in control flow, rename it as “Audit Info On Failure” and edit it.

The Execute SQL task is same as the above except for a variable “StringStatusFailure” as this will execute on Failure in package.

Now, Connect all the tasks to the Execute SQL task “Audit Info On Failure” and edit the precedence constraints connecting to this task. Change the Value to “Failure”.  Select the “Logical OR” option in Multiple Constarints.

Check the Audit table before executing the package.

Now, execute the package and check the Audit table again.

Now, Let’s deliberately create some error in “Update Destination” Execute SQL task and execute the package and check the Audit table again.

Happy Learning!!!