Improve company productivity with a Business Account.Sign Up

x

SSIS

SQL Server Integration Services (SSIS) is a component of the Microsoft SQL Server database software that can be used to perform a broad range of data migration tasks. SSIS is a platform for data integration and workflow applications. It features a fast and flexible data warehousing tool used for data extraction, transformation, and loading (ETL). The tool may also be used to automate maintenance of SQL Server databases and updates to multidimensional cube data. SSIS replaced Data Transformation Services, which had been a feature of SQL Server since Version 7.0.

Share tech news, updates, or what's on your mind.

Sign up to Post

Hi,

I'm using SSIS 2008 and SQL SERVER 2008R2.

I want to get the current datestamp instead of  @[System::ContainerStartTime]   but I don't know what it's called.

In SSIS, I have a SEND MAIL TASK, then in  EXPRESSIONS, I have set MessageSource to :

        (DT_STR, 100, 1252)  @[System::ContainerStartTime] + " -- " + "Package completed -- SUCCESS"
0
Free Tool: IP Lookup
LVL 12
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Can you please tell me where that error is?
SSIS package "C:\Users\Kathleen A\Documents\Visual Studio 2012\Projects\TxtLoading\TxtLoading\Basic Package.dtsx" starting.
Information: 0x4004300A at Import all the apps, SSIS.Pipeline: Validation phase is beginning.
Information: 0x4004300A at Import all the apps, SSIS.Pipeline: Validation phase is beginning.
Information: 0x40043006 at Import all the apps, SSIS.Pipeline: Prepare for Execute phase is beginning.
Information: 0x40043007 at Import all the apps, SSIS.Pipeline: Pre-Execute phase is beginning.
Information: 0x402090DC at Import all the apps, List of apps [2]: The processing of file "C:\Users\Kathleen A\Desktop\Apps.txt" has started.
Information: 0x4004300C at Import all the apps, SSIS.Pipeline: Execute phase is beginning.
Information: 0x402090DE at Import all the apps, List of apps [2]: The total number of data rows processed for file "C:\Users\Kathleen A\Desktop\Apps.txt" is 5.
Error: 0xC0202009 at Import all the apps, OLE DB Destination [23]: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 11.0"  Hresult: 0x80004005  Description: "The statement has been terminated.".
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 11.0"  Hresult: 0x80004005  Description: "Cannot insert the value NULL into column 'AppId', table 'Movies.dbo.Apps'; column does not allow nulls. INSERT 

Open in new window

0
Can you please tell me how to i go back to the design view? I am using Visual Studio 2012 SIS in windows 7 environment.
screenshot.PNG
0
Can you please tell me what this error means?
screenshot.PNG
[0DE8:16C4][2018-03-18T01:14:42]i001: Burn v3.11.0.1528, Windows v6.1 (Build 7601: Service Pack 1), path: C:\Users\Harris\AppData\Local\Temp\{42915321-4BFA-466D-AC06-4F791449F7CE}\.cr\SSDT-Setup-ENU.exe
[0DE8:16C4][2018-03-18T01:14:42]i000: Initializing string variable 'ProductVersionVar' to value '14.0.16156.0'
[0DE8:16C4][2018-03-18T01:14:42]i000: Initializing string variable 'HeaderText' to value 'Release 15.5.2'
[0DE8:16C4][2018-03-18T01:14:42]i000: Initializing string variable 'SubHeaderText' to value 'Microsoft SQL Server Data Tools'
[0DE8:16C4][2018-03-18T01:14:42]i000: Initializing string variable 'VSSqlEvergreenLink' to value 'https://aka.ms/vs/15/release/vs_sql.exe'
[0DE8:16C4][2018-03-18T01:14:42]i000: Initializing string variable 'LicenseTermsUrl' to value 'https://go.microsoft.com/fwlink/?LinkID=853826&clcid=0x409'
[0DE8:16C4][2018-03-18T01:14:42]i000: Initializing string variable 'PreviewStatementUrl' to value 'https://go.microsoft.com/fwlink/?LinkID=824140&clcid=0x409'
[0DE8:16C4][2018-03-18T01:14:42]i000: Initializing string variable 'SystemLocaleMismatch' to value 'Setup detected mismatch between the languages of the installer and the OS. Use the English-language version installer, or install the language pack and change the format and system locales through the regional settings in the control panel.'
[0DE8:16C4][2018-03-18T01:14:42]i000: Initializing string variable 

