We help IT Professionals succeed at work.

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.

I have ssis package that reads in excel file

I know there are some rows in the excel file that have non date values in cells that should have date values

I tried redirecting rows to an error table that has columns that are defined as varchar(255)

However package keeps failing saying conversion error

I know there is a conversion error just want the records to write to a table

not sure what I am missing
0
I want to read SSIS package code into a PowerShell variable and parse for the VersionBuild. I've found examples online and it works for some but not others. I've come across two formats of XML, one seems for an older versions of SISS, one for newer. The two different versions are below and the code examples I've found work for the older versions but not newer.

The code I'm using is:
[xml]$pkgCode = Get-Content -Path $pkg.FullName
$verNode = $pkgCode.Executable.Property | Where-Object {$_.Name -Like 'VersionBuild'}
$BuildVersion = $verNode.'#text'

Open in new window

Is there code that will work reading either XML format?

Older version (I can parse this)
<?xml version="1.0"?><DTS:Executable xmlns:DTS="www.microsoft.com/SqlServer/Dts" DTS:ExecutableType="SSIS.Package.2">
<DTS:Property DTS:Name="PackageFormatVersion">3</DTS:Property>
<DTS:Property DTS:Name="VersionComments"></DTS:Property>
<DTS:Property DTS:Name="CreatorName">WFS\Mahesh.Sabne</DTS:Property>
<DTS:Property DTS:Name="CreatorComputerName">CSN-C-SABNEM01</DTS:Property>
<DTS:Property DTS:Name="CreationDate" DTS:DataType="7">4/24/2012 12:28:56 AM</DTS:Property>
<DTS:Property DTS:Name="PackageType">5</DTS:Property>
<DTS:Property DTS:Name="ProtectionLevel">0</DTS:Property>
<DTS:Property DTS:Name="MaxConcurrentExecutables">-1</DTS:Property>
<DTS:Property DTS:Name="PackagePriorityClass">0</DTS:Property>
<DTS:Property DTS:Name="VersionMajor">1</DTS:Property>
<DTS:Property DTS:Name="VersionMinor">0</DTS:Property>
<DTS:Property DTS:Name="VersionBuild">526</DTS:Property>
<DTS:Property DTS:Name="VersionGUID">{B026A66C-971C-47D9-84D2-4A8075450EF0}</DTS:Property>
...

Open in new window

Newer version (returns nothing with my code)
<?xml version="1.0"?>
<DTS:Executable xmlns:DTS="www.microsoft.com/SqlServer/Dts"
  DTS:refId="Package"
  DTS:CreationDate="3/25/2013 2:31:59 PM"
  DTS:CreationName="Microsoft.Package"
  DTS:CreatorComputerName="85NGXR1"
  DTS:CreatorName="GEHA\T457"
  DTS:DTSID="{F6322EA9-AA4B-4B62-836E-A37DFF6ECD49}"
  DTS:EnableConfig="True"
  DTS:ExecutableType="Microsoft.Package"
  DTS:LastModifiedProductVersion="14.0.3026.27"
  DTS:LocaleID="1033"
  DTS:ObjectName="CL-GF-90290-UHCClaimsHistoryTableLoad"
  DTS:PackageType="5"
  DTS:ProtectionLevel="0"
  DTS:VersionBuild="373"
  DTS:VersionGUID="{800CC4BE-C063-44D0-B960-8CAE1EAEB804}">
  <DTS:Property
   ...

Open in new window

0
Hello Experts,

After lot of efforts I did my SSIS pkg working in my local, which connects to Oracle DB using Oracle.ManagedDataAccess.Client dll(32-bit). After deploying it to 2016 server it says below and also I was using script task in the SSIS pkg 2017 and the target version is sql serer 2014.

Cannot load script for execution.



Thanks,
ASPDEV
0
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
I have an excel sheet that has 45 columns, am using it to create 4 different tables in SSIS. How will I use  the conditional split to do that? I will need to use data conversion before loading to  the 4 different OLE DB destination
0
Exporting a data having nvarchar(4000) and also other fields having nvarchar(2000)  onto .xls or .xlsx file  using SSIS runs into an issue .Size of 'Column' is too long. If I keep these columns  to nvarchar(255) and also set the project property Run64bitRuntime to true,I receive the following error

