Adding date as variable in SSIS

Hello there,

I have a package with a control flow and data Flow task. I run some query and create views before sending the result to a db destination. In the view I want to replace the static dates to dynamic dates from variable. But for some reason i am not able to run the task. Can somebody help me please.
Error I get is

SSIS package "FDO Phase 2 Sales Test (2) (1).dtsx" starting.
Error: 0xC002F210 at Execute SQL Task Sales Discount, Execute SQL Task: Executing the query "IF OBJECT_ID ('ViewProvinceProductSalesNDiscount',..." failed with the following error: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: Execute SQL Task Sales Discount
SSIS package "FDO Phase 2 Sales Test (2) (1).dtsx" finished: Success.
The program '[10224] FDO Phase 2 Sales Test (2) (1).dtsx: DTS' has exited with code 0 (0x0).


my SQL query look like so,

USE phtest;
GO
IF OBJECT_ID ('ViewProvinceProductSalesNDiscount', 'V') IS NOT NULL
DROP VIEW ViewProvinceProductSalesNDiscount;
GO
CREATE VIEW ViewProvinceProductSalesNDiscount
AS 
SELECT 
a.code as  ProductCode,
a.product as ProductName,
a.province as Province,
SUM(a.SalesQty+a.DisSum) as SalesQtyinclDis,
SUM(a.DisSum) as SalesDiscount
FROM 
(SELECT
    SUM(dbo.DeliveryOrderDetail.quantity) AS SalesQty,
    (dbo.DeliveryOrderDetail.quantityDiscount+dbo.DeliveryOrderDetail.supplierQuantityDiscount) as DisSum,
    dbo.Tafsil.description AS product,
    dbo.Province.name      AS province,
    dbo.Customer.organizationName,
    RIGHT('000' + CAST(kol.code AS VARCHAR), 3) + RIGHT('00'+ CONVERT(VARCHAR,dbo.Mohin.code),3) + '' + RIGHT('0000' + CAST(tafsil.code AS VARCHAR), 4) AS code
FROM
    dbo.DeliveryOrderDetail
INNER JOIN
    dbo.DeliveryOrder
ON
    (
        dbo.DeliveryOrderDetail.orderId = dbo.DeliveryOrder.id)
INNER JOIN
    dbo.Receipt
ON
    (
        dbo.DeliveryOrder.receiptId = dbo.Receipt.id)
INNER JOIN
    dbo.TafsilLink
ON
    (
        dbo.DeliveryOrderDetail.productId = dbo.TafsilLink.targetid)
        INNER JOIN
    dbo.Tafsil
ON
    (
        dbo.Tafsil.Id = dbo.TafsilLink.sourceid)
LEFT OUTER JOIN
    dbo.Customer
ON
    (
        dbo.DeliveryOrder.customerId = dbo.Customer.id)
INNER JOIN
    dbo.ContactInformation
ON
    (
        dbo.Customer.organizationContactInformationId = dbo.ContactInformation.id)
INNER JOIN
    dbo.Address
ON
    (
        dbo.ContactInformation.addressId = dbo.Address.id)
INNER JOIN
    dbo.City
ON
    (
        dbo.Address.cityId = dbo.City.id)
INNER JOIN
    dbo.District
ON
    (
        dbo.City.districtId = dbo.District.id)
INNER JOIN
    dbo.Province
ON
    (
        dbo.District.provinceId = dbo.Province.id)
INNER JOIN
    dbo.Mohin
ON
    (
        dbo.Tafsil.mohinId = dbo.Mohin.id)
INNER JOIN
    dbo.Kol
ON
    (
        dbo.Mohin.kolId = dbo.Kol.id)
WHERE
    dbo.Receipt.receiptDate >= ?
AND dbo.Receipt.receiptDate < '2015-08-23'
AND dbo.Receipt.receiptType = 10
GROUP BY
    dbo.DeliveryOrderDetail.quantityDiscount,
    dbo.DeliveryOrderDetail.supplierQuantityDiscount,
    dbo.Tafsil.description,
    dbo.Province.name,
    dbo.Customer.organizationName,
    dbo.Kol.code,
    dbo.Mohin.code,
    dbo.Tafsil.code) a
     GROUP BY
    a.code,
    a.province,