Open in new window

0
I am using MS Access as the front end (Microsoft Office 365 ProPlus) and currently have a SQL2008 server. I am migrating to a newer SQL 2014 server and are experiencing issues with code written using SQLDMO while testing in the new environment. I have an SSIS job that is called using the now deprecated SQLDMO that is obviously failing in the new environment. I am having limited success finding how to use SQLSMO in a similar fashion. Can someone please give some general direction (or specific!) so that the application can call these jobs in the new SQL server 2014?

Here is the code snippet we use:

Private Sub Command45_Click()

   Set objSQL = CreateObject("SQLDMO.SQLServer")
   ' Leave as trusted connection
   objSQL.LoginSecure = True
   ' Change to match the name of your SQL server
   objSQL.Connect "Server2K14"
   Set objJob = objSQL.JobServer
   For Each job In objJob.Jobs
      If InStr(1, job.Name, "SSIS Bills") > 0 Then
         MsgBox job.Name
         job.Start
         MsgBox "Job Started"
      End If
   Next

End Sub

Thank you, and please let me know if I have failed to provide necessary information.
KLB
0
I am using Subscriptions to save an SSRS report as pdf into a network folder mjd\send at 6:00 am on weekdays. The name of the file is rptPot.pdf. The file is overwritten each day. I would like to create a package in integration services that would rename this file to rptPot_CurrentDate_CurrentTime so that I have a copy of each day's file.  How would I accomplish this ? Thanks.
0
Hi,

Hope someone could help me with this one (I am not a Developer).
I need to convert a local JSON file (see attached file) to a csv. in Powershell

In this way I can create an SSIS that will read the file and the column I need.

The extension  should be JSON instead of txt.


I got to a point that a file been created but without records

$pathToOutputFile = "C:\Freshdesk\Daily_Export_json.csv"
$file = "C:\Freshdesk\Daily_Export.json"
Get-Content -Path $file
$json = ConvertFrom-JSON (Get-Content $file -Raw)
$json.DataFeed.Rows | Select "performed_at",
"ticket_id","performer_type","activity/automation/type","activity/automation/rule","activity/send_email/agent_ids/0","performer_id","activity/note/id",
"activity/note/type","activity/status","activity/ticket_type","activity/requester_id","activity/source","activity/priority","activity/new_ticket","activity/Product Fixlist",
"activity/agent_id","activity/deleted,activity/Choose Product","activity/Choose Component","activity/How can we help?","activity/Product","activity/Product version","activity/group",
"activity/Vendor Ticket#","activity/description","activity/send_email/requester_id/0" | Export-CSV $pathToOutputFile

Open in new window



Thanks,
Roey
Daily_Export.txt
0
Hi,

I have an excel file contains around 1200 columns and 100,000 rows. I have created a SSIS package to export this dataset to a SQL Table. I am using dataflow task to export these data to SQL table, but it takes very long time more than 1 hour. The client excepts to complete this task within 1 minute.

Can someone suggest a better way to optimize this process or a better way to achieve this?

