I am working on task where I need to load data into DWH from OLTP. Load needs to b incremental load. For this I am planning to use CDC, to track changes and only load data which has been new inserts/updates/deletes. But to load data to destination, there are couple of joins, which I need to do. Is there a way in CDC, where I can use query as source?
View 0 RepliesI am a complete newbie to SSIS. I can create a simple package to transfer data between SQL instances and thats about it.
I have tableA (source data) and tableB (Destination data). TableA has 4 column and tableB has 5. I want to transfer all of the columns from tableA into TableB, but the 5th column in tableB needs to be populated with the ServerInstance name of the server TableA sits on. Do I need to have multiple data sources to achieve this? I have tried but no matter how I set it up, the Column in the destination is set to ignore.
Passing a date parameter in a stored procedure through OLEDB Source (Data flow task). We have a multipurpose stored proc in which one of the many expected parameter is a date. It keeps giving me the following error:Description: "Operand type clash: int is incompatible with date"- although there is no integer.
One way to fix it would be change the data type from date to datetime in proc but I can't do that since it is multi-purpose proc. I didn't find anything good on the net.
I have a SSIS package that simply moves data from a SQL database A to another SQL database B. I have update (increased) the size of a nvarchar column, on both A and B.I am wondering if there is a way to "refresh" somehow the SSIS package so I don't have to rebuild and redeploy it.The error I get now is a truncation error: "Text was truncated or one or more characters had no match in the target code page".
View 2 Replies View RelatedCan anyone help me with this ?
I am trying to extract data from oracle 9i server and
pushing it onto the SQL Server 2005 using Data Flow Task.
Details for OLE DB Source are :
OLE DB Source Editor Details:
OLE DB Connection Manager - Oracle Source
Data Access Mode - SQL Command
SQL Command Text -
SELECT EMPNO, EMPNAME, JOB, HIREDATE
FROM EMP_DETAILS
WHERE (HIREDATE > TRUNC(SYSDATE) - 1)
SSIS parses this query succesfully but when i build the query it shows query
SELECT EMPNO, EMPNAME, JOB, HIREDATE
FROM EMP_DETAILS
WHERE (HIREDATE > TRUNC("SYSDATE") - 1)
Please note :- SYSDATE IN " "
This query returns no Result Set.
Hi,
Quick question on how SSIS handles queries from Data Source in a Data Flow. I noticed that when I run a particular query from Query Analyzer it takes forever. But, when I run the same query in SSIS data source in a data flow. The query results are immediate.
The query plan is already cached in SQL.
Is this just something which I am seeing incorrect or is there some bit of optimization in there in SSIS. As per my understanding SSIS does not optimize the source query.
Thanks,
Gaurav
Hi,
I'm trying to use query parameters with an Oracle OLEDB Source in a data
flow task and I'm having problems.
I've tried formatting the query each of the following ways...
--
select
frq_code,
frq_name,
update_frq,
uptime_frq
from frequency_bcs
where update_frq > ?
and update_frq <= ?
--
Parameters cannot be extracted from the SQL command. The provider might not
help to parse parameter information from the command. In that case, use the
"SQL command from variable" access mode, in which the entire SQL command is
stored in a variable.
Additional information
---> Provider cannot derive parameter information and SetParameterInfo has
not been called. (Microsoft OLE DB Provider for Oracle).
Hi,
Urgent Help required..........
Can anyone explain me steps how to parameterized query to send oracle.
If you know any other control which help to do this rather than OLEDB source.
Please let me know.
THanks
I need to see inside a SSIS 2012 project a new SSIS installed component, but in the SSDT 2010 I cannot see the SSIS Data Flow Items tab for adding data source/data destination respect to the choose toolbox items pane.
View 4 Replies View Related
Hi
I am having a huge xml file with nested section.
i also have a xsd file for that xml.
i have a destination table where the data from the xml should be loaded into.
i am using the xml source transformation. But o get all the data i need to use multiple merje joins to get the data in a single row which i can insert into the destination.i was not quiet convinced with using so many joins.
so i tried using the script source transformation where i am using xml objects to get the node and dynamically construction the data row. and the output is then inserted into the destination.
on comparing the two approach the one using the script source is working much faster than the xml source transformation.
i wanted to know is there any limitaion using the script source to parse through xml files.
also i would like to know any other better way of getting the data from xml source without using the joins.
Hari
The environment is Server 2008 32-bit, SQL 2012 SP2 Standard 32-bit (11.00.5058), and Visual Studio 10 SP1 (10.0.40219.1).
I have a query that begins with MERGE <tablename> USING (SELECT blah, blah, blah While in SSMS, I can parse the query with no issues. I can execute the query and see the results I expect. But when I put the same query into an Execute SQL Task in SSIS, it won't parse. It gives me one of those very informative messages that I so love - Query failed to parse. Incorrect syntax near the keyword "MERGE".
I don't know if the 32-bit/64-bit thing has any bearing or not, but I have taken the exact same project folder, copied it to a 64-bit box, and it works fine in both SSMS and SSIS. That one is running Server 2012 R2, SQL 2012 SP2 Standard (11.00.5343), and Visual Studio 10 SP1 (10.0.40219.1).
I have a requirement of migrating DTS package which is done in Sql Server 2000 to SSIS 2012.
I started with one package having data driven query task and done with source for which i chose OLE DB Source and given the required select query in ssis 2012
I'm stuck now and i'm unable to choose the relevant tools in ssis 2012 for binding, transformation,queries and lookup tabs used in dts 2000 for this DDQT.
Hi,
I'm finding that the standard components often just don't quite meet my
needs, but would only need some fairly minor changes to save me and my team a lot of
work (and produce more elegant solutions). So I was just wondering whether the source code was available for the standard components that come with SSIS, or if there is anyway to extend their functionality?
Or do you just have to start form scratch?
Thanks,
Lawrie
I have an odd one. I have a SQL job that doesn't have a schedule and is being run each morning. It is a legacy system and I am trying to document the data flow process and I am having a hard time tracking down where/what is starting the job. I see which user executed the job:
SELECT message FROM sysjobhistory WHERE job_id = 'jobid' AND run_date > 'yesterday'
Which is useful, but I want to know what is starting the job.
I'm trying to find the command to open up an odbc conection inside sql2005 express. I only have ues of an odbc connector, we're conection to remedy. We will eventually be using stored procedures to extract the data we need from remedy and doing additional data crunching. I'm a foxpro programmer so once I get the correct syntax for making the odbc connector I shold be ok. Also I need a really good advanced book on sql2005. The type of book that would have my odbc answer. I've spent all morning trying to find this information and was unable to.
Thanks in advance
Daniel Buchanan.
If this was the wrong forum to post this on, please move this question to the correct one. I need this answer soon.
We have a job running the SSIS Package. I am working with that SSIS Package. The SSIS package has a script task. In the script task they used the variable of source path. I want to change the value of that variable (Need to modify the source path).
I modified the variable value in that package and in the config file but getting error message
Exception has been thrown by the target of an invocation.
We need to replicate data from a live database to a reporting database nightly.
There is data which is only needed for the reporting function - namely the selection criteria for reports which are to be run nightly or monthly.
Is it possible to have extra tables in the target database, holding the selection criteria?
If not, we'll have the reports running heterogeneous queries between the target database and a separate database with the selection criteria in it.
I am not able to see the Data Source in Visual studio 2012. I have tried several things like 'Alt + shift + D' which is meant to bring up the datasource tab but it does not. I have also looked in view->Other windows but its not displayed there.
View 5 Replies View RelatedI need to use the same SQL source code in more than one stored proc.
Is there a way to -include mysql.sql or something like that ?
Is InsertSnippet the only way to do this ?
,CASE WHEN P.PROD_CTG = 'Core' AND LEFT(C.SALESFORCE_LEAD_ID,3) = '00Q' THEN S.MSL_QUANTITY ELSE 0 END AS CoreTotal
,CASE WHEN P.PROD_CTG = 'Core' AND LEFT(C.SALESFORCE_LEAD_ID,3) = '00Q' AND LEFT(S.OPPS_RECORD_TP_NM,5)!='Upsel' THEN S.MSL_QUANTITY ELSE 0 END AS CoreSales
,CASE WHEN P.PROD_CTG = 'Core' AND LEFT(C.SALESFORCE_LEAD_ID,3) = '00Q' AND LEFT(S.OPPS_RECORD_TP_NM,5)='Upsel' THEN S.MSL_QUANTITY ELSE 0 END AS CoreUpsell
,CASE WHEN P.PROD_CTG != 'Core' AND LEFT(C.SALESFORCE_LEAD_ID,3) = '00Q' THEN S.MSL_QUANTITY ELSE 0 END AS NonCoreTotal
Hi,
I want to use output of SSIS package in Windows application, any suggestions?
For this I have created SSIS package and loaded data into DataReader destination, but now how to use this data in my application.
Thanks in advance.
Regards,
Omkar.
Hello,
I have SSIS solution which has multiple packages and one Main package which starts all others.
I also have 2 Data Sources which I use on each package.
I need to do my testing on different Data Sources. When I try to change Data Source from one database to another - SSIS is getting confused and fails on the connections or starting to insert to the previous database.
Could you please explain the steps for doing it. What is the problem here?
Can I use Windows Attentications in data source or use special account?
Also some confusion here with Config file. Should I create Config file just from Main package or from each package. For now I am working with a Source code, but later I would need to change connections string before running the executable. If I change the string in my Main package - would it be understandable in all others?
Please give me details here.
Thanks.
V.
Hi All,
I want to use an excel sheet as a source and pick up the data starting from 4th row.
can anybody suggest me a solution for this.
Cheers,
Prash
Dear Friends,
I need to import data from several excel files. How can I configure excel source object to dinamically import each file?
The name of the file will be in a parameter of ssis package and this name change frequently, and ach time the filename change I dont want to change the configuration on the excel source?
What you sugest?
Shoul I use a script component as source?!
Regards!
I am creating an OLE DB Data Source in an SSIS Data Flow - it is calling a SQL Server 2005 stored procedure, which accepts 3 parameters and returns a result set, but I get the following error:
"There is a data source column with no name. All data source columns must have a name."
I have have tested the output of this proc a million times, and every column has a name! I am also able to call a different proc in the same way, without error:
EXEC pProc @DT = '20060928', @Id = 1015, @Debug = 0
Any ideas what could possibly be causing this error - assuming all of my columns DO have names??
Thanks : )
I created a Stored Proc to compute all the data I need to export to a CSV file.
I use the provider MS OLE DB Provider for SQL Server.
It's a very simple package with a single Data Flow Task.
This flow task is using an OLE DB Source to call a simple SQL Command : Exec MyStoredProc.
There are no params.
This Stored Proc is using table variables to compute the data.
It takes about 10 seconds to return anything.
The problem is that the mapping doesn't work with the OLE DB Source.
There are no fields at all shown in the mapping screen.
I tried to replace the Stored Proc by a version which only returns fields and no data.
Then the mapping would work just fine.
The package is then compiling and working fine.
But everytime I put back the real stored proc, even without changing the SQL Command, the SSIS execution breaks at execution.
It keeps saying :
"Error: 0xC0202005 at Data Flow Task, OLE DB Source [477]: Column "RecordType" cannot be found at the datasource."
My guess is that SSIS doesn't wait the 10 secs and thinks the Stored Proc is not returning anything.
What can I do to make this work ?
Thanks,
Vincent
Hi,
I am using Excel datasource and Excel destination in a simple SSIS package which i created. I have got two queries regarding this
How does excel datasouce/destination accept .xlsx files, since i observed that they accept only .xsl file
Does excel destination ascept .xlsm files, because i have some macros enabled in destination
Thanks in advance for advice
Warm Regards,
gchanduu.
Our team has the following problem:
I create a SSIS project in VS and add it to Team Source Control. When the project and all it's files exist on a machine and I want to open the project from Source Control, everithing works fine, but when another developer, who doesn't have the files on his machine, wants to open the project from Source Control he is required to check out the project file.
I've investigated the problem and found out that each project has a database file (with .database extension) and project file contains a reference to it. Once the user opens from Team Source Control a project that isn't presented on a target machine, the VS creates a new database file on a local machine and insists to change the reference in the project file.
Does anyone have a workaround for the problem?
Thanks
Hi all,
Can a SSIS package treat a file from a table ( VARBINARY(MAX) or BLOB) as a source for migration ?
Thanks in advance,
DBAnalyst
Hi,
I am trying to load a XML document using SSIS into relational tables and pulling my hair out.
In it's simpliest form I have:
<Batch>
<POSBasket>
<Header>
<SomeDetail/>
</Header>
</POSBasket>
<POSBasket>
<Header>
<SomeDetail/>
</Header>
</POSBasket>
</Batch>
Now, when you have a parent child situation, you get a column in the output called xxx_Id, in my case, POSBasket_Id. This allows you to relate your header back to your root and SomeDetail back to header etc etc.
Loading all of this in parrallel does not work because there is nothing unique once the load has happened more than once. i.e. You will have repeating POSBasket_Id's as well as Header_Id's.
I ended up loading POSBasket into a table with an identity column. Once done, I would load the header details and do a lookup (eek) on the POSBasket table by using POSbasket_Id "where isComplete = 0" (A flag to tell me current import).
Great, this works find with 1 item in my document. Add a second (2 POSBaskets) and it randomly works. (???) Add more and it hardley works. The look ups are failing with "no match found".
On using Data Views, I see that each XML source creates different POSBasket_Id's. Some matching, some not. Also seem to randomly match.
My question is why? Why would there be the ID if they differ when loading them from the same document? They not even unique across sources.
Bug or "by design"?
Anyone got a better method for doing this?
Cheers,
Crispin
Greetings!!
I have a MsAccess db containing a table called Employees which i am transforming to a staging table in Sql server 2005. Everything is working fine. I am using Foreach File enumerator and uploading the files one by one.However I now plan to validate the schema of MsAccess before uploading it. For eg: My employee table in msaccess is as follows :
Code Snippet
Employees
empId int,
empName varchar(60),
empAge int
Since the files come from different vendor, while looping, i want to perform a check if the empid or empAge are not of type long or string etc. If they are of type smallint,i have no problem.
However if they are larger datatypes than the the ones kept in Sql server, then the file needs to be logged in the db with the reason and moved to the error folder. In short, if the datatypes in access tables are smaller than those in Sqlserver, allow it, otherwise reject it. THe schema of Sqlserver table is same as of that of Employees in msaccess.
I want to compare the schema of the incoming access tbl fields with my desired schema and all mdb's having data types that are higher or incompatible with the desired schema should be moved to the error.
How do I do it.
Thanks ,
Ron
The report having SSIS package as source works fine on my local 32 bit windows xp machine from Report Manager. But same doesn't work from 64bit windows 2003 machine... fails with below error... what could be an issue?
An error has occurred during report processing.
Cannot create a connection to data source 'DataSource1'.
is not a valid Win32 application. (Exception from HRESULT: 0x800700C1)
The same works fine when previewed from visual studio on 64 bit machine.
Thanks
Mahesh
Hi,
I have a problem where I want to import a 1.6 GB XML file with SSIS into a SQL Server database. My hunch is that SSIS is not very good with handling such large amount of XML data. My test shows that SSIS tries to read all of the file into memory.
Does anyone know if there is any solution of solving this memory problem. My problem is that I want to take this source XML file import it into a database, make some transformations on it (eliminate duplicates etc) then produce a NEW XML file as output in a different XSD-format.
Is really SSIS the right tool for this operation?
The source XML file also have mixed content on Complex Types which seems to be a problem for SSIS as well.
Best regs,
//Patrick
I have a package that runs every hour. It runs fine most of the time but once in a while
I get this not very helpful error message "the component 'xyz' is unable to process data" on DataReader Source component.
when i try to run it again on the same data it works fine. So i dont think it has to do with data.
Is there a way i can get more information on this error?