a.product

Open in new window


2015-09-01_13-54-06.png2015-09-01_13-55-33.png2015-09-01_13-55-47.png
zolfAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vikas GargBusiness Intelligence DeveloperCommented:
Hello,

Are you able to run the query in the SSMS  ?
0
PortletPaulfreelancerCommented:
WHERE
    dbo.Receipt.receiptDate >= ?
AND dbo.Receipt.receiptDate < '2015-08-23'
AND dbo.Receipt.receiptType = 10

why is that date hardcoded into the query?

it would be simple for that query to produce no rows, all that is needed is for that ? to be any date after 2015-08-23
0
Prakash SamariyaIT ProfessionalCommented:
Can you try like below in your Query
WHERE
    dbo.Receipt.receiptDate >= CAST(ISNULL(?,'1900-01-01') as varchar(10))
AND dbo.Receipt.receiptDate < '2015-08-23'
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Vikas GargBusiness Intelligence DeveloperCommented:
HI,

I think it would be good idea to create function rather than parameter view.

CREATE FUNCTION dbo.GetCertificate(@receiptDate datetime)
RETURNS TABLE
AS
   RETURN 
SELECT 
a.code as  ProductCode,
a.product as ProductName,
a.province as Province,
SUM(a.SalesQty+a.DisSum) as SalesQtyinclDis,
SUM(a.DisSum) as SalesDiscount
FROM 
(SELECT
    SUM(dbo.DeliveryOrderDetail.quantity) AS SalesQty,
    (dbo.DeliveryOrderDetail.quantityDiscount+dbo.DeliveryOrderDetail.supplierQuantityDiscount) as DisSum,
    dbo.Tafsil.description AS product,
    dbo.Province.name      AS province,
    dbo.Customer.organizationName,
    RIGHT('000' + CAST(kol.code AS VARCHAR), 3) + RIGHT('00'+ CONVERT(VARCHAR,dbo.Mohin.code),3) + '' + RIGHT('0000' + CAST(tafsil.code AS VARCHAR), 4) AS code
FROM
    dbo.DeliveryOrderDetail
INNER JOIN
    dbo.DeliveryOrder
ON
    (
        dbo.DeliveryOrderDetail.orderId = dbo.DeliveryOrder.id)
INNER JOIN
    dbo.Receipt
ON
    (
        dbo.DeliveryOrder.receiptId = dbo.Receipt.id)
INNER JOIN
    dbo.TafsilLink
ON
    (
        dbo.DeliveryOrderDetail.productId = dbo.TafsilLink.targetid)
        INNER JOIN
    dbo.Tafsil
ON
    (
        dbo.Tafsil.Id = dbo.TafsilLink.sourceid)
LEFT OUTER JOIN
    dbo.Customer
ON
    (
        dbo.DeliveryOrder.customerId = dbo.Customer.id)
INNER JOIN
    dbo.ContactInformation
ON
    (
        dbo.Customer.organizationContactInformationId = dbo.ContactInformation.id)
INNER JOIN
    dbo.Address
ON
    (
        dbo.ContactInformation.addressId = dbo.Address.id)
INNER JOIN
    dbo.City
ON
    (
        dbo.Address.cityId = dbo.City.id)
INNER JOIN
    dbo.District
ON
    (
        dbo.City.districtId = dbo.District.id)
INNER JOIN
    dbo.Province
ON
    (
        dbo.District.provinceId = dbo.Province.id)
INNER JOIN
    dbo.Mohin
ON
    (
        dbo.Tafsil.mohinId = dbo.Mohin.id)
INNER JOIN
    dbo.Kol
ON
    (
        dbo.Mohin.kolId = dbo.Kol.id)
WHERE
    dbo.Receipt.receiptDate >= @receiptDate
AND dbo.Receipt.receiptDate < '2015-08-23'
AND dbo.Receipt.receiptType = 10
GROUP BY
    dbo.DeliveryOrderDetail.quantityDiscount,
    dbo.DeliveryOrderDetail.supplierQuantityDiscount,
    dbo.Tafsil.description,
    dbo.Province.name,
    dbo.Customer.organizationName,
    dbo.Kol.code,
    dbo.Mohin.code,
    dbo.Tafsil.code) a
     GROUP BY
    a.code,
    a.province,
