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

How to write Data to Excel Sheet (.xlsx) from DataTable dynamically in SSIS using SCript Task and C#?

I am getting the data from SQL Server and trying to insert that data in Excel Sheet. One of the column contains long text with ' in the string. I feel when string contains ' then its having issues and it's failing to  insert into excel sheet. Failing while  Excel_OLE_Cmd.ExecuteNonQuery(); and it says Missing operator in the query expression.

Excel_OLE_Cmd.CommandText = command;
I am getting the command while debugging

"Insert into Sheet1([ID],[Name],[Message],[Date],[Category])Values('123','test','After time Form\r\n:\r\n\r\nDescribe:This is it's history book.','11/14/2019','Development')"

Problem is in Message column. This field contains lots of text with quotes. I am not sure how to fix in the code that I wrote. Please fix my code.
code.docx
0
SSIS Package, How to export data from Ado.net Source to Excel Destination (.xlsx)?
I have Varchar(MAX) in Sql Server. But when I use the ADO.net source field datatype changes to DT_NTEXT.

Data not exported to excel

Excel Destination Editor: Create Table 'Sheet1'('ID 'INT, 'Comment 'NTEXT)
Getting the Error: Excel Destination An OLEDb error is occured. An error is due to binding for 'Comment' column, The binding status was DT_NTEXT. Cannot create an OLEDB Accessor.
0
Hi Guys

I have a script that looks at a folder called Freak in this directory: C:\Users\szakhour\Desktop\Freak - there are .json files within this folder, each file name will be called something though will always start with LastScore

Snip20200203_5.png
What I want to do is import whatever .json file comes into the Freak folder into SQL whenever the script is run, script below

Declare @JSON varchar(max)

select @JSON = BulkColumn from openrowset (bulk 'C:\Users\szakhour\Desktop\Freak\*.json', single_clob) as j 

SELECT *
FROM OPENJSON (@JSON)
WITH 
(
    LeaderboardName varchar(20), 
    LeaderboardNameFull varchar(20), 
    PlayerCount varchar(20), 
    PlayerName varchar(20), 
    Score nvarchar(20),
    ZombiesKilled varchar(15)'$.ScoreDetails.ZombiesKilled',
    Accuracy varchar(50)'$.ScoreDetails.Accuracy',
    DamageTaken varchar(20)'$.ScoreDetails.DamageTaken',
    SecondsInLevel varchar(30)'$.ScoreDetails.SecondsInLevel',
    Deaths varchar(50)'$.ScoreDetails.Deaths'
)

Open in new window



I hope this makes sense. If not let me know.

Any help is greatly appreciated,
0
Need help running an SSIS package from a remote computer.

Hello.  I created a DTSX package that works with our MS SQLServer 2008 R2 software.
It works perfectly from the machine in which MS SQL Server is installed.  Can someone help me run the package from a remote computer?
Thanks.
0
After quite long, i started using SSIS, and ran into issue with building an SSIS package dynamically. Below is the scenario.

I built an SSIS package to load the data from a file in a specific folder to a table in SQL Server database, and archive the file to the respective folder. Since there can be several files in a folder, i used for each loop container to load the files. Below Attached are the screenshots/snippets of the package.  This package is executing successfully without any issues. I cloned and extended the same package for few other clients as well. As part of cloning, i modified the source folder and Archive folder connections and also the respective client database connection for the staging table. And they are all executing successfully without any issues.  Now, whenever i have to make a change, i am ending up implementing the same across all the packages. This is becoming little bit of hectic. So, i'm thinking just to build 1 package in the central database which has all the necessary linked server connections, and want to dynamically pass the server/database values and the source/archive folder values. Can someone help me to achieve this. Or, if there any other better way to implement this as part of 1 package that would be helpful too. I appreciate any help from experts in advance. Thank you.

Whole Package Data Flow Task in the For Each Loop ContainerSSIS Package Variables
Just an fyi- I tried various ways but wasn't successful. For example:

I created a …
0
In the data flow designer, i want a PARAMETER as my TABLE NAME in an ODBC Destination task. This is doable with OLE DB Destination because in the "Data Access Mode" drop down, "Table name or view name variable" is an option. This isn't an option in ODBC, so how do I apply a parameter or varaible to the table name in an ODBC Destination task?
0
Hello all:

I am having some issues with sql server deadlocking.  Just to explain my scenario: I have the following tables:

