SSIS Components for Stripe

Build 24.0.9062

Subscriptions

Create, update, delete, and query the available Subscriptions in Stripe.

Table Specific Information

Select

Server-Side Query Support

The 本製品 uses the Stripe API to filter the results by the following columns and operators while the rest of the filter is executed client-side within the 本製品.

  • Id, CustomerId, PlanId, Status, MetadataAggregate, AutomaticTaxEnabled, CollectionMethod, TestClock and AccountId support the following operator: =.
  • CreatedAt supports the following operators: =, !=, >, >=, <, <=.

You can select:

A subscription by specifying its Id:

SELECT * FROM Subscriptions WHERE Id = 'mySubscriptionId'

Subscriptions created after a specific date (Created may be used with the >, >=, <, <=, or = conditions and may be used twice to specify a range):

SELECT * FROM Subscriptions WHERE CreatedAt > '2016-01-03'

Subscriptions that belong to a customer:

SELECT * FROM Subscriptions WHERE CustomerId = 'cus_12345678'

Subscriptions that belong to a plan:

SELECT * FROM Subscriptions WHERE PlanId = 'myPlanId'

Subscriptions with a specific status:

SELECT * FROM Subscriptions WHERE Status = 'active'

Subscriptions with metadata:

SELECT * from Subscriptions where MetadataAggregate ='{\"fdgm\":\"gdfgkm\"}'

Insert

CustomerId and ItemsAggregate columns are required to create a new subscription:

INSERT INTO Subscriptions (CustomerId, TrialEnd, ItemsAggregate,CancelAtPeriodEnd) VALUES ('cus_PDYvuww0WynoTg', '2023-12-22', '[{\"price\": \"43\",\"quantity\":\"12\"}]', true)

Alternatively, you can provide CustomerId and PlanId columns to create a new subscription:

INSERT INTO Subscriptions (CustomerId, PlanId, TrialEnd,CancelAtPeriodEnd) VALUES ('cus_LMchUD47S8Eumg', 'price_1ONAgvCZ8rn6wdu','2024-12-22', true)

Update

To update a subscription, specify the Id:

UPDATE Subscriptions SET ApplicationFeePercent = 0, MetadataAggregate='[{\"order\":\"14453\"}]' WHERE Id = 'sub_A9WZGVTbvgBJ4t'

Delete

To delete a subscription, specify the Id.

DELETE FROM Subscriptions WHERE Id = 'sub_A9WZGVTbvgBJ4t'

Columns

Name Type ReadOnly References Description
Id [KEY] String True

The Id of the subscription.

CustomerId String False

Customers.Id

The Id of the customer who owns the subscription.

PlanId String False

Plans.Id

The Id of the plan.

PlanActive Boolean True

Whether the plan can be used for new purchases.

PlanAggregateUsage String True

Specifies a usage aggregation strategy for plans of usage_type=metered.

PlanAmount Integer True

The unit amount in cents to be charged, represented as a whole integer if possible.

PlanAmountDecimal String True

The unit amount in cents to be charged, represented as a decimal string with at most 12 decimal places.

PlanBillingScheme String True

Describes how to compute the price per period.

PlanCreated Datetime True

Time at which the object was created. Measured in seconds since the Unix epoch.

PlanCurrency String True

Three-letter ISO currency code, in lowercase. Must be a supported currency.

PlanInterval String True

The frequency at which a subscription is billed. One of day, week, month or year.

PlanIntervalCount Integer True

The number of intervals (specified in the interval attribute) between subscription billings.

PlanLivemode Boolean True

Has the value true if the object exists in live mode or the value false if the object exists in test mode.

PlanMetadata String True

Set of key-value pairs that you can attach to an object.

PlanMeter String True

The meter tracking the usage of a metered price.

PlanNickname String True

A brief description of the plan, hidden from customers.

PlanObject String True

String representing the object's type. Objects of the same type share the same value.

PlanProduct String True

The product whose pricing this plan determines.

PlanTiersMode String True

Defines if the tiering price should be graduated or volume based.

PlanTransformUsage String True

Apply a transformation to the reported usage or set quantity before computing the amount billed.

PlanTrialPeriodDays Integer True

Default number of trial days when subscribing a customer to this plan using trial_from_plan=true.

PlanUsageType String True

Configures how the quantity per period should be determined.

ApplicationFeePercent Decimal False

A positive decimal that represents the fee percentage of the subscription invoice amount that will be transferred to the application of the Stripe account owner each billing period.

CancelAtPeriodEnd Boolean False

If the subscription has been canceled with the at_period_end flag set to true, cancel_at_period_end on the subscription will be true. You can use this attribute to determine whether a subscription that has a status of active is scheduled to be canceled at the end of the current period.

CanceledAt Datetime True