[Excel Destination [83]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC00F9304.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.
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
I have scoured experts exchange and all the other posts related to the failed validation and returned validation status "VS_NEEDSNEWMETADATA" error in Visual Studio 2019 and nothing I found applies to me.

I have an SSIS project that has two SSIS packages in them. Both use the same ODBC connection. Both are querying smiliar views from the same database. These views aren't changing (aka: the columns in the view are, and have been, static).

One of the packages executes fine. The other gives me the VS_NEEDNEWMETADATA error.

I've tired everything suggested in other posts to no avail. I've even completely deleted the Data Flow task and recreated it.

This is not an issue with OLE DB, but I don't want to use OLE DB.

Is there anything else I can try?

I did see in another post that this was only  happening to them on VS2019 and not 2017. Does anyone have any info on that?
0
In SSIS, I am using project level paramaters to choose ODBC drivers based on if the package has been deployed to Development or Production.

However, the SCHEMA on each server is DIFFERENT. For example, in development the schema is "DVDTA" and in production it is "PRODDTA". So, when querying a table in dev, it'd look like this: SELECT * FROM DVDTA.Users, and in prod: SELECT * FROM PRODDTA.Users

It does not appear I can set the schema in the DSN itself, and it does not appear I can create a SCHEMA parameter in SSIS, and then do something like this in the "ODBC source" data flow: @[$Project::Schema].Users

So, in SSIS, how would I go about dynamically writing the query correctly based on which environment the package is deployed to?
0
hi,

For MS SQL SSIS, if I want to be a SSIS developer what should I learn and what language usually it use ?
0
New to SSIS... what do 'Build"  and "Re-Build" do?  Do you do a "build" before you "Deploy'?  Does the "build" do some short of a compile with the package, and some other items that you need to do before you "deploy"  the solution?
0
Hello Experts,

I have a task to import data from Oracle database, since VS 2017 SSIS works only with 32-bit ODAC and after installing it I was using Connection Manager(.NET provider\ODP.net, managed driver, also attached) in my SSIS for script task. When instantiating my ConnectionManager object, I get below error message.

In the below code, TESTDB is the ODP.NET connection manager, then I get this error "Could not create a managed connection manager". I was using Microsoft  System.Data.OracleClient instead of Oracle.ManagedDataAccess.Client. Because I had issue with the Oracle.ManagedDataAccess dll and I was getting run-time error.
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.OracleClient;
public void Main()
		{
            try
            {
OracleConnection oraConn = new OracleConnection();
   object rawOraConn = Dts.Connections["TESTDB"].AcquireConnection(Dts.Transaction);
                oraConn = (OracleConnection)rawOraConn;
}
Catch(Exception ex)
{
}

Open in new window


Please suggest, How can I fix this issue
0
Hello Experts.

I installed 32-bit Oracle12 ODAC and where can I find the Oracle.managedDataAccess dll which is 32-bit  and I checked under C:\App\client\MyUserName\product\12.2.0\client_1\odp.net\managed\x86 and I didn't find that dll and I found Oracle.managedDataAccess dll underC:\App\client\MyUserName\product\12.2.0\client_1\odp.net\managed\common. How can I know whether it's 32 or 64-bit.

Below is the issue I have and I followed the instructions installing 32-bit ODAC.
https://stackoverflow.com/questions/1067727/connecting-to-oracle-from-ssis-on-vista-64-bit


Thanks,
ASPDEV
0
Hi Experts

Could you explain how Ensemble InterSystems works and what is the purpose of this tool?

Is that an ETL tool like Pentaho f.e.?

Thanks in advance
0
Hello Experts,

I have a issue connecting to Oracle Database using ODP.NET (Oracle managed driver) in my SSIS pkg in VS 2017 under script task. I was using connection manager(no .config file for database connection string) Oracle ODP.NET Managed driver and no issues connecting to database but when I call the connection manager inside the script task and once the OracleConnection object instantiated(Oracle.ManagedDataAccess.Client, I added the reference dll)  and run the application below is the runtime error I get, interestingly it works with no issues when using Microsoft Oracle Client(System.Data.OracleClient, I added the reference dll).


 
at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor)
   at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments)
   at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
   at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams)
   at Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript()

Open in new window


Please suggest or hep what exactly I was doing wrong.

Thanks,
ASPDEV
0
How can i use a filename with spaces in it as a datasource? If I take the spaces out of the filename it works.
0
SQL SSMS was updated to v18, however, SSIS is not connecting. Web search stated if the versions do not match, SSIS will not connect to updated versions. The original SSMS is v14 and SSIS does connect (so I am regulated to use v14 SSIS). I have searched the internet for v18 SSIS with no luck and cannot find it to download.