tblInvoice
tblInvoiceArchive

tblInvoice has a trigger that fires whenever a modification is done and adds or updates data to the tblInvoiceArchive. It''l basically add a new record if it's a new invoice or update existing data if the invoice was already existing.

Additionally, I have a SSIS package that runs based on a flag in the tblInvoiceArchive table.  Once it runs it sets the flag to false.

What's been happening is people are making modifications to some of the data in the tblInvoice table, which then updates the tblInvoiceArchive table at the same time my SSIS package is trying to reset the flag in the tblInvoiceArchive table.

Is there something I can do that will allow these operations to occur with out the deadlocking?  My SSIS package updates just one field, while a modification to tblInvoice may update any of the other fields (except the flag column).

Many thanks for your assistance!
0
New 2016 server, new SQL 2017 SQL install.  Install SSIS and cannot connect with SSMS v18.4.  
Getting this when trying to connect in SSMS.  I have tried to connect on SSMS 2016 with same error.

 TITLE: Connect to Server
------------------------------
Cannot connect to myserver.
------------------------------
ADDITIONAL INFORMATION:
Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)
For help, click: https://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&LinkId=20476
------------------------------
Connecting to the Integration Services service on the computer "myserver" failed with the following error: "Class not registered".
This error can occur when you try to connect to a SQL Server 2005 Integration Services service from the current version of the SQL Server tools. Instead, add folders to the service configuration file to let the local Integration Services service manage packages on the SQL Server 2005 instance.
For help, click: http://go.microsoft.com/fwlink/?LinkId=506689
------------------------------
Connecting to the Integration Services service on the computer "myserver" failed with the following error: "Class not registered".
This error can occur when you try to connect to a SQL Server 2005 Integration Services service from the current version of the SQL Server tools. Instead, add folders to the service configuration file to let the local Integration Services service manage packages on the SQL Server 2005 …
0
Dear Experts
We have just started implementing Power BI for reporting and analytics, can you please help me understand following concepts data lake, data mart, data warehouse  and ETL, thanks in advance.
0
I'm having trouble getting yesterdays date in the following format:  yymmdd
I was using the following but having trouble with the year, I can only get 4 digit year not 2.

4 Digit year
(DT_WSTR, 4) YEAR(DATEADD("day",-1,GETUTCDATE()))  +
RIGHT("0" + (DT_WSTR, 2) DATEPART("MM", DATEADD("day", -1, GETUTCDATE())),2) +
RIGHT("0" + (DT_WSTR, 2) DATEPART("DD", DATEADD("day", -1, GETUTCDATE())),2)