If the subscription has been canceled, the date of that cancellation. If the subscription was canceled with cancel_at_period_end, canceled_at will still reflect the date of the initial cancellation request, not the end of the subscription period when the subscription is automatically moved to a canceled state.

CreatedAt Datetime True

The creation date.

CurrentPeriodEnd Datetime True

End of the current period that the subscription has been invoiced for. At the end of this period, a new invoice will be created.

CurrentPeriodStart Datetime True

Start of the current period that the subscription has been invoiced for.

CouponId String False

The id of the discount coupon.

EndedAt Datetime True

If the subscription has ended (either because it was canceled or because the customer was switched to a subscription to a new plan), the date the subscription ended.

Quantity Double True

The quantity of the plan to which the customer should be subscribed. For example, if your plan is $10/user/month, and your customer has 5 users, you could pass 5 as the quantity to have the customer charged $50 (5 x $10) monthly.

StartDate Datetime True

Date the most recent update to this subscription started.

Status String True

The status of the subscription

使用できる値は次のとおりです。active, past_due, unpaid, canceled, incomplete, incomplete_expired, trialing, all, ended, paused

TaxPercent Decimal True

If provided, each invoice created by this subscription will apply the tax rate, increasing the amount billed to the customer.

TrialEnd Datetime False

If the subscription has a trial, the end of that trial.

TrialStart Datetime True

If the subscription has a trial, the beginning of that trial.

MetadataAggregate String False

The set of key/value pairs that you can attach to a subscription object.

BillingCycleAnchor Datetime False

Determines the date of the first full invoice, and, for plans with month or year intervals, the day of the month for subsequent invoices.

DefaultPaymentMethod String False

ID of the default payment method for the subscription

ItemsAggregate String False

List of subscription items, each with an attached price.

LatestInvoice String True

The ID of the most recent invoice this subscription has generated.

PendingSetupIntent String True

You can use this SetupIntent to collect user authentication when creating a subscription without immediate payment or updating a subscription's payment method, allowing you to optimize for off-session payments.

PendingUpdate String True

If specified, pending updates that will be applied to the subscription once the latest_invoice has been paid.

BillingThresholds String False

Define thresholds at which an invoice will be sent, and the subscription advanced to a new billing period

AutomaticTaxEnabled Boolean False

Automatic tax settings for this subscription.

AutomaticTaxLiabilityAccount String False

The connected account being referenced when type is account.

AutomaticTaxLiabilityType String False

Type of the account referenced.

CollectionMethod String False

Either charge_automatically, or send_invoice. When charging automatically, Stripe will attempt to pay this subscription at the end of the cycle using the default source attached to the customer.

使用できる値は次のとおりです。charge_automatically, send_invoice

DaysUntilDue Integer False

Number of days a customer has to pay invoices generated by this subscription. This value is null for subscriptions where collection_method=charge_automatically

DefaultSource String False

ID of the default payment source for the subscription.

DefaultTaxRates String False

The tax rates that will apply to any subscription item that does not have tax_rates set. Invoices created will have their default_tax_rates populated from the subscription.

LiveMode Boolean True

Has the value true if the object exists in live mode or the value false if the object exists in test mode.

NextPendingInvoiceItemInvoice Datetime True

Specifies the approximate timestamp on which any pending invoice items will be billed according to the schedule provided at pending_invoice_item_interval

PauseCollectionBehavior String False

If specified, payment collection for this subscription will be paused. The payment collection behavior for this subscription while paused. One of keep_as_draft, mark_uncollectible, or void.

PauseCollectionResumesAt Datetime False

If specified, payment collection for this subscription will be paused. The time after which the subscription will resume collecting payments.

PaymentSettings String False

Payment settings passed on to invoices created by the subscription.

PendingInvoiceItemInterval String False

Specifies an interval for how often to bill for any pending invoice items. It is analogous to calling Create an invoice for the given subscription at the specified interval. Specifies invoicing frequency. Either day, week, month or year.

PendingInvoiceItemIntervalCount Integer False

Specifies an interval for how often to bill for any pending invoice items. It is analogous to calling Create an invoice for the given subscription at the specified interval. The number of intervals between invoices.

Schedule String True

The schedule attached to the subscription

TestClock String True

ID of the test clock this customer belongs to.

TransferData String False

The account (if any) the subscription's payments will be attributed to for tax reporting, and where funds from each payment will be transferred to for each of the subscription's invoices.

CancelAt Datetime False

A date in the future at which the subscription will automatically get canceled.

Description String False

The subscription's description.

CancellationDetailsComment String False

Details about why this subscription was cancelled. Additional comments about why the user canceled the subscription, if the subscription was canceled explicitly by the user.

CancellationDetailsFeedback String False

Details about why this subscription was cancelled. The customer submitted reason for why they canceled, if the subscription was canceled explicitly by the user.

CancellationDetailsReason String False

Details about why this subscription was cancelled. The reason why this subscription was cancelled.