a.product
GO

Open in new window

0
zolfAuthor Commented:
Thanks guys for all your comments.

Are you able to run the query in the SSMS  ?
No I get error [Error Code: 156, SQL State: S1000]  Incorrect syntax near the keyword 'VIEW'.
one question can I have parameters in query with View

why is that date hardcoded into the query?
I wanted to test in SSIS first with one data parameter before changing all static data

Please use 'MS SQL Server' for all things SSIS, SSRS, SSAS.  Thanks.
Thanks for informing!!

I think it would be good idea to create function rather than parameter view.
I think in SSIS the parameter names is not allowed for OLE DB, it has to be in the form of ?

Prakash Samariya

I tried using your code but I still get same error

USE ptest;
GO
IF OBJECT_ID ('ViewProvinceProductSalesNDiscount', 'V') IS NOT NULL
DROP VIEW ViewProvinceProductSalesNDiscount;
GO
CREATE VIEW ViewProvinceProductSalesNDiscount
AS 
SELECT 
a.code as  ProductCode,
a.product as ProductName,
a.province as Province,
SUM(a.SalesQty+a.DisSum) as SalesQtyinclDis,
SUM(a.DisSum) as SalesDiscount
FROM 
(SELECT
    SUM(dbo.DeliveryOrderDetail.quantity) AS SalesQty,
    (dbo.DeliveryOrderDetail.quantityDiscount+dbo.DeliveryOrderDetail.supplierQuantityDiscount) as DisSum,
    dbo.Tafsil.description AS product,
    dbo.Province.name      AS province,
    dbo.Customer.organizationName,
    RIGHT('000' + CAST(kol.code AS VARCHAR), 3) + RIGHT('00'+ CONVERT(VARCHAR,dbo.Mohin.code),3) + '' + RIGHT('0000' + CAST(tafsil.code AS VARCHAR), 4) AS code
FROM
    dbo.DeliveryOrderDetail
INNER JOIN
    dbo.DeliveryOrder
ON
    (
        dbo.DeliveryOrderDetail.orderId = dbo.DeliveryOrder.id)
INNER JOIN
    dbo.Receipt
ON
    (
        dbo.DeliveryOrder.receiptId = dbo.Receipt.id)
INNER JOIN
    dbo.TafsilLink
ON
    (
        dbo.DeliveryOrderDetail.productId = dbo.TafsilLink.targetid)
        INNER JOIN
    dbo.Tafsil
ON
    (
        dbo.Tafsil.Id = dbo.TafsilLink.sourceid)
LEFT OUTER JOIN
    dbo.Customer
ON
    (
        dbo.DeliveryOrder.customerId = dbo.Customer.id)
INNER JOIN
    dbo.ContactInformation
ON
    (
        dbo.Customer.organizationContactInformationId = dbo.ContactInformation.id)
INNER JOIN
    dbo.Address
ON
    (
        dbo.ContactInformation.addressId = dbo.Address.id)
INNER JOIN
    dbo.City
ON
    (
        dbo.Address.cityId = dbo.City.id)
INNER JOIN
    dbo.District
ON
    (
        dbo.City.districtId = dbo.District.id)
INNER JOIN
    dbo.Province
ON
    (
        dbo.District.provinceId = dbo.Province.id)
INNER JOIN
    dbo.Mohin
ON
    (
        dbo.Tafsil.mohinId = dbo.Mohin.id)
INNER JOIN
    dbo.Kol
ON
    (
        dbo.Mohin.kolId = dbo.Kol.id)
WHERE
    dbo.Receipt.receiptDate >= CAST(ISNULL(?,'1900-01-01') as varchar(10))
AND dbo.Receipt.receiptDate < '2015-08-23'
AND dbo.Receipt.receiptType = 10
GROUP BY
    dbo.DeliveryOrderDetail.quantityDiscount,
    dbo.DeliveryOrderDetail.supplierQuantityDiscount,
    dbo.Tafsil.description,
    dbo.Province.name,
    dbo.Customer.organizationName,
    dbo.Kol.code,
    dbo.Mohin.code,
    dbo.Tafsil.code) a
     GROUP BY
    a.code,
    a.province,
