Search This Blog

Loading...

Saturday, April 30, 2016

How to Display a variable value in SSIS


Some time in order to debug variables in SSIS you want to see what’s the values that the variable is holding or you want to check the Result set which is stored in the variable.

If you want to show or check the variable value or want to show the value inside a Message box than it can be done through Script task.

Below I will create a test table and will insert a row into the table and will display the ColB value i.e. Hello How are You in SSIS using SQL Execute Task and Script task.

Table script

create table tbl (ColA varchar(50), ColB varchar(50))

insert into tbl  values('Test Message','Hello How are You')

Now we will open SSIS and will drop a SQL Execute task and a Script task in the Package

Follow the below steps

       Double click on SQL execute task

Create a data connection to the database where above table created

In SQL Statement add -select * from tbl

 Select Resultset as Single Row as below Screenshot



  Go to Result tab in the Right side

Add a variable with message and ResultName make it as 1 (Index of your column) Since we will show ColB value in the Message box as below screenshot


Next  connect the SQL Execute task to script task

Now, double click on Script task

Select Read only variable as User Message which we created above as below screenshot


Click on edit script and add Message box (MessageBox.Show(Dts.Variables["Message"].Value.ToString());) inside Main Function as below Screenshot



  Save and Click ok

 Run your Package this will display your variable value in the Message box as below.

Please write in Comments If you'r stuck with any step or need any help.



Sunday, March 27, 2016

For Container in SSIS with example


In this post I will show you how to configure For Container in SSIS with an example. This post will gives a clear picture on For container in SSIS & How it is differ from For Each(Previous Post) 

To know about Foreach Container in SSIS, Check my previous post on For Each Container -http://www.sqlandssrssolutions.com/2016/02/foreach-container-in-ssis-with-example.html/

So below I will create a simple package to demonstrate the use for loop container, which will insert a record in ForLoop table for every minute increment which we will configure in For Container as below.

Below is the step by step guide to configure For Container in SSIS.


First we will create a table in SQL, where we will insert a record for each minute till the For Loop ends.
--ForLoop Table Script
Create Table dbo.ForLoop
(
ID int identitY(1,1) not null ,

DT_Date datetime not null
)


    Once we will create the For Loop table next we will open Integration Service Project in SSIS and will drop a For container into it.

 Next give a Name to For Loop container for eg Looping Each  Minute from StartDate to EndDate

 Now we will start configuring the For container, so for that first we will create two variable in SSIS i.e. Start Date & EndDate as in screenshot


Next double Click on For Loop, Configure the for Loop as below Screenshot.



  Once done, You will get a warning message in the for Loop container as below.

Warning               1              Validation warning. Looping Each  Minute from StartDate to EndDate : The initialization expression is not an assignment expression: "@StartDate". This error usually occurs when the expression in the iterate expressions on the ForLoop is not an assignment expression.      forloop.dtsx       0              0

You can either Ignore it or set Delay validation to True to remove the warning message.

Once done with For Loop, next Drop an Execute SQL Task inside it.

Name Execute SQL Task like this- For Every Increment of minute Inserting Record in table

Double click on execute SQL Task and configure it as below Screenshot



 First create a connection to your Data source

Next Select  SQL Source Type- Direct Input

Next write the below query in SQL Statement

INSERT INTO ForLoop
 (DT_Date)
 VALUES(?)

Next click on Parameter mapping in Right hand side and add input variable StartDate as below



 Make sure the data type should be Date otherwise it will throw the below error