Currency String False

Three-letter ISO currency code, in lowercase. Must be a supported currency.

TrialSettingsEndBehaviorPaymentMethod String False

Settings related to subscription trials. Indicates how the subscription should change when the trial ends if the user did not provide a payment method.

使用できる値は次のとおりです。cancel, pause, create_invoice

OnBehalfOf String False

The account on behalf of which to charge, for each of the subscription?s invoices.

DiscountId String True

The Id of the discount.

DiscountCheckoutSession String True

The Checkout session that this coupon is applied to, if it is applied to a particular session in payment mode. Will not be present for subscription mode.

DiscountCustomer String True

The ID of the customer associated with this discount.

DiscountStart Datetime True

If the subscription has a trial, the beginning of that trial.

DiscountEnd Datetime True

If the subscription has a trial, the end of that trial.

DiscountInvoice String True

The invoice that the discount's coupon was applied to, if it was applied directly to a particular invoice.

DiscountInvoiceItem String True

The invoice line item id that the discount's coupon was applied to if it was applied directly to a invoice line item.

DiscountPromotionCode String True

The promotion code applied to create this discount.The promotion code applied to create this discount.

DiscountSubscription String True

The subscription that this coupon is applied to, if it is applied to a particular subscription.

DiscountCouponCreatedAt Datetime True

The creation date.

DiscountCouponCurrency String True

If amount_off has been set, the three-letter ISO code for the currency of the amount to take off.

DiscountCouponName String True

Name of the coupon displayed to customers on, for instance, invoices or receipts.

DiscountCouponDuration String True

One of forever, once, and repeating. Describes how long a customer who applies this coupon will get the discount.

DiscountCouponDurationInMonths Integer True

the number of months the coupon applies.

DiscountCouponAmountOff Integer True

Amount (in the currency specified) that is taken off the subtotal of any invoices for this customer.

DiscountCouponPercentOff Integer True

Percent that will be taken off the subtotal of any invoices for this customer for the duration of the coupon. For example, a coupon with a percent_off of 50 will make a $100 invoice $50 instead.

DiscountCouponValid Boolean True

Taking account of the above properties, whether this coupon can still be applied to a customer.

DiscountCouponMaxRedemptions Integer True

Maximum number of times this coupon can be redeemed, in total, before it is no longer valid.

DiscountCouponRedeemBy Datetime True

Date after which the coupon can no longer be redeemed.

DiscountCouponTimesRedeemed Integer True

Number of times this coupon has been applied to a customer.

DiscountCouponObject String True

String representing the object's type. Objects of the same type share the same value.

DiscountCouponLiveMode Boolean True

Has the value true if the object exists in live mode or the value false if the object exists in test mode.

DiscountCouponMetadataAggregate String True

Set of key-value pairs that you can attach to an object.

Application String True

ID of the Connect Application that created the subscription.

BillingCycleAnchorConfigDayofMonth Integer False

The day of the month of the billing_cycle_anchor.

BillingCycleAnchorConfigHour Integer False

The hour of the day of the billing_cycle_anchor.

BillingCycleAnchorConfigminute Integer False

The minute of the hour of the billing_cycle_anchor.

BillingCycleAnchorConfigmonth Integer False

The month to start full cycle billing periods.

BillingCycleAnchorConfigsecond Integer False

The second of the minute of the billing_cycle_anchor.

InvoiceSettingsAccountTaxIds String False

Invoice settings account tax ids.

InvoiceSettingsIssuerType String False

Invoice settings issuer type.

InvoiceSettingsIssuerAccount String False

The connected account being referenced when InvoiceSettingsIssuerType is account.

Pseudo-Columns

SELECT ステートメントのWHERE 句では、疑似カラムフィールドを使用して、データソースから返されるタプルを詳細に制御することができます。

Name Type Description
IsProrate Boolean

Flag indicating whether to prorate switching plans during a billing cycle.

ProrationDate Date

If set, the proration will be calculated as though the subscription was updated at the given time. It can also be used to implement custom proration logic, such as prorating by day instead of by second, by providing the time that you wish to use for proration calculations.

PaymentBehavior String

Use allow_incomplete to transition the subscription to status=past_due if a payment is required but cannot be paid. This allows you to manage scenarios where additional user actions are needed to pay a subscription?s invoice.

ProrationBehavior String

Determines how to handle prorations when the billing cycle changes (e.g., when switching plans, resetting billing_cycle_anchor=now, or starting a trial), or if an item?s quantity changes. The default value is create_prorations.

TrialPeriodDays Integer

The number of trial period days before the customer is charged for the first time. If set, trial_period_days overrides the default trial period days of the plan the customer is being subscribed to.

AccountId String

The Id of the connected account to get subscriptions for.

Copyright (c) 2024 CData Software, Inc. - All rights reserved.
Build 24.0.9062