Subscriptions

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

Table Specific Information

Select

Server-Side Query Support

The component 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 component.

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 The allowed values are 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. The allowed values are 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	Integer	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. The allowed values are 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

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.


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.

SSIS Components for Stripe