2 digit year (THIS DOESN'T WORK)
RIGHT("0" + (DT_WSTR, 2) DATEPART("YY", DATEADD("day",-1, GETUTCDATE())),2)  +
RIGHT("0" + (DT_WSTR, 2) DATEPART("MM", DATEADD("day", -1, GETUTCDATE())),2) +
RIGHT("0" + (DT_WSTR, 2) DATEPART("DD", DATEADD("day", -1, GETUTCDATE())),2)
0
Error: 0xC0029151 at Upload to SFTP, Execute Process Task: In Executing "C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe" "-ExecutionPolicy Unrestricted -file "\\dsetl\d$\Powershell\WinSCP_SFTP_FileUpload.ps1" "\\dsetl\d$\SSIS\WinSCP\WinSCPnet.dll" "Sftp" "passive" "none" "22" "mcty-8-jfd8q6mxnt9pfcnd42dq4.ftp.marketingcloudops.com" "70479" "ET123!" "ssh-rsa 2048 ypSoIuzwzTskowWJDqNud7RU9Q4rlzLQ6RQvVIxz5sM=" "" "/Import/" "CustomerInfo_ET.zip" "\\heritagecoin.com\shares\Data Services\ExportData\ExactTarget_DevUploads\\" "CustomerInfo_ET_Olivia.zip" "true"" at "", The process exit code was "-196608" while the expected was "0".
Task failed: Upload to SFTP
Warning: 0x80019002 at CustomerInfo_ET: SSIS Warning Code
0
I have an SSIS package in Visual Studio 2019 that uses an ODATA connection to pull data from one database and update a table in another SQL Server database.

An SQL Server Job runs every 5 minutes to execute the package but duplicate data is being populated.

How can I stop the data from duplicating?
Do I need the SQL Server Job to run this package that has been deployed to the SSIS Service Catalog?
0
Code: 0xC0014005
   Source: BBDW_DIM_ATTRIBUTE
   Description: The connection type "OLEDB" specified for connection manager "BBETL_DB_CONN_DW" is not recognized as a valid connection manager type. This error is returned when an attempt is made to create a connection manager for an unknown connection type. Check the spelling in the connection type name.
End Error

Error: 2019-11-26 14:16:18.23
   Code: 0xC0010018
   Source: BBDW_DIM_ATTRIBUTE
   Description: Error loading value "<DTS:ConnectionManagers xmlns:DTS="www.microsoft.com/SqlServer/Dts"><DTS:ConnectionManager DTS:refId="Package.ConnectionManagers[BBETL_DB_CONN_DW]" DTS:CreationName="OLEDB" DTS:DTSID="{A218DADF-A0C5-4D8D-B77D-26FF02F87601}" DTS:ObjectName="BBETL_DB_CONN_DW" from node "DTS:ConnectionManagers".
End Error
0
Hi,

I am trying to upload data into MSSQL 2016 using SSIS.


The process is:

SSIS.png
What I am trying to do is rollback all data if anything fails. First I tried using TransactionOption=Required but this locked the target tables until the SSIS sequence had completed. I  have then tried tried using SQL Tasks to create BEGIN, ROLLBACK and COMMIT transactions as I read this is preferred by some and it does not lock the tables. However, my tables are still locked. I am unable to SELECT data from the tables involved until the sequence has compeleted.

Transactions:

BEGIN TRANSACTION T1;
ROLLBACK TRANSACTION T1;
COMMIT TRANSACTION T1;

Is what I am trying to do even possible and if so what is the best way to do it?

Thanks, Greg
0
I get this error when trying to run DTEXEC from a command prompt.

Microsoft (R) SQL Server Execute Package Utility
Version 12.0.5000.0 for 32-bit
Copyright (C) Microsoft Corporation. All rights reserved.

Started:  5:56:27 PM
DTExec: Stopping the package execution.
Could not create DTS.Application because of error 0x80040154
Started:  5:56:27 PM
Finished: 5:56:27 PM
Elapsed:  0.234 seconds

This is the version info I see when I click Help..About in SSDT.

SSDT Version 15.9.17
The .NET Framework version is 4.8.03761.

Microsoft (R) SQL Server Execute Package Utility
Version 12.0.5000.0 for 32-bit
Copyright (C) Microsoft Corporation. All rights reserved.

Started:  5:56:27 PM
DTExec: Stopping the package execution.
Could not create DTS.Application because of error 0x80040154
Started:  5:56:27 PM
Finished: 5:56:27 PM
Elapsed:  0.234 seconds


This is the version of SSDT I use.

Microsoft SQL Server Data Tools for Visual Studio 2017  (SSDT)
Version 15.9.17
VisualStudio.15.Release/15.9.17+28307.905
Microsoft .NET Framework
Version 4.8.03761

Installed Version: IDE Standard

Microsoft Visual Studio Tools for Applications 2017   00371-40000-00001-AA911
Microsoft Visual Studio Tools for Applications 2017

C# Tools   2.10.0-beta2-63501-03+b9fb1610c87cccc8ceb74a770dba261a58e39c4a
C# components used in the IDE. Depending on your project type and settings, a different version of the compiler may be used.

Common Azure Tools   1.10
0
We have an AIX unix server we need to connect to and then run two commands using C# or something in SSIS. How can we do this? Ultimately we are working to automate what is currently a manual task for us.

cd /strbus/fw
sh job/edid2c
0
I have an XML generated out of a stored procedure and stored in a variable in Execute Process task. Scoping at package level.
I am trying to pass that variable on to a power shell script as input to write to an  file with .xml . It executes without any error ,but no file is generated.
Arguments is set through a variable having the following ..

 
   "-ExecutionPolicy ByPass  -command \". '" + @[User::PowerShellScriptFile] + "' " + @[User::XMLFileNamePath] +@[User::XMLOutput]

Open in new window


PowerShellScriptFile --> Power shell file path  
XMLFileNamePath      --> UNC path where file is to be generated

PS file:

   
param ([string]$Filepath , [string]$XMLText)
    
    if (!(Test-Path $Filepath))
    {
       New-Item -path $Filepath -type "file" 
       Add-Content -path $Filepath -value $XMLText
    
       Write-Host "Created new file and text content added"
    }
    else
    { 
      Clear-Content -path $Filepath
      Add-Content -path $Filepath -value $XMLText
    
      Write-Host "File already exists and new text content added"
    }

Open in new window

Please guide me for correct argument value, as I tried with some other options. Following Argument value when run with filepath hard coded in above Ps file ( and removing the params of course) ,does generate file /append the text correctly.  So I guess no permissions issue .

-ExecutionPolicy ByPass -command  ". 'C:\PSCheck\XMLFilegenerate.ps1' Hello"

Open in new window

0
I have a very large table with 1 billion rows
One of the columns stores full address details (street, town, city, country)

Previously it was varchar(max) but since we moved to multilingual we have to change it to nvarchar(max) which doubles the size

Our database size is now growing by 10.5GB a day and I'm looking at ways to reduce this
Is there a way to compress this data and uncompress on the web app?

Open to ideas and suggestions

SQL Server 2017 enterprise
0
ssis 2017 export to excel works in Visual Studio but not in the cloud

Please provide an example of how to do it.

Thanks
0
I’m loading multiple .txt files in Ssis using for each loop, data flow tasks.
First I load the data from a flat file to a staging table, then from staging to target table.
However, I’d like to get the exact number of rows loaded to destination table.I mean, I want the target table count.
Can that be accomplished by using rowcount transformation between source and destination or do I need to get the count after
The destination table is loaded using A SQL statement.Does rowcount give us the rows that were loaded from source to destination
, which can be considered the destination table rowcount?

Could someone help me ASAP.

Thank a million in advance
0
Hi,

I'm using SSIS 2017, SQL SERVER 2016, Azure cloud:

My ssis package gets data from a table and exports it to an existing Excel file successfully.

Problem I'm having with is that in PROD, I can't have the existing Excel file out there, so the SSIS package fails when CONNECTION MANAGER Excel tries to find the destination Excel but can't find it.

Options?

I'm thinking:
1) Create an Excel template in another folder and copy it over to PROD every time the ssis package runs.   But in SSIS TOOLBOX, I don't see any objects for copying a file.
2) Create an Excel template in another folder and copy it over to PROD every time the ssis package runs.   I could create a Powershell vbscript (that copies the Excel template to the PROD folder)  but I don't know if Azure can run the Powershell.
3) any other ideas?  Pleaes provide example code.
4) Can ssis create a new Excel file?
0
Hi, I'm using Visual Studio 2017 to create an SSIS package.  Data isha from SQL Server 2016.

