Hi There,
I have Task in SSIS to import the flat file to the table and break the table and insert and update the other tables in the same database.
I searched every where i couldnt find appropriate tutorial for this.
Please advice me how to achieve this task.
Thanks in Advance
Regards
Savera
Hi All,
Here i am going to ask some questions in SSIS pls clarify my doubts...
1. Can i include a package with in another package? like If i have 20 packages..i need to run all packages at a time ..for this can i create a package and can i place all those packages in to a single package?
2. If i have 700 packages then how to run those packages all at a time?
3.Can i develop a Workflow or package in SSIS with out using Data Flow Tasks? Like with the help of only control Flow Tasks can i create a package or workflow?
Thanks & regards,
Maruthi...
There are a few questions I have on the parameters you can use in a connection string. I didn't see a post with these already, and I think they may be helpful for those just starting to figure out how things work. I couldn't seem to find these defined in the msdn anywhere. What exactly do the following do:
"server=.\sqlexpress;" or "data source=.\sqlexpress;" - Is there a difference in using these, or are they completely interchangeable without any side effects? So far it seems I have been able to swap them for each other without anything happening however there may be something I don't see happening behind the scenes!
"Trusted_Connection=yes;" - Is this a workaround for getting SQL Server to work on vista's UAC or something? If not what does it do?
"Integrated Security=true;" - I know this has to be used with User Instance=true, but is there anywhere that says what it's actual function is?
"database=myDB;" - If I use this in a string it causes my program to crash. It also has a side effect of making me have to stop all sqlservr.exe processes and restart the service before my program will run again without it included in the string. Not sure why this is the case, however here is the string I try to use:
Code Snippetstring connStr="server=.\sqlexpress;AttachDbFilename="c:\sfsf\sads\myDB.mdf";database=myDB;Trusted_Connection=Yes;";
Now with using that string, the point of having both the "AttachDbFilename="c:\sfsf\sads\myDB.mdf";" and the "database=myDB;" options is that I read if you use those together it causes the SQL Server to first check and see if the database is already attached. If it is then it doesn't reattach the database, and just uses the current attached database. While this makes sense it doesn't seem to work for me since as I stated above anytime I use the database option it crashes the app. Any ideas? If that isn't how it is used what is the "database=" option for?
There may be more options I don't know about yet, but those are the ones I am not clear on. Hopefully one of you more experienced folks and shed some light on them!
Test Qn
View 5 Replies View RelatedDear Expertise
Please give me a solution to my general question.
1. How can we Impliment the report builder in client machene.
2. What is the diffrense between Report Server and Report Builder.
I'm evaluating SQL 2005 Everywhere Edition for use by our desktop application. I'm a traditional SQL Server developer and I rely heavily on stored-procedures to encapsulate basic data manipulations across multiple tables and inside multi-statement transactions.
I was excited to see an in-process version of SQL released and my thought was "this is great... now I can ditch the tediousness of individual OLEDB/.NET commands, and write batches of T-SQL and just focus on the data manipulations". But, alas, it seems I cannot. Why is SQL Everywhere Edition limited to executing a single SQL statement at a time?
For example, my application would like to update mutlipe rows in one table, delete multiple rows from another, and insert multiple rows into a third. I can do that with 3 T-SQL statements in a single small batch in a very readable way with full blown SQL Server. (and I can put that batch in a stored procedure and re-use it efficiently later.) If I contemplate how to do that with OLEDB and the single statement limitation of SQL Everywhere, it's a lot more code and a lot less appealing/maintainable. I want as much of my app to be using declarative code and as little as possible tied up in tedious OLEDB calls. Is this not possible with SQL Everywhere Edition?
Code Snippet
SELECT 3308 / 15104
The above division will result with: 0
Code Snippet
SELECT 3308 / (15104 * 1)
The above division will result with: 0
Code Snippet
SELECT 3308 / (15104 * 1.0)
The above division will result with: 0.219014830
MY MAIN GOAL is to produce a result of: 0.2
I was thinking of using ROUND, then with some combination of RIGHT. Your explanation and advice is greatly appreciated! Thanks again!
Please add some more substantial needed info in ALL the web pages of "SQL Server 2005 Compact Edition RC1, Release Candidate 1", like at:
http://support.microsoft.com/kb/920700
for:
1) Clarifying the confusing name changes of the Mobile Edition to Everywhere Edition then to CE,
2) Giving installion instructions of the RC1 for mobile devices
Thanks,
Alpoge
how to create a xml file as destination
View 4 Replies View RelatedIn my package i have to load data from multiple files one after the other into a table
View 5 Replies View Relatedhow to send a mail using script task
View 1 Replies View RelatedHow to retrieve multiple files using script task in a control flow
I am trying to use ssis (sql query or .net-script task)
to transfer data from one table to another.
I have difficulties to make the query:
i have one table tbl_games which fields are :
type ,startDateTime, EndDateTime, playerId.
I need to check every 1/2 hour according to the startDateTime,
How many times a person is playing and to show it in a new table
tbl_collectData like this:
Time playerId Games_0-30_seconds Games_30-50_seconds
8-8:30 A 3 5
8:30-9 B 2 10
9-:30 C 20 7
The length of the game is measured by the StartDateTime -EndDateTime,
and only games type # 2 is being collected.
The number 3 in the example is: 3 games that lasted between 0-30 seconds and were played between 8-8:30 by player A, and the game type is 2.
I tryed to start by doing something like this , but it is not a complete query:
INSERT INTO tbl_collectData
(Time, playerId, Games_0-30_seconds)
SELECT '12 / 26 / 2007 4 : 53 : 03 PM' AS Expr1, playerId, COUNT(DATEDIFF(ss, StartDateTime, EndDateTime)) AS Games_0-30_seconds
FROM tbl_games WHERE (Type = 2) AND (DATEDIFF(ss, StartDateTime, EndDateTime) < 31) AND (StartDateTime > '12 / 26 / 2007 4 : 53 : 03 PM') AND
(StartDateTime < '12 / 26 / 2007 5 : 23 : 03 PM')
GROUP BY playerId
I just don't know how to do it! other ways? ideas? Please help!!!!
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 Replies View RelatedHi,
We have 8 SSIS jobs which need to be made dependent on the arrival of a single file.
All the flows need to be triggered based on the presence of a file
Whats the way to do this
Thanks
Manish
Is there component in SSIS Package that I can use SQL Statement to update input dataset column based on a join query with a table?
update <input dataset> set column01 = -1
where column01 not in (select column from dbo.columnlist)
and column01 <> -1
Hi All,
I am trying to design a package that needs to compare two tables in two diiferent servers. Basically I need to insert records into one server by comparing existing records with second table in other server. Is ther any way I can do it with out using Linked server?. Both tables have same structure.
Hope any one will reply soon.
Thanks,
lmp
I am using the SSIS Import & Export Wizard to make packages importing data from Access 2003 into SQL Server2005.
In the Access database I am using, there are about 100 tables and 4 select queries.
When I ran the SSIS wizard, it found the first query fine, and the package was made successfully.
Now, I need to make a package to import data using one of the other select queries (it has two subqueries.)
When I run the wizard, the "Select Source Tables and Views" dialog opens and shows all 100 tables, plus only two of the queries, not all 4. Two are missing, including the one I need to do the import.
I cannot see why these two queries would be excluded. One is a compound query (the one I need) the other is not. Is there a limit to the number of objects the dialog can show?
If anybody has had this problem please let me know. For now it's a real show stopper.
Many thanks
Mike Thomas
Can 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.
On another thread a poster suggested that you can experience performance gains in Ssis by not using stored procedures.
I asked if I could have some Microsoft articles that articulated this position, but received no response.
Does anyone have links to Microsoft supported articles articulating when/if it is appropriate from Ssis to Not exec Stored Procedures for data manipulation or data collection?
I have been searching for a GOOD example of using SSIS to query MOSS 2007 list data. I just want to return the data from a list in Sharepoint and use that data in SSIS. It should be as simple as defining a data source to connect to a specific list and return the rows like a SQL query.
Can anyone provide a good example of doing this in SSIS (and by the way, I'm not looking for how to write a C# program) - I want to be able to do this directly from SSIS.
Any help would be greatly appreciated.
We have an SSIS package which runs regularly throughout the day, on 15 minute intervals. This package is moving data between two SQL Server instances, performing some simple identity mapping along the way. The primary source table is large, and we want to move only those records which have changed since the last time the package ran, so we use VersionDatestamps in the table, and pick up the dataset to be transferred by using the pacakge execution time, and the (previously recorded by the package) last-run time.
The problem we are having, is that the initial dataset picked up by SSIS is often missing records. The missing records are clearly within the time window that the package queried for, though they are near the boundary (within a minute, of the boundary, but as much as 30 seconds away) and typically all have an identical version datestamp to each other, within a single execution of the package.
At first, we thought this was an issue with date precision, but we've both truncated the dates, and even expanded the time window, and we still experience the same phenomenon.
The stored procedure which is responsible for updating the records in question, runs as a previous step to the SSIS package, within a single SQL Server Agent Job.
Has anyone experienced anything similar, or have some recommendation on how to track the source of this down?
Hello All,
I am trying to run the below query in SSIS, However it does not work, but when I try to run the same query in Oracle client it works fine. Here is the following query:
select 'AAA-'||OWNER AS SOURCE,
table_name,
column_name,
SUBSTR(data_type,1,50) DATA_TYPE ,
SUBSTR(decode(data_type,'NUMBER', DATA_PRECISION, DATA_LENGTH),1,20) DATA_LENGTH
from all_tab_cols
where owner='XXX'
ORDER BY TABLE_NAME, COLUMN_ID
Here ARE the following errorS I get when running from SSIS:
[ORA_AAA_XXX [147]] Error: There was an error with output column "SOURCE" (612) on output "OLE DB Source Output" (157). The column status returned was: "The value could not be converted because of a potential loss of data.".
[ORA_AAA_XXX [147]] Error: The "output column "SOURCE" (612)" failed because error code 0xC0209072 occurred, and the error row disposition on "output column "SOURCE" (612)" specifies failure on error. An error occurred on the specified object of the specified component.
Any help?
Regards,
Raju
Although I am able to connect to a SQL 2005 server (both through the SQL Management Studio and through the connection wizard in Visual Studio 2005), I am unable to parse a query if I use a connection set up as ADO.NET. When I use OLE-DB, it works properly. When I attempt to execute a SQL task (comprised of a single SELECT statement), I get "Failed to acquire connection 'servernamegoeshere'. Connection may not be configured correctly or you may not have the right permissions on this connection." How can this be if I'm able to parse data from this connection through other means? This happens both locally and remotely.
View 17 Replies View RelatedHi,
I am using SSIS in SQL Server 2005 and want to have a query like this in my data flow task
Select a.*
from abc as a
inner join (Select max(b.id) as ID from xyz as b inner join pqr as c on b.id = c.id and b.id > ?) as t1
on t1.id = a.id
SSIS fails to detect the parameter (?) for the inner query and gives message.
"
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.", so assuming this is your problem, then you can workaround.
"
The idea is to parameterize the inner query ,,,
(so if the above query doesnt make sense ignore it )
Is there a way to use the results of a query as parameters of a WHERE statement in Oracle?
I have a list in a SQL table that I would like to use as criteria for a query through Oracle? Basically I have two data sources; one Oracle and the other SQL, I'm currently querying the SQL one, then using a Merge Join object in SSIS to combine the data. The SQL query has roughly 2000 rows which is fine, however the Oracle one had several million... I've tried to limit the oracle one as much as I can however its still returning far too much data...
If anyone has any suggestions on how to do this I'd greatly appreciate it. I've looked into Linked servers, however this isn't an option with my set up due to account restictions on the Oracle server. Thanks and let me know if you require any other details
The database that I try to access in my query in the package gets restored in the same time when my package runs. If those two things clashs I get the error message. How do I make it retry in 5 minutes for example?
Thanks
Marina
Hi All
I am trying to run a query against our 'Active Directory' and I keep getting errors. I have done the following:
1. Set up Linked Server (using the OLE DB For Directory Services) and also set the login information:
EXEC sp_addlinkedsrvlogin 'ADSI', False, 'SQL_Usr','DDEMOJillSmith', 'PassWord'
2. Created a connection in SSIS
3. In My SSIS Package I have created a dataflow select the AD connection 'ADSI'.
4. Tried to run the following query:
SELECT samaccountname, adspath, sapUsername, mail, info, costCenter, pager, mobile, facsimileTelephoneNumber, telephoneNumber, company,
department, title, sn, givenName, name, employeeNumber FROM
''LDAP://OU=Main_Users, DC=DemoAD,DC=com'' WHERE objectCategory = ''Person'' AND objectClass= ''user''
And it fails - I can get this working in SQL 2000 DTS by using an Open Query statement!
The Error message I get when trying to test the query is:
Error at Export From AD [OLE DB Source [1]]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x00000000.
Exception from HRESULT: 0xC0202009 (Microsoft.SqlServer.DTSPipelineWrap)
I have event tried the working OPEN QUERY in SSMS and I get the following:
Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "SELECT samaccountname, adspath, sapUsername, mail, info, costCenter, pager, mobile, facsimileTelephoneNumber, telephoneNumber, company,
department, title, sn, givenName, name, employeeNumber FROM
'LDAP://OU=DEMO_Users, DC=DEMO,DC=com' WHERE objectCategory = 'Person' AND objectClass= 'user'" for execution against OLE DB provider "ADsDSOObject" for linked server "ADSI".
Am I missing something?
Thanks Gopher
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 am migrating DTS packages to SSIS (recreating all the logic).
I have a Data Driven Query task in DTS with
Source query - select x,y from table1 (from database db1)
Binding - table2 which contains columns which match table1 x,y (fron database db2)
Transformation - maping from source table1 x,y to Binding table2 x,y
Queries - type update update table2 set x=? where y=?
I know that there is no similar task in SSIS,can someone tell me how to replicate this in SSIS
Thanks in Adv
Hi,
we've created a ata Flow task to execute several aggregations. Our Task access database using OLE DB source and selects data out of our staging tables (we've analyzed the query using MS SQL Management Studio which didn't showed any issues). But when we try to run our dataflow task using SSIS (debug mode and DTEXEC from command line) we experince that tasks seem to stop during processing.
Unfortunately we didn't found a way to see long logfile entries which explain the issue to us.
We do use several aggregation tasks divided in 4 sequences. Unfortunately we just see one logical processor out of 4 logical processors working. It is a Windows 2003 SP2 machine with SQL 2005 SP2 on top of it.
Is there any solution to use all processors to one package for parallel execution?
So basically we experience two issues:
- SSIS seems to stop somewhere in thre middle
- SSIS just uses one processor instaed of all four
your advice is appreciated
In the tutorial Creating a Basic Package Using a Wizard > Lesson 1: Creating the Basic Package >
says to use the following sql statement on the query page:
SELECT * FROM [Customers$] WHERE NumberCarsOwned > 0
When I paste the query in I get the message :
This SQL statement is not a query.
Does anyone have any suggestions? The input and output are set up correctly and I have the sample excel file Customers.xls.
I am new to all this, is there some setting I need to change for the tutorial to work or..?
FYI I have installed Sql 2005, Sp1.
Hi, I'm new to this forum, so please bare with me.
I've created a mining model, i've tweaked it etc and i'm now happy with the results its producing. I'm now looking to automate the processing and exporting of the results of the model i've done this simply by creating an SSIS package with two tasks, one task being to process the model the other task is a Data Mining Query task.
This package works fine in visual studio and when i deploy it to the server.
The problem i'm having is when i then try to execute the package from a job, after a bit of investigating i have tracked it down to the Encryption of "sensitive" properties. By default the encryption is based on UserKey which is why the package works for me when i execute it from VS or even the server, but when the job trys to execute the package running under the sql agent account it fails.
Looking at the security options i have for packages, i can either DontSaveSensitive, EncryptSensitiveWithUserKey or EncryptSensitiveWithPassword plus a few others.
DontSaveSenstive is clearly not an option as this just creates an unusable package.
EncrptSensitiveWithUserKey doesn't seem to be an option as the job runs under the SQL Agent account (also i'm thinking that the UserKey that the encryption is based on also incorporates other factors related to my profile that i can't impersonate? i might be wrong though)
EncryptSensitveWithPassword seems to be an option except that i can't get this to work either, there doesn't seem to be anyware in the job step to give it the password information.
Its frustrating me now because i've fallen at the very last hurdle, if anyone else has experienced this problem and knows how to resolve it that would great.
Thanks
Bob.