Does anyone have a suggestion?
0
Hi,
I somehow cannot see my SSIS toolbox anymore
The icon exists, and the entry exists in the menu as shown below but when I click on either of them nothing happens.
I have tried restarting VS but still the same.
Any advice appreciated.
Thanks
ssis-toolbox.png
0
I currently have SSIS jobs set up that are being executed (via a .bat file) on the Task Scheduler. We want to move these to the SQL Server Agent. However, I'm getting errors.

Please note that these packages are executing just fine from the Task Scheduler.

The first error I received was: "Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B "Key not valid for use in specified state."

Again, didn't get this error via the task scheduler. I changed the SSIS Protection Level from "EncryptSensitiveWithUserKey" to "EncryptSensitiveWithPassword". That took care of that error, but when I tried to run the job in SQL Agent again, I received:

"Open Database Connectivity (ODBC) error occurred. state: '42S02'. Native Error Code: -204"

So, in the "Command Line" tab of the Step, I added /DECRYPT "password", but still get the same error.

Not sure what else to do, here.

Any help is appreciated. Thanks in advance.
0
Hi, I have 3 flat file sources. Now I want to count the number of records in each flat file and I want to write the counts to a flat file in the below output.

FileName  Count

can anyone suggest me step by step on how exactly I achieve this ?
Many Thanks
0
I'm working with SSIS and i am able to do add a date and time as an express to append to an attachment file name.  I searched everywhere but I can't seem to find a way to do the Date and the time to be in a 12 hour format with AM/PM.

So i would like the format to look like this     ExcelFileName_MM-DD_YYYY  2:45 PM    or    ExcelFileName_MM-DD_YYYY  10:45 AM
0
I have a package that, amongst other things, is performing a data flow task to create a flat file from a SQL source. That task is within a ForEach loop container.
Upstream from the Foreach container is a script task that converts a comma-delimited string parameter into an ArrayList, which is the basis of the ForEach container. The list contains 'lookback months' (e.g. "12,24,36" or "18,24,30", etc.)
WIthin the ForEach container is a script task that I want to use to dynamically change the output filename. The 'base' filename is something like "extract_^MONTHS^.txt" and I am replacing the "^MONTHS^" placeholder with the actual value (e.g. extract_24.txt).
Following the script task is the data flow task that actually creates and populates the file.

How, within the script task do I actually change the Filename of the flat file connection manager?

What I tried, is I have an expression for a variable named FullFileName which concatenates the FilePath variable and FileName and the ConnectionString property is set to that value, so at runtime, initially, it would be something like "C:\SSIS\Data\Output\" + "extract_^MONTHS^.txt".

The script task within the Foreach container sets the value to C:\SSIS\Data\Output\extract_24.txt but, apparently, at that time, changing the string value does not actually change the connection.

Is there a Dts command I can issue within the script task to actually set the path for the flat file connection before I execute the data flow task.…
0
New installation of MSSQL 2017 on a new Windows Server OS 2016 installation. During the installation Integration services was selected to install and it successfully installed. The Integration services are running. There are two integration services with this version of MSSQL.  I can connect to Integration Services using SSMS 17.9.
I expected to see the Integration Services database SSISDB in the list of databases when I connect to the Database Engine using SSMS 17.9. But it is not listed. Articles state that it should be listed. What am I missing? How do I see the database SSISDB in SSMS?
0
Hi,
I have built a dtsx package using VS 2008 on Windows Server 2008 R2 Standard SP1 and it works fine when I use Preview in one of the OLE DB Source components (see below)
okHowever, I have installed the package in a different environment, VS Pro 2017 on Windows 10 Desktop and I am getting an error when I click Preview, see below.
I have checked the Connection Manager  and it Tests fine, also the same Connection Manager is used in multiple components in the package and it works fine.
Any ideas? If you need any more info please  ask.
Thanks.
error
0
we have migrated our SSIS packages from 2014 to 2016 in SSDT 2017.  I have a data flow task with odedb source connecting to a sql server DB with the below query. the package is running fine with no errors but no data is gettting populated
but if i run this in management studio its giving me 150 rows. Strange thing is if i remove the paramter conditions, its workign fine in SSIS but then IF i hardcode the dates its not populating any data again

do i need to refresh parameters or variables since we did the migration ? can anyone suggest what is causing this issue ?

Many Thanks


select * from vPBTF_Daily_Cash
where Round(Amount,2) <> 0
and Currency = 'GBP'
and (USERDATE = ? or (CD = ? and USERDATE < ?))

The above query is using a datetime variable with expresson (DT_DATE)(DT_DBDATE)GETDATE(). This will basically give todays date . for example 17/10/2019
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.