Connection Manager has an object EXCEL.

My package has 2 objects: 1) A dataflow control to get data from SQL Server.   2) Excel Data Flow that points to the Connection Manager object Excel.

I can export data from a SQL SERVER to Excel just fine.

QUESTION: How do I reorder the fields that are exported to Excel?
0
Hi,

I'm using Visual Studio 2017 and Azure SQL Server 2016.  I'm creating an SSIS package and a script in c#.net

QUESTION:
1. My ReadOnlyVariables = System::StartTime
2. In my script Task, how do I get the value of System::StartTime?
3. how do I format datetime to yyyymmdd_hhmm
4. I want to write this information to a LOG file.  If the app1.LOG NOT exist, then create a log file (E.G., DATESTAMP + ' package kickoff'), else append data to it (E.G., datestamp + 'package completed'.
5. My WriteVariables = User:varFolderName.    in my script task, how do I assign a value to to varFolderName ?

What is the code to do this?  Thanks.
0
Hi,

I'm using Visual Studio 2017.

QUESTION:
In Microsoft Azure Enterprise Cloud Services (ECS), I have a variable ENVIRONMENT=DEV\DEV

In SSIS 2017, how do assign a variable varEnvironment equal to the ECS variable ENVIRONMENT=DEV\DEV ?
0
In SSIS 2017, I have a package that has:

1. tab Control Flow has 1 task 'Data Flow'

2. tab 'Data Flow' has 2 objects:
2a. DataFlow component that imports data from SQL SERVER 2016.
2B. FlatFileComponent that exports data to a file.    The Connection Manager has an object named DestinationConnectionFlatFile

ISSUE:
1. DestinationConnectionFlatFile refers to a fixed path such as C:\myfile.txt   but I want it to point to a variable varMyFile whose value is \\myserver\myfile.txt

How do I do that?
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.