a.product

Open in new window

0
Vikas GargBusiness Intelligence DeveloperCommented:
Hello,

Zolf you can not create parameterized view you can create function which will accept parameter and solve your purpose too..

I have shown the code in my above answer
0
zolfAuthor Commented:
Vikas,

Thanks for your feedback!!. How can I also add the drop/create view with your function. can you please give me the code to also incluse that.see my original query
0
Vikas GargBusiness Intelligence DeveloperCommented:
Hello,

Here is the code for you

IF EXISTS (
    SELECT * FROM sysobjects WHERE id = object_id(N'GetCertificate') 
    AND xtype IN (N'FN', N'IF', N'TF')
)
    DROP FUNCTION GetCertificate
GO

CREATE FUNCTION dbo.GetCertificate(@receiptDate datetime)
RETURNS TABLE
AS
   RETURN 
SELECT 
a.code as  ProductCode,
a.product as ProductName,
a.province as Province,
SUM(a.SalesQty+a.DisSum) as SalesQtyinclDis,
SUM(a.DisSum) as SalesDiscount
FROM 
(SELECT
    SUM(dbo.DeliveryOrderDetail.quantity) AS SalesQty,
    (dbo.DeliveryOrderDetail.quantityDiscount+dbo.DeliveryOrderDetail.supplierQuantityDiscount) as DisSum,
    dbo.Tafsil.description AS product,
    dbo.Province.name      AS province,
    dbo.Customer.organizationName,
    RIGHT('000' + CAST(kol.code AS VARCHAR), 3) + RIGHT('00'+ CONVERT(VARCHAR,dbo.Mohin.code),3) + '' + RIGHT('0000' + CAST(tafsil.code AS VARCHAR), 4) AS code
FROM
    dbo.DeliveryOrderDetail
INNER JOIN
    dbo.DeliveryOrder
ON
    (
        dbo.DeliveryOrderDetail.orderId = dbo.DeliveryOrder.id)
INNER JOIN
    dbo.Receipt
ON
    (
        dbo.DeliveryOrder.receiptId = dbo.Receipt.id)
INNER JOIN
    dbo.TafsilLink
ON
    (
        dbo.DeliveryOrderDetail.productId = dbo.TafsilLink.targetid)
        INNER JOIN
    dbo.Tafsil
ON
    (
        dbo.Tafsil.Id = dbo.TafsilLink.sourceid)
LEFT OUTER JOIN
    dbo.Customer
ON
    (
        dbo.DeliveryOrder.customerId = dbo.Customer.id)
INNER JOIN
    dbo.ContactInformation
ON
    (
        dbo.Customer.organizationContactInformationId = dbo.ContactInformation.id)
INNER JOIN
    dbo.Address
ON
    (
        dbo.ContactInformation.addressId = dbo.Address.id)
INNER JOIN
    dbo.City
ON
    (
        dbo.Address.cityId = dbo.City.id)
INNER JOIN
    dbo.District
ON
    (
        dbo.City.districtId = dbo.District.id)
INNER JOIN
    dbo.Province
ON
    (
        dbo.District.provinceId = dbo.Province.id)
INNER JOIN
    dbo.Mohin
ON
    (
        dbo.Tafsil.mohinId = dbo.Mohin.id)
INNER JOIN
    dbo.Kol
ON
    (
        dbo.Mohin.kolId = dbo.Kol.id)
WHERE
    dbo.Receipt.receiptDate >= @receiptDate
AND dbo.Receipt.receiptDate < '2015-08-23'
AND dbo.Receipt.receiptType = 10
GROUP BY
    dbo.DeliveryOrderDetail.quantityDiscount,
    dbo.DeliveryOrderDetail.supplierQuantityDiscount,
    dbo.Tafsil.description,
    dbo.Province.name,
    dbo.Customer.organizationName,
    dbo.Kol.code,
    dbo.Mohin.code,
    dbo.Tafsil.code) a
     GROUP BY
    a.code,
    a.province,
