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.

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
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
I'm using an ssis pkg to load data from Oracle 12.2.0.1.0 (we just upgraded) into Sql Server 2014.  It worked fine before upgrade, now I'm getting buffer and memory errors.  

Any suggestions?

Connection manager - Native OLE\DB Oracle provider for OLE DB

using a dataflow
0
Can someone give me a example on how to accomplish this task. Does robocopy work or file system task.
0
I have an SSIS solution which has grown over the past few months to the stage where I need to break it out into separate solutions.
A bit of background: The project is migrating data from one database to a new system. Data is extracted from the old database into flat text files which I then import to SQL Server using SSIS Workflows. The data is then transformed and output to Excel templates ready for importing to the new system.

All of this works fine. Now we are coming up to the cut off date for data from the old system. This will be the dataset that forms the Live data in the new system. There is a period of one month between the data cut off and the use of the new system, during which time changes will be made on the old system. So, we have a requirement to produce delta output which generates records that have been Inserted, Deleted, or Updated. All of this is fine and the scripts are ready.

However, I've run into serious issues with memory with having both the main data migration and delta workflows in the same project. Therefore, I want to move the Delta workflow to a new solution. The way I've gone about doing this is to set up a new solution in VS 2017 (15.9.16), and adding an existing package to the solution. When I do this, it loses the Connection to the database. So, I've tried creating the connection prior to adding the existing package, yet I get the same result.

I could go through every data flow task and update the connection, but this also requires …
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.