[Execute SQL Task] Error: Executing the query "INSERT INTO ForLoop
                         (DT_D..." failed with the following error: "Parameter name is unrecognized.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
OR
Error: 0xC002F210 at Execute SQL Task 1, Execute SQL Task: Executing the query “insert into ForLoop
                         values (?)” failed with the following error: “Invalid time format”. Possible failure reasons: Problems with the query, “ResultSet” property not set correctly, parameters not set correctly, or connection not established correctly.

Once done click ok and Run the Package, The package will run successfully as below.



Do a select * from dbo.ForLoop to verify the data, you will notice that for every minute there is a entry in ForLoop Table.

Explanation -While configuring For Loop below are three imp things.

Initialize expression-This is the Initialization expression from where our Loop will start i.e. StartDate= “27-03-2016 01:30”

Evaluate expression-This will Evaluate the expression till the loop ends i.e @StartDate<=@EndDate, This will run until the startDate become equal to endDate

Assign Expression-Every times when the For Loop runs it will add a minute in startdate until start date will be equal to End Date

Next in Execute SQL Task Every time when StartDate will incremented by 1 minute it will insert the Record in ForLoop table until the StartDate equals to EndDate i.e. 27-03-2016 01:30<= 27-03-2016 01:40
Therefore a total of 11 entries inserted into ForLoop Table when you will do a select * from dbo.ForLoop


Please write in comment box if in case you will face any Issue while performing the above steps, I will be happy to help you.



Saturday, February 27, 2016

Foreach Container in SSIS with example

I often see  Developers having issues or confusion with foreach container, Sometimes they faced issue with looping the files or asking questions on how to process each file and load each file records in the destination table

So below I will create a simple package to demonstrate the  use for each loop container which will process 3 text files and using data flow we will process each file and will insert Record into destination table. For Reference I have added the all 3 data files in the last.

Below are the steps 

First I have created three small text files and have kept in  SSIS. folder with Name (File1, File2 and File3)

Next I am creating a destination table in SQL where the three files data will get loaded

Create table EmployeeDetail
(ID varchar(100),
 Name varchar(100),
 Salary varchar(100)
)

Now Open BIDS

 –Add a SSIS Project

-Add a for each container in you Package

Add a variable SourcePath which will have the folder location where all files has been placed.

Add a variable FileName that will be a file Name as in screenshot.

Next double Click on For Each Loop

Click on collection

Go to expression

Select Directory and add variable source path as below screenshot


Next click on variable mapping

Map the FileName  variable as below Screenshot.


Once done click ok

Next Drop a Data flow task Inside Foreach container.

Double click on Data flow task

Add a Flat file Source and create a flat file connection 

Next select flat file connection properties by pressing F4 

Now Go to expression as highlighted in yellow and Select connection string and add file Name variable as in Screenshot

Click OK

Add an ole-db destination

Connect the Flat file source to SQL  destination and click on mapping in SQL Destination

Run the Package and It will run successfully without any Issue.

,
Once completed you will see all 3 files data will get loaded into your table.Do a check by doing 

Select  * from EmployeeDetail

Below I am sharing the three file test data so that you can use these file to test for each container in SSIS.


File1.txt

ID,Name,Salary
6,Vikas,1000
7,Amit,1100

File2.txt

ID,Name,Salary
6,Vikas,1000
7,Amit,1100

File3.Txt

ID,Name,Salary
4,A,4000
5,B,7000

Please write in comment box if in case you will face any Issue while performing the above steps, I will be happy to help you.

Saturday, January 2, 2016

Finding user Id's and details of objects like, tables, procedures or functions in SQL server


As an active user of various forums and groups someone asked below questions on my SQL/MSBI Developer & Jobs Group.  

Issues asked by Users

Requirement 1

Can someone please help me to trace the user Id who created the objects like, tables, procedures and functions etc.... in SQL server?

Requirement 2

Can anyone help me for the below requirement
1) Need to get Deleted Stored Procedure’s name
2) Need to get Deleted Table name and function With User name and Timing

Solution is simple and same for all two above Requirement.

Fetching all this type of Info like who has created the objects (Table, Function, SP and view) in which database and on which date in SQL is simple. Just follow the below steps.

1. Right click on Your Server (SQL Server)

2. Click on Reports.

3. Click on standard Reports.

4. Click on Schema Change History as below.    

                                        
5. Finally you will get the complete Report who has created what object with what name as below.
                                                                         
In the above Report you will see I have created one SP, one Function one table with different users and so on. with different user (Sa and Rakesh).




Sunday, December 27, 2015

Use temp table inside Stored Procedure with Oledb Source in SSIS


It’s a common practise to use Temp tables inside SP’s to optimize the performance but when you will call the same SP in SSIS which is using temp table inside it you will get the below error.