a.product
GO

                                          

Open in new window

0
zolfAuthor Commented:
Vikas, my apologies but I see some issue with this method. if you see my Control Flow I have 2 SQL execution where I create 2 VIEWS and then in the 3rd COntrol Flow I have a data flow task where I UNION the 2 VIEW tables to get their result and put them in a destination table. Now your method we have removed the VIEW so how can i union them. maybe I am missing something
0
zolfAuthor Commented:
here is my Data Flow screenshot where I am taking 2 views from the source db

SELECT
      productcode
    , productname
    , province
     , SUM(SalesQtyinclDis) AS SalesQty
    , SUM(SalesDiscount) AS SalesDiscount
    , ISNULL(SUM(salesReturn),0) AS SalesReturn
    , ISNULL( SUM(discountReturn),0) AS SalesDiscountReturn
FROM (
      SELECT
          productcode
        , productname
        , province
        , salesReturn    
        , discountReturn as discountReturn
        , cast(null as int) as SalesQtyinclDis
        , cast(null as decimal(12,3)) as SalesDiscount
      FROM ViewProductSaleReturnNDiscount
      UNION ALL
      SELECT
          productcode
        , productname
        , province
        , null    
        , null
        , SalesQtyinclDis
        , SalesDiscount as SalesDiscount
      FROM ViewProvinceProductSalesNDiscount
) sq
GROUP BY productcode
       , productname
       , province
ORDER BY province

Open in new window


1
0
Vikas GargBusiness Intelligence DeveloperCommented:
Hello,

What I would suggest you is Create a Stored Procedure.

Step 1- Store the result of query which is for first view in a table variable.

Step 2- Store the result of query of view 2 in second table variable.

Step 3-Now you can use the two table variable in the third query where you have used the views.


So there will be only single stored procedure called from DFT. No need of execute sql tasks.

I think this is short and sweet solution
0
zolfAuthor Commented:
Thanks for your feedback.

Store the result of query which is for first view in a table variable.
what do you mean. I did not understand.

Also how do you run a Function in Studio to test.I am new to Function!!

also the data parameter will be like so 2015-02-21.correct??
0
Vikas GargBusiness Intelligence DeveloperCommented:
Hi,

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE StoredProc 
	
	@receiptDate Datetime
	
AS
BEGIN
	
	SET NOCOUNT ON;

DECLARE @tbl1 TABLE (col1 int, col2 int,col3 int, col4 int, col5 int)

INSERT INTO @tbl1

