I am trying to convert "Derived Columns" Expressions to SQL statements to be able to add and replace column values and names.
I am having some issues in finding the correct help to understand nested formulas in SSIS.
Formula i have in SSIS is,
I have one main folder, which contains 40+ folders. I have created an SSIS package that creates subfolders in 22 of the 40 folders. I want to use a Foreach Loop in my package to loop through only the 22 folders for my script to return the names and d... more
I am working on SSIS 2008. My task is to get the stats from a table in OLE BD and save it in a flat file. I am using two fields in a table saved in OLE DB the fields are in NVARCHAR data type. Amount and Currency are the two fields. I
wanted the SU... more
I have read the various documents on the Merge Transformation and they all say the data has to be sorted, but none of them talk about which columns and how to decide what columns to sort. If, for example, my merge join criteria is on multiple columns... more
I have the SSIS package, which will load the excel file into Database. I have created Excel Source task to map the excel column name to Database table column name and its working fine.
In rare case, We are receiving the excel file column name with s... more
I am looping through a folder with hundreds of .csv files that are formatted in a standardized way with 5 years of daily financial data in each. I am attempting to put a single column (the day's opening value) from each into their respective column n... more
I have an issue occurring due to a very specific situation when trying to import a csv file into an SQL Server table using a SSIS package. The data in the csv file can contain double quotes and commas. As such, data with commas are double quoted an... more
tl;dr: How to I convert "NULL" in a flat file to a NULL in SSDT/SSIS?
In SSMS run this code:
/* Run once
SET NOCOUNT ON
CREATE TABLE #source (DT DATETIME, DT2 DATETIME2)
CREATE TABLE #target (DT DATETIME, DT2 DATETIME2)
INSERT INTO #source
VALUES ... more
I am making a set of Talend jobs where I am going to query same set of tables differently depending on the need of the jobs. These tables are huge. Will it be good if before running the jobs I query all the tables and take all the data from the DB an... more
I have two data sources - Document and Customer and I want to join them, creating rows that that information both from documents and customer, having the customer_code as key.
Finally, I get only 58 out of 30k results that I expected.
Where is the... more
The scenario is to connect to multiple POS terminals and fetch data and save to a table in a staging Db. For instance, there are 10 POS terminals, each terminal has different IP and Database name. However the table where the select query is... more
I want to store the date from the getdate()-1 in SSIS variables.
Something like when we run the sql query
Output - 2017-11-10
I tried the below query in the expression.
I have SSIS package that grabs excel file and load it to sql table .i get the following error when i run it. I have tried to make run on 64 bit to false. That did not work
i also have installed 64 bit access driver engin . That did not help either.... more
I have a Flat file which contains 2 columns . One is date time in this format
2017-11-06T11:16:08 AM GMT
and Another Column Total value stored in this format
Destination is OLEDB (ms sql),
Column 1 is Datetime and column 2 is Int ... more
I am trying to convert GETDATE() to a string in SSIS in the format of dd-MMM-yy (ex;, 03-NOV-17) as I am using Oracle as a data source.
I've developed the expression below, which will return 2017-11-03. This is close, but I need the "11" to read "NO... more
I use EPPlus.dll library for generating excel files on the fly for attachment and generate mailer in Script Task of SSIS package.
When there is new requirement for change in mailer comes, I do the change in Script Task on my local machine and send th... more
I'm relatively new to Azure and am having trouble finding what options are out there for connecting to an existing SQL database to push data into it.
The situation is that we have an external client who needs to connect to our Azure SQL database to ... more
I'm loading a pipe delimited flat file into a staging table. During the load process an SSIS script component performs some operations on a row. It may set a flag in one field based on values in another field, add a prefix to certain columns, or appl... more
I'm having performance issues with ETLing update-able tables from our OLTP environment to Redshift. Our basic workflow is the typical OLTP->S3->Redshift data flow. Let's say I want to ETL a table like this
create table source_data (
id int primary k... more
I am using the copy command of redshift to load json file into redshift but I receive the error
**ERROR: The total size of the json object exceeds the max limit of 4194304 bytes
error: The total size of the json object exceeds the ma... more
I am trying to do a SSIS package deployment to SQL server 2008. I generated the manifest file but double clicking it didn't open the deployment wizard.
I have tried to execute the dtutil command from the command prompt but getting an error Argument ... more
Working with SQL server 2014 ans SSIS, I am having the following issue in a data flow:
Data Source: There was an error "The value could not be converted
because of a potential loss of data.".
This is the query:
I have a variable in ssis, with query
SELECT OBS_TYPE_ID, OBS_TYPE_NAME, OBS_UNIT_ID, IS_LEAF, PATH, LEVEL0_NAME, LEVEL1_NAME, LEVEL2_NAME, LEVEL3_NAME, LEVEL4_NAME,
LEVEL5_NAME, LEVEL6_NAME, LEVEL7_NAME, LEVEL8_NAME, LEV... more
I am writing a SSIS package and trying to extract the data from one database to another. I have created a Execute SQL task and using the following statement INSERT INTO dbo.getParties EXEC dbo.getParties to perform the operation. This works when the ... more
I am trying to convert a data from a flat file to a SQL date. I am using variables to separate the Month, Day, and Year and added necessary "0" when needed then concatenate them back together. the job works fine except when I run into a date that is ... more
I can't quite find the answer to this. How can I evaluate an expression that contains arithmetic in SSIS. Specifically within a variable's expression field or in the Execute SQL task -> SQL statement as expression.
I have a user defined variable 'Wi... more
I am using sql server 2012 and in it i have sql server data tools which is using visual studio 2010 so, i have one table which contains name only and i would like to copy this table in destination table so i have taken oledb source and oledb destinat... more
There is a text file that having records more than 10,000 lines.
Each line starts with a specific number like 1,2,3,4 and 5. Each number indicates whether it's a header, main data, file detail, footer etc.
So, we need to find out the line number t... more
I have a very simple package of SQL Data Flow Task that executes a query and deletes the result.
Is there a way to make my package fails when there's no result in the query? I try adding Execute SQL Task to get the result Count, but I'm not sure how... more
I try new project SSIS and i have an error
Exception deserializing the package "The package failed to load due to error 0xC0011008 "Error loading from XML. No further detailed error information can be specified for this problem because no Events ... more
I have an SSIS package that has to execute a SQL Task for each file in a directory, where the volume of files is very large. This is causing a problem with logging, because every time the SQL task executes it produces the following output:
I have an input column "MonthName" which has values in the following string format 22-MAY-2017 02:29:33.00. I would like to convert this into Datetime data type in the destination table.
For that the following conversion needs to be done
I am trying to import a group of Flat Files to SQL Server using SSIS.
Everything is going fine, and the package execute normally, but while checking to imported data, it looks like the last column contains extra information from other rows.
After c... more