TITLE: Package Validation Error
------------------------------

Package Validation Error

------------------------------
ADDITIONAL INFORMATION:

Error at Data Flow Task [OLE DB Source [1]]: No column information was returned by the SQL command.

Error at Data Flow Task [SSIS.Pipeline]: "output "OLE DB Source Output" (11)" contains no output columns. An asynchronous output must contain output columns.

Error at Data Flow Task [SSIS.Pipeline]: One or more component failed validation.

Error at Data Flow Task: There were errors during task validation.

 (Microsoft.DataTransformationServices.VsIntegration)

------------------------------
 we will use the below Table script and SP to produce this Error then in the below I will show the steps to solve it.

Below is the test script of table
.
CREATE TABLE [dbo].[LKP_Countries](
       [Country] [varchar](100) NULL,
       [Code] [int] NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[LKP_Countries] ([Country], [Code]) VALUES (N'INDIA', 91)
GO
INSERT [dbo].[LKP_Countries] ([Country], [Code]) VALUES (N'SINGAPORE', 65)
GO
INSERT [dbo].[LKP_Countries] ([Country], [Code]) VALUES (N'USA', 1)
GO
INSERT [dbo].[LKP_Countries] ([Country], [Code]) VALUES (N'PAKISTAN', 92)
GO


Now we will create a Stored Procedure that will have a temp table inside it using the above table



create PROC  USP_COUNTRIES
@CODE VARCHAR(200)

AS
BEGIN

DECLARE @COUNTRYCODE VARCHAR(200)

SET @COUNTRYCODE=@CODE
      
SELECT * INTO #COUNTRIES FROM  [DBO].[LKP_COUNTRIES]
       IF @CODE=91
              SELECT * FROM #COUNTRIES WHERE CODE=@COUNTRYCODE
       ELSE
              SELECT * FROM #COUNTRIES

END

Next step we will open SSIS and drop an oledb connection into it

Will call the above SP in oledb destination and map the code parameter accordingly


Since SSIS does mapping in design time so if you will click on mapping you will get the below error with no column information available as below.

The component reported the following warnings:

Error at Data Flow Task [OLE DB Source [1]]: No column information was returned by the SQL command.

Now click ok and try to run the package you will get the below error.


Solution:

Since SSIS does a mapping in design time so will generate a fake condition 1=0 or 1=2 in the SP which contains all the SP’s columns and help SSIS to generate the column list in the design time as below as highlighted.

ALTER PROC  USP_COUNTRIES
@CODE VARCHAR(200)

AS
BEGIN

DECLARE @COUNTRYCODE VARCHAR(200)

SET @COUNTRYCODE=@CODE
IF 1=0
BEGIN
SELECT  CONVERT(VARCHAR(200), NULL) AS CODE,
        CONVERT(VARCHAR(200), NULL) AS COUNTRY
END   
SELECT * INTO #COUNTRIES FROM  [DBO].[LKP_COUNTRIES]
       IF @CODE=91
              SELECT * FROM #COUNTRIES WHERE CODE=@COUNTRYCODE
       ELSE
              SELECT * FROM #COUNTRIES

END

Since we have only two columns in our select so we have added both the column name

Note : You have to add all the column names in the 1=0 condition which you want in your SSIS Package.

Once you have altered your SP Go to SSIS click on mapping now this time it will map all the columns


Add a Flat file destination Map the column names and Run your SSIS Package.

It will export all result to your destination.

Note:You can also use SET FMTONLY ON; above your sp to get the temp table column information but it has disadvantage that to get the meta data information it used to execute the SP five times.

Saturday, November 28, 2015

SSIS Error-Column "Name" cannot convert between unicode and non-unicode string data types.


Error-One of the most common error in SSIS is while exporting data from SQL to Excel destination 
as below.

TITLE: Package Validation Error
------------------------------

Package Validation Error
------------------------------
ADDITIONAL INFORMATION:

Error at Data Flow Task [Excel Destination [41]]: Column "Name" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task [SSIS.Pipeline]: "Excel Destination" failed validation and returned validation status "VS_ISBROKEN".

Error at Data Flow Task [SSIS.Pipeline]: One or more component failed validation.

Error at Data Flow Task: There were errors during task validation.

 (Microsoft.DataTransformationServices.VsIntegration)

Solution-To solve this issue we need to use a Data Conversion transformation in between oledb source and Excel destination and change the data type Unicode string [DT_WSTR].

Below is the complete Step by Step Solution

First we will create a table in the SQL and will insert some records into it to produce the above error

create table test_Unicode
(Id int identity(1,1),
 Name varchar(50)
 )

 Insert into test_Unicode values ('Rakesh'),('Mukesh'),('Nitin')

 Select * from test_Unicode


Now we will create a SSIS package that will have a Oledb Source which is connected to our test_Unicode table now we will take a excel destination and try to load data into excel destination.

You will see a cross mark in excel destination with a error showing as below.

Column "Name" cannot convert between unicode and non-unicode string data types.



Below are the steps that need to be followed to remove the above highlighted error.

1. Add a data conversion transformation between Source and destination.

2. Connect source to data conversion transformation.

3. Open data conversion add two column and change the data type to Unicode string [DT_WSTR] as in below screenshot

4.Click ok

5. Now connect data conversion to excel destination

6. Map the new column which we have converted to Unicode string [DT_WSTR] to excel destination columns as below screenshot


7. Click ok

8. Execute package

9. It will run successfully 

 Note:To fix this Issue in SQL table side, change the datatype to nvarchar instead of varchar then create your SSIS package to load table data into excel destination you won't face the above issue.

Sunday, October 25, 2015

ORDER BY items must appear in the select list if SELECT DISTINCT is specified.


I was working in a scenario where I need to get distinct names order by some other column which was not a part of Select Query. Below I will show you how to fix this error if you will stuck with the same error.

The error message was as below.

Msg 145, Level 15, State 1, Line 1
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

Below I will create a test environment to create the error and in next step I will show how to fix this above error.

--test table script

create table #TestOrder
(id int identity(1,1),OrderName varchar(100),Period varchar(100),Month int)

Insert into #TestOrder values('TV','Jan15',1)
Insert into #TestOrder values('WashingMachine','Feb15',2)
Insert into #TestOrder values('TV','Mar15',3)
Insert into #TestOrder values('Refrigrator','Apr15',4)
--Need Distinct of below two since we don't know the month
Insert into #TestOrder values('TV','2015',13)
Insert into #TestOrder values('TV','2015',14)

If you will analyse the above table you can notice that we have three main Column orderName Period and Month column.

OrderName-  Shows the order Name
Period-          MonthName along with the Year when the order was made
Month-         No. of Month for the order for eg-for Jan=1,for Feb=2 and so on…

Now if  you will see the last two records the Period is 2015 and Month is 13 and 14 which means for these two records it was not sure for which month this Product was ordered so they made period entry as 2015 and put a random entry as 13 and 14 for the Month.

Requirement-So, the Requirement was we wants to see a single column in the output which will be distinct and will be concatenation of Order Name and period order by Month. Since for the last two records the order is same so we will take distinct of it and keep it them in the top as below screenshot



 I tried the below query to take distinct of order and period name order by month I got the above error.

select distinct OrderName +'_'+Period as OrderPeriod  from #TestOrder
order by month

Msg 145, Level 15, State 1, Line 1
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

Solution- So below code will give you distinct name order by a column Month which is not a part of Select query.

select  OrderName +'_'+Period as Order_Period  from #TestOrder
group by  OrderName +'_'+Period
order by min(Month)

Output as Required.



Note: Since we are using aggregate function in the order by so you must need to write group by before order by clause other wise it will throw the error. for eg as below

select  OrderName +'_'+Period as Order_Period  from #TestOrder
--group by  OrderName +'_'+Period
order by min(Month)

Msg 8120, Level 16, State 1, Line 1
Column '#TestOrder.OrderName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

So the above query will solve your issue if in case you are stuck with some similar scenario where you want distinct record order by a column which is not a part of Select query.


If you have any other issue regarding SQL & BI where you are stuck write to me in the comment box I will try to help you out.