SELECT 
a.code as  ProductCode,
a.product as ProductName,
a.province as Province,
SUM(a.SalesQty+a.DisSum) as SalesQtyinclDis,
SUM(a.DisSum) as SalesDiscount
FROM 
(SELECT
    SUM(dbo.DeliveryOrderDetail.quantity) AS SalesQty,
    (dbo.DeliveryOrderDetail.quantityDiscount+dbo.DeliveryOrderDetail.supplierQuantityDiscount) as DisSum,
    dbo.Tafsil.description AS product,
    dbo.Province.name      AS province,
    dbo.Customer.organizationName,
    RIGHT('000' + CAST(kol.code AS VARCHAR), 3) + RIGHT('00'+ CONVERT(VARCHAR,dbo.Mohin.code),3) + '' + RIGHT('0000' + CAST(tafsil.code AS VARCHAR), 4) AS code
FROM
    dbo.DeliveryOrderDetail
INNER JOIN
    dbo.DeliveryOrder
ON
    (
        dbo.DeliveryOrderDetail.orderId = dbo.DeliveryOrder.id)
INNER JOIN
    dbo.Receipt
ON
    (
        dbo.DeliveryOrder.receiptId = dbo.Receipt.id)
INNER JOIN
    dbo.TafsilLink
ON
    (
        dbo.DeliveryOrderDetail.productId = dbo.TafsilLink.targetid)
        INNER JOIN
    dbo.Tafsil
ON
    (
        dbo.Tafsil.Id = dbo.TafsilLink.sourceid)
LEFT OUTER JOIN
    dbo.Customer
ON
    (
        dbo.DeliveryOrder.customerId = dbo.Customer.id)
INNER JOIN
    dbo.ContactInformation
ON
    (
        dbo.Customer.organizationContactInformationId = dbo.ContactInformation.id)
INNER JOIN
    dbo.Address
ON
    (
        dbo.ContactInformation.addressId = dbo.Address.id)
INNER JOIN
    dbo.City
ON
    (
        dbo.Address.cityId = dbo.City.id)
INNER JOIN
    dbo.District
ON
    (
        dbo.City.districtId = dbo.District.id)
INNER JOIN
    dbo.Province
ON
    (
        dbo.District.provinceId = dbo.Province.id)
INNER JOIN
    dbo.Mohin
ON
    (
        dbo.Tafsil.mohinId = dbo.Mohin.id)
INNER JOIN
    dbo.Kol
ON
    (
        dbo.Mohin.kolId = dbo.Kol.id)
WHERE
    dbo.Receipt.receiptDate >= @receiptDate
AND dbo.Receipt.receiptDate < '2015-08-23'
AND dbo.Receipt.receiptType = 10
GROUP BY
    dbo.DeliveryOrderDetail.quantityDiscount,
    dbo.DeliveryOrderDetail.supplierQuantityDiscount,
    dbo.Tafsil.description,
    dbo.Province.name,
    dbo.Customer.organizationName,
    dbo.Kol.code,
    dbo.Mohin.code,
    dbo.Tafsil.code) a
     GROUP BY
    a.code,
    a.province,
a.product

END
GO

Open in new window


I have shown sample that how you can store the result in one table variable

Now add second table variable

And then use those table variable in place of you used view in your DFT query

and call the SP from DFT like

EXEC SPNAME ? ? (parameters)
0
zolfAuthor Commented:
So i need to remove those 2 Execute SQL Task in the Control Flow and this SP query will be what kind of task.Can you please tel me....damn this has become so difficult compared to what I started off :(
0
Vikas GargBusiness Intelligence DeveloperCommented:
Zolf,

Its very simple now.

You don't need the execute sql task.

IN SP you just store the result of query which you are using for First view in Table variable 1

then store the result of query used in view 2 in Table variable 2

Then write your dft query and just replace the viewname with table variable 1 and 2.

Hope this is very clear method.
0
zolfAuthor Commented:
Guys any help on solving my issue!!
0
zolfAuthor Commented:
I want to stick to my original solution and need help on that matter
0
zolfAuthor Commented:
Could it be that I have my parameter in a View and SSIS does not allow this
0
Vikas GargBusiness Intelligence DeveloperCommented:
Zolf,

You can not have parameter in View but you can create function for the same as I stated earlier
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.