Thank you for your help!
0
We recently upgraded from SQL Server 2012 to 2016 and have noticed it takes longer than we expected to insert from one database into the other.  See below for details and specific question
Hardware Specs
•      SQL Server 2016 (SP1-CU6)
•      Virtual Server (32 processers and 32GB RAM)
•      Windows Server 2012 R2
•      1 instance of SQL server
•      Database A (compatibility =130)      
•      Database B (compatibility =130)      
•      Table Statistics in database A up-to-date
SSIS package runs Stored Procedure on DatabaseA (Estimated Cost = 2750)  (35Million rows returned from query)
Loads to table in DatabaseB. Table has 2 indexes, one clustered another non clustered.
Stored Procedure returns 25 columns. Most are integers, money or small varchars (255) is the biggest varchar.
When deployed to the server, the package take over 30 minutes to run.  
In SSIS we have tried “AutoAdjustBufferSize” = True
And DefaultBufferMaxRows  = 10,000
AutoAdjustBuffer size seems to be slower than using DefaultBufferMaxRows  = 10,000.

Questions:  
Is there anyway to make this run faster?
What are some possible things to check which might cause it to run slowly?
0
I have a need where I need to retrieve CSV files from an external source which will contain either updated or new records for tables in my SQL database (one CSV file per table).

This needs to be a job that will open each file (file name will be the table name to be updated or added to) in the folder, update existing records or add new records, depending on what's in the file (could be both, or one or the other).

Is SSIS the best approach for this (this is where I'm leaning)? If so, how would one go about automating this process? Some things to note:

1. The files could be different every day since not all tables may or may be updated or added to every day.
2. There may be no files in the folder on any given day.
3. Obviously, since each CSV is related to a different table, the column names will be different for each CSV (which would probably affect column mappings in the Data Flow of the SSIS package), and I really don't want to create a SSIS package for every single possible table in the database. That'd be 100s of SSIS packages.

Any help would be appreciated, especially on how to determine if a record in the CSV is new or if it's an existing record. If it's an existing record, all columns will be updated in the table based on what's in the CSV.

Please let me know if you need more details from me on this.

I'd also be open to purchasing existing software if this becomes too cumbersome.

Thanks in advance!
0
Free Tool: SSL Checker
LVL 12
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

I have a blocking report (Blocking Detected On Production Server), Can anybody please help me understand that?

Here are the details:-

Session_id || Status || blocking_session_id || wait_type || wait_resource || WaitSec || cpu_time || Logical_reads || reads || writes

156 || Suspended || 137 || LCK_M_IS || OBJECT: 18:925246351:4 || 172.660000 || 7 || 204 || 66 || 0 ||

Anything more about this will be very helpful.

Thanks in Advance.
0
ISSUE :

Cannot process the SSIS package ( to process Cube) from SQL Agent Job  but the package is processed successfully without any error from  SQL Server Data Tools (Visual Studio) when processed manually .  Environment used is WINDOWS 2012 R2 && SQL SERVER 2016.

OBSERVATION:

1.Same SSIS Package mentioned above( to process Cube) is processed successfully without any error from  SQL Server Agent .  Environment used is WINDOWS 2016 && SQL SERVER 2016.  It seems that problem may be with WINDOWS 2012 R2.

Error Message :

Executed as user: JDADELIVERS\_dbadmin. Microsoft (R) SQL Server Execute Package Utility  Version 13.0.1601.5 for 64-bit  Copyright (C) 2016 Microsoft. All rights reserved.    Started:  3:31:45 AM  Error: 2018-02-01 03:59:34.01     Code: 0x00000001     Source: Connect AS Server      Description: Exception has been thrown by the target of an invocation.  End Error  DTExec: The package execution returned DTSER_FAILURE (1).Started:  3:31:45 AM  Finished: 3:59:34 AM  Elapsed:  1668.25 seconds.  Process Exit Code 1.  The step failed.


Please help me in identifying the problem  and the root cause of the error. . Will appreciate your help in this regard.

Thanks,
SRK.
0
I have a unique situation where my source system is Oracle, the Oracle DBAs (ODBAs) are using Golden Gate to push data to a replica that is also Oracle. The SQL Server (SDBAs) want to ingest the data from the Oracle Replica, and if we could get transactional replication to work, that would just be fantastic. I have read through some MS KBs that it is possible to have an Oracle source as the replication publisher. I wonder though if this would work knowing the Publication source is a golden gate replica.

I know with some Change Data Capture (CDC) tools, they actually use the replication engine of the source and target system. So enabling SQL Server replication on top of that does not always work as advertised.

The trick here is we are trying to not pay for another set of GG licenses if possible.

Are there any experts out there who have done this in the past?
1
So, I got this error message

"failed with the following error: "ORA-06550: line 0, column 0: PLS-00907: cannot load library unit USAGE.SP_MISSING_FILE_CASE_CLOSURE (referenced by )". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly."

sometimes, but sometimes my SSIS job runs normally. Can someone provide me with some kind of solution? I cannot see any pattern in it's failure except PROCEDURE which is always the same.

I have searched google for answer or anything that can help me but I was not able to find a thing. Can anyone please help me?
0
When importing a flat file I get an error because one of the values in the file is negative number?  I tried a bunch of the different datatypes and a still get the problem. Any clue what the issue is?
0
Hello,
I am trying to get the date for two days ago.  It works with the following, except for when a new month begins.

(DT_WSTR,4)DATEPART("yyyy",GetDate()) +
 RIGHT("0" + (DT_WSTR,2)DATEPART("mm",GetDate()) ,2) +
 RIGHT("0" + (DT_WSTR, 2) DATEPART("DD", DATEADD("day", -2, GETDATE())),2)

Today it yields 20180231, but it should yield 20180130.
Thank you for your help with this.
0
query issue

select * from city where population >= '100000' and NAME='USA';

why above query is not resulting any results while trying on ms sql server

please advise
0
Hi,

I'm using SSIS 2008.

I have an 1 SSIS package that has 4 different SEQUENCE CONTAINERS.

My dtexec passes a parameter to the SSIS to tell it which SEQUENCE CONTAINER to run.

QUESTION: Can I have 4 dtexec running the same SSIS package at the same because they each run a different SEQUENCE CONTAINER?

Thanks.
0
I am using Visual Studio to create a SSIS package to convert a SQL file into a Pipe Delimited text file without the header row.
I have tried several times to skip the header rows.  I set the number to 1 on the general screen  for Header Rows to Skip and enabled the Column Name in the first data row.

I still get the row headers.

What am I doing wrong?

Thanks

Glen

Glen
0
Free Tool: Path Explorer
LVL 12
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

I would like to add records to an existing table but only with data in selected columns leaving the rest un-populated.  Of the columns that are not included are ones that are specified as non-null, including some numeric fields.

In the past, I have been able to do this by using the Tasks -> Import Data wizard in SSMS.  Suddenly, this has stopped working giving an error message about trying to put NULL data into a non-null column (see the error message in the 2nd image regarding the ADCOST column in the dbo.ADCOSTS table.   The columns in green (commented out) are just my notes for which columns to include and are not part of any commands.

Something has changed that now prevents me from doing something that I had been for several years.  Either that, or I've changed something, but I can't find what that would be.

I've attached a couple of images that might help.  It shows the table to be appended and the columns that I wish to include.
IMG_6288.JPG
IMG_6290.JPG
0
Hi All

I'm trying to understand why I cannot see Integration in msdb.dbo.syscategories. The SQL instance I'm running is MS SQL 2016 Standard SP1, version details below

Microsoft SQL Server Management Studio                                    13.0.16000.28
Microsoft Analysis Services Client Tools                                            13.0.1700.441
Microsoft Data Access Components (MDAC)                                    10.0.14393.0
Microsoft MSXML                                                                                     3.0 6.0
Microsoft Internet Explorer                                                             9.11.14393.0
Microsoft .NET Framework                                                             4.0.30319.42000
Operating System                                                                             6.3.14393

In the syscategories I can see 22 items, see below

name
[Uncategorized (Local)]
[Uncategorized (Multi-Server)]
[Uncategorized]
[Uncategorized]
Data Collector
Database Engine Tuning Advisor
Database Maintenance
Full-Text
Jobs from MSX
Log Shipping
REPL-Alert Response
REPL-Checkup
REPL-Distribution
REPL-Distribution Cleanup
REPL-History Cleanup
Replication
REPL-LogReader
REPL-Merge
REPL-QueueReader
REPL-Snapshot
REPL-Subscription Cleanup
Reports

Open in new window


In the SQL Object Explorer, I can see Integration Services Catalogs and the Integrated Services is enabled as a feature whilst running MS SQL 2016 Setup (see SQL Features.pdf).

What I'm trying to do here is create a SQL Job and select Integration in the Category (see Category List). The Server OS is Windows Server 2016 Datacenter. Any help is greatly appreciated!

StevieSQL-Features.pdfCategory-List.pdf
0
In my SQL SSIS package, I have exported some data to file.

I want to export to a file with following filename format: filename_YYYYMMDD_hhmmss.txt

In my expression builder, it type in the following:
"C:\\BlueCreek Files\\Account\\AmesAccount"+"_"+(DT_STR, 50, 1252) Getdate()+".txt"

Result are:

C:\BlueCreek Files\Account\AmesAccount_2018-01-23 13:03:21.122000000.txt

This is close, but how can I get it in the exact format I want?
0
What does this error mean and how does it get resolved?

An error occurred while assigning a value to variable "TruncateLength": "Single Row result set is specified, but no rows were returned."

I am trying to load data using a SSIS package and receive the error above. Does it have to do with data in the files I am trying to load (for example some of the data needs to shortened in length or a comma) or is it something wrong with the SSIS package? I have two types of files and mapping instructions for each type I have four different files with regards to the data for each type. The same error comes up for each file. This leads me believe it is not with data, but with the SSIS package itself.  

Please let me know if any additional information is needed.
0
Hi all,
is there a documented process to do replication with log shipping where the only thing available to the destination database is the log files?  If not I will have to setup an SSIS package to do restores of the log files to the destination database, which seems problematic?

thanks,
john.
0
Good day,

I have been asked to get involved with the installation and configuration of a SQL Server BI environment.

I am seeking some guidance for the deployment architecture for SQL Server, SSIS, SSAS, and SSRS as well as SSMS and Visual Studio.

To date, my experience with these products have had all products installed on a single server, with a fairly straightforward, default installation (and training modules a la AdventureWorks).

Given that I have been provided with a development server for DB (Windows 2012R2) and separate server for the apps tier (IIS), I am seeking information/clarification as to which products need to be installed on which server and in which order.

Added into this, is the requirement for SSIS to connect to an Oracle database leveraging Microsoft (Attunity) Connectors for Oracle.

QUESTION: Can you please confirm which of the following products should be installed on which server? (and references/links to the Manual(s) which I should be reading would also be appreciated. Any links to architecture diagrams depicting a similar architecture (with or without Attunity) would be a significant bonus.

MS SQL Server 2016
MS SQL Server Data Tools (2016), including
   - MS SQL Server Analytic Services (SSAS)
   - MS SQL Server Integration Services (SSIS)
   - MS SQL Server Reporting Services (SSRS)
MS SQL Server Management Studio
MS Visual Studio 2015
Microsoft (Attunity) Connectors for Oracle and Teradata (v4)
MS SQL Server …
0

SSIS

SQL Server Integration Services (SSIS) is a component of the Microsoft SQL Server database software that can be used to perform a broad range of data migration tasks. SSIS is a platform for data integration and workflow applications. It features a fast and flexible data warehousing tool used for data extraction, transformation, and loading (ETL). The tool may also be used to automate maintenance of SQL Server databases and updates to multidimensional cube data. SSIS replaced Data Transformation Services, which had been a feature of SQL Server since Version 7.0.