Understanding SSIS Solution Directory Structure
Apr 8, 2008
Say I do File -> New -> Project and make a new project called RedApple under C:. For simplicy, I chose root, but I would designate location for all my SSIS projects. I also check the box "Create directory for solution".
I see that this creates C:RedApple
Under this, I have another RedApple directory with a .sln and .suo files.
In this 2dn RedApple directory, I have .database, .dtproj, and .dtproj.user files.
I deleted the default package1, so I don't have any .dtsx files yet.
As far as the above files are concerned (.sln, .suo, .database, .dtproj, .dtproj.user), I understand these are some kind of system or meta data files and I shouldn't be messing around with them. And that's as far as I need to concern myself on these files.
As I add packages to my project, I see that it is creating .dtsx files under the 2nd RedApple directory. So, I'm thinking these are the package files, one for each package I see in my solution explorer, correct?
MY QUESTION:
Now, I am looking at another SSIS directory tree for a project that I created before I started to pay attention to all this. In this other directory tree, I see that there is also a bin directory, which I do not yet have in C:RedAppleRedApple. In this bin directory I see more .dtsx files, the same ones as I see in the directory above it, but also other .dtsx files that have the names of other packages that I onoce created and deleted along the development of this particular project. What are these .dtsx files?
View 3 Replies
ADVERTISEMENT
Jan 18, 2007
I need to be able to import a directory file list into a sql table.
i.e. table contains fields for filename, date etc...
is this possible?
thanks?
View 9 Replies
View Related
Jan 23, 2008
Hi,
As stated in the subject, I need to import a complete directory structure, from a root folder that I specify into a SQL Server database. To add further complexity, I also need to see what files exist in each directory.
At the end of the day what I am trying to achieve is to populate a table that will essentially have two columns, the filename and the directory that the file resides in. I am running SQL Server 2005.
Has anybody ever done anything like this before using T-SQL?
Thanks in advance.
View 5 Replies
View Related
Feb 19, 2007
Hi,
I am adding a file to a filegroup on a remote system, and i don't know the direcotry structure of that machine, how can i provide the path for FILENAME param. :eek:
ALTER DATABASE TESTDB ADD FILE (NAME = N'TESTFILE', FILENAME = N'physicalfilepath.ndf') TO FILEGROUP TESTFILEGROUP
Warm Regards,
Mushq
View 1 Replies
View Related
Sep 26, 2005
Hi,I've just installed SQL server and then IIS and SQLCE tools.I created a virtual directory and was trying to update the NTFSpermissions from SQLServer Connectivity management when i got thefollowing error - Access Control List (structure) invalid.Has anyone come across this and if so, what did u do to fix it.ThanksLyn
View 2 Replies
View Related
Jun 1, 2007
I am a new learner of SQL, Please can someone give me a very brief overview of what SSIS and what it can do?
thanks
View 2 Replies
View Related
Apr 26, 2007
I have a flat file component in a package that is reading a comma separated file.
It expects two columns of int type.
I have an on error event for the entire package. The On error event has one script task which just logs to the windows event log.
if I corrupt the input values in the file, for example instead of a number I put a character
When I debug the package, I see that the script task in the on error event blinks 7 times and I get 7 errors in the windows event log.
Now If I put another script task next to the flat file source and attach it to the flat file source as a error output (with row redirect).
I get only one error message.
Why are the two behaviours different? Both are error handlers one is at the component level the other is at the package level.
So why is it that the package level is invoked 7 times if the input is corrupt but the component level is invoked only once.
regards,
Abhishek.
View 12 Replies
View Related
May 15, 2006
I am having trouble understanding how the SSIS data pump determines when to decide "The final commit for the data insertion has started/ended". On some tasks the rows are inserted one at a time every few milliseconds (shown by a default getdate() in a datetime column). In others the final commit occurs as I would expect at the end of the data pump task.
There are times i want the data pump task to commit all records that are succesful, row by row and there are times I want an all or none situation. Can somebody explain why this behaviour occurs and how i can control which commit option I want the data pump tasks to use?
View 1 Replies
View Related
Feb 23, 2006
I built a packaage in SSIS with the import/export utility. It created a Package.dtsx and Package1.dtsx. Both of these files seem to be XML files. I want to understand how these files work. For example, in the package I built I had about 80 tables exporting and importing data. Some of them I want to allow the identiy insert and delete the rows first. Others I want to append the data. How can I find the code or settings that does this? Or where can I find the options on the gui interface to change these settings. When I search the code I can't even find a some of the tables that are being transferred.
View 20 Replies
View Related
Jun 1, 2006
Hi,
When I create a new Project, I am not getting a solution name in the Solution Explorer. It is starting from the Project Name only.
So when I create a Package Deployment Utility, I am not getting a DTSInstall.exe file in the deployment folder.
Pls guide me.
Thanks & Regards,
Prakash Srinivasan
View 3 Replies
View Related
Jun 20, 2007
I have been asked to write a script to hit about 50 SQLServer databases to check the individual status of those dbs. and i need help to write this script.
what i need to do is.
1. hit sqlserver#1 and hit all the databases from that box and see whether the sqlserver dbs are
ok or are suspect. if they are anything else other then OK then I send an email via sendfile.
2. hit sqlserver#2 and continue doing this.
is this possible? do anyone of you know how i can do this programatically via an ssis job or have a script that I can use?
I have both SQL2k and SQL2k5
thanks
View 4 Replies
View Related
Mar 12, 2007
Hi All,
A small part of our project needs to be customizable for each of our clients. Each of our clients have the concept of a "JOB"; The details of which are held in their own database. When our product is installed it must gather Job information from their database and match the appropriate fields to our generic Jobs table. Once this has been done, we can generate a stored procedure that will periodically poll the clients database for new/updated jobs and transfer the data to our database.
What we really need is to come up with something that has a generic UI that will allow us to connect to any data source, enumerate the databases and their tables so it will allow someone to perform the operations above.
My question is can SSIS assist us in achieving this goal??
Any help on this would be greatly appreciated.
Paul.
View 5 Replies
View Related
Jan 12, 2006
I am trying to open the solution and it is giving me error
"The application for the project \.... est.dtproj is not installed
Make sure application for the project type(.dtproj) is installed."
I tried to open any SSIS solution and it is giving me same error.
Anybody has any ideas how to resolve this problem?
View 7 Replies
View Related
Mar 20, 2007
Hi
I have created a package which imports a unit of measure table. I now want to populate more rows in the same table from itself. In Access VBA the below code extract does the first part of the job I need to do. Can anyone point me in the right direction to convert this into an efficient SSIS solution.
SQLstr =€?SELECT [UOM conv].[Short Item No], [UOM conv].UOM, Count([UOM conv].[UOM 2]) AS [CountOfUOM 2] €œ & _
€œFROM [UOM conv] €œ & _
€œGROUP BY [UOM conv].[Short Item No], [UOM conv].UOM €œ & _
€œHAVING (((Count([UOM conv].[UOM 2]))>1)); €œ
Set RsMoreThanOne = db.OpenRecordset(SQLstr)
With RsMoreThanOne
While Not .EOF
SQLstr = "SELECT [UOM conv].* FROM [UOM conv] WHERE ((([UOM conv].[Short Item No])=" & ![Short item No] & ") AND (([UOM conv].UOM)='" & !UOM & "'));"
Set RsSql = db.OpenRecordset(SQLstr, dbOpenSnapshot)
RsSql.MoveLast
x = RsSql.RecordCount
Set rs = db.OpenRecordset("UOM Conv")
rs.Index = "PrimaryKey"
RsSql.MoveFirst
Item = RsSql![Short item No]
For y = 1 To x
Um1(y) = RsSql![UOM]
Um2(y) = RsSql![uom 2]
Fct(y) = RsSql!factor
RsSql.MoveNext
Next y
For y = 1 To x - 1
For k = 1+y To x
rs.Seek "=", Item, Um2(y), Um2(k)
If rs.NoMatch Then
rs.AddNew
rs![Short item No] = Item
rs!UOM = Um2(y)
rs![uom 2] = Um2(k)
rs!factor = Fct(k) / Fct(y)
rs!calculated = True
rs.Update
Else
If rs!calculated = True Then
rs.Edit
rs!factor = Fct(k) / Fct(y)
rs.Update
End If
End If
rs.Seek "=", Item, Um2(k), Um2(y)
If rs.NoMatch Then
rs.AddNew
rs![Short item No] = Item
rs!UOM = Um2(k)
rs![uom 2] = Um2(y)
rs!factor = Fct(y) / Fct(k)
rs!calculated = True
rs.Update
Else
If rs!calculated = True Then
rs.Edit
rs!factor = Fct(y) / Fct(k)
rs.Update
End If
End If
Next k
Next y
RsSql.Close
.MoveNext
rs.Close
Wend
.Close
End With
db.Close
Regards
ADG
View 3 Replies
View Related
Jun 20, 2007
I need to load Excel files, which have 200+ columns. There is a check for every column, some are simple type and range checking, some checking involve a little bit complex logic that depends on other columns. The legacy problem use a VB code check each column one by one, row by row, and color the problematic cells.
- What's the best practice to do it?
- How to generate a human readable error log in SSIS? the output error log is so hard to read.
Thanks,
View 1 Replies
View Related
Mar 3, 2006
Hi,
We need to migrate data from Sqlserver2000 database to Sqlserver2005. The SQLserver2000 DB was poorly Normalized and the DB was redesigned inSqlserver 2005. Both the databases are operational.Currently we have around 90 tables that need to migrate the data from the OLD DB to NEW DB, all the table mappings between the old schema and new schema have been successfully established.
Each Table is treated on its own merit and applying its own business rules which allows the data to be transformed to the new tables.The client wants us to build a SSIS solution that needs to performs the following
a. Import all Look Tables.
b. Import all master Tables ( One time import of some tables data and other tables may need to import every 4 hrs)
c. Import all Transaction tables( One time import and other tables may need to import every 4 hrs of data)
My Question is how to apporoach this scenario of pulling one time lookup, master and transaction tables and scheduling 4 hrs data importing process, how to build a SSIS solution for this type of scenario.
Thanks
Jayadev
View 4 Replies
View Related
Jan 9, 2008
How would you do the following in SSIS?
SELECT a.TestID,
a.TestCode
FROM TableA a
WHERE UPPER(RTRIM(a.TestCode)) IN SELECT (SELECT UPPER(RTRIM(b.TestCode)) FROM TableB b)
Of course the above query is missing a few things but with ETL the where clause UPPER(RTRIM does not appear to be something that has an object or property that I can use in the Lookup.
Please correct and educate me.
View 4 Replies
View Related
Jun 2, 2015
I would like to have my SSIS tasks to be transacted, but due to infrastructure issues in our network, cannot utilize DTC, because our SQL Boxes are situated where the DTC cannot communicate. I know we can use execute SQL task with Bein Trans, but I dont think dataflow and all will not be in scope of this and cannot be rollback in case of any error in the underlying tasks.
View 2 Replies
View Related
Jul 7, 2006
Hi
I have been working with DTC packages together with analysis service, and are
now begining on SSIS.
But I have some demands that's been giving me headache before, so I hope
there's a solution in the SSIS for the following.
My DTC packages was build to be executed i 5 steps/Packages
1. Transfeer data from my Oracle 9.2.0.4 to SQL 2000
2. Transform data
3. Quality check
4. Process dimension
5. Process cubes
BUT in 2005, I want a little more flexibilty. I want to execute smaller
packages, let's say, only my "general ledger".
This way I will be able to update some of my important cubes quicker. And
not wait until all data
is transferred.
So what I have done until now is that I have created SSIS packages per module. I
have a General_ledger.dtsx and a Sales.dtsx. Which I can execute separately.
I also have a MasterFlow.dtsx which contain an "Execute package Task"
for each package.
I can run the MasterFlow and it will run through all packages once every night.
Each package contains
1. Truncate package tabels
2. Transfer package
tabels
3. Execute dimensions
data
4. Execute cubes
5. Process
Dimensions
6. Process Cubes
Is this the best way?
I'm not satisfied
myself because,
when I run MasterFlow. I would like to execute all
"step 1" in all packages, before "step 2" and so forth.
Then I would be sure that all data is OK when all Cubes are processed, if I
have a virtual cube that contains data from 2 packages.
How would you design this kind of setup?
Thanx in adv.
Best Regards
Bjarne R Mikkelsen
A-TEX A/S
View 3 Replies
View Related
Dec 28, 2007
Hi All,
I have created a solution which contains only 2 packages say Package1.dtsx and Pakage2.dtsx. I want to create a deployment utility to deploy onto other developers machince. I changed the project properties "CreateDeploymentUtility" to TRUE. When I do the build it is not creating the files in "Deployment" folder. It is saying Rebuild All Failed but the error is not showing.
For more information the 2 packages have 4 indirect configurations from environment variables which are storing the actual config file path.
Am I missing something here?
Thanks.
Venkat.
View 8 Replies
View Related
May 12, 2006
I'm trying to query against AD to grab some data. I've done this setup and got it to work at my location but can't get it working at one of my customers.
Per information I've found via this site I did the following:
Task: from SS2005, select data elements from Active Directory and populate in ODS (using an
SS2005 Package in SSIS)
I created a linked server on the MS2005
EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5', 'ADSDSOObject', 'adsdatasource'
I then created the following View (in the Master DB):
CREATE VIEW viewADContacts
AS
SELECT [NAME],MAIL
FROM OPENQUERY( ADSI,
'SELECT NAME, MAIL
FROM ''LDAP://#######/ DC=####,DC=###''
')
The View created w/ no errors
When I execute
select * from viewADContacts
I get this error:
Cannot fetch a row from OLE DB provider "ADSDSOObject" for linked server "ADSI".
Any thoughts on this error? Again, I did the exact same thing at my office (against my local AD) and it worked fine.
Thanks in advance
Tom
View 3 Replies
View Related
Aug 18, 2006
Hi There
I installed Sql Server 2005 and SSIS to the C: drive of a server.
However i am going to install 3 more instances on this server and i want to install SSIS on the RAID drive.
So i uninstalled SSIS, then i installed another Sql Server instance on the D drive.
Now when i try to install SSIS, when i go to advanced and browse for installation path it is greyed out and it says that the path has already been bound to the 90DTS folder of the first instance i installed on the C: drive, so basically i dont know how i can install SSIS to another drive ???
Please help.
Thanx
View 6 Replies
View Related
Jan 12, 2008
Hi everyone!
I have a configuration file, I want to place it in somewhere of SSIS package to extract configuration information. But i dont know where is root directory. can you help me?
Thanks you very much
View 3 Replies
View Related
May 22, 2008
I hope this topic is appropriate for this forum. If not, I pre-apologize.
I have an SSIS package that takes data from active directory and dumps it to a table.
Here is the query I am using:
SELECT displayName, Mail, Title, physicalDeliveryOfficeName, description, telephoneNumber, department
From 'LDAP://ourserver'
WHERE objectClass='user' AND objectCategory='Person'
I am dumping it into a table on SQL Server where the columns are all of data type: NTEXT.
All columns come across GREAT except the column DESCRIPTION. We keep a piece of information in that column that is important and without it the SSIS is worthless. What is coming across in that column is the following:
System.Object[]
No values are coming across. It should be character data up to 10 in length.
Anyone see this or know of something that I can do to force the "real" data to come through?
Thank you in advance for any consideration you give.
James
jamestow@yahoo.com
View 16 Replies
View Related
May 8, 2007
In SSIS, I was to:
take files from c:directory1
copy them to cirectory2 with a different name (concatenated date on the end)
delete them from c:directory1
Should I be using the Script Task for this? I am wondering if I should be using the File System Task which copies directories or files, but I don't know if I could rename the files during the copy.
Also, I want the directory names to be in the configuration file. I am not sure how to do this. If I set up a flat file source connection, I need to specify a file name and I just want the directory names. How do I get them in the config file and how to I read them into my script from the config file?
Linda
View 25 Replies
View Related
Jan 7, 2008
I'd like to know more about data warehouse. I've known some for SSIS working with SSAS. But it's existing data warehouse. I just transfer data to data warehouse using bussiness rules. I have no idea about data warehouse, how to design data warehouse from transactional database, how to generate the table structure. Any tips or books for that? Thank you very much.
View 5 Replies
View Related
Apr 22, 2007
I have all the config files for my various packages in a folder call configs. It seems that I cannot add folders to the SSIS solution. I can add config files, but they get copied into the root folder, which breaks the directory structure that I have.
Am I missing something? Is there a recommended to manage config files, and other external files used by the SSIS package, in SourceSafe and in the SSIS solution? Thank you.
View 1 Replies
View Related
Feb 9, 2007
I have a solution with a couple of ssis projects in it. Everytime I open the solution, Visual Studio creates an extra .database file for the project's existing xxx.database file. The solution is under VSS control and VS2005 checks out the project and shows the file as a newly added file.
What causes this and how can I prevent this from occurring?
The files contain this:
<Database xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:dwd="http://schemas.microsoft.com/DataWarehouse/Designer/1.0" dwd:design-time-name="d3ce9653-3ac5-4ee5-85c3-7d60b2e5f109" xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<ID>Publish</ID>
<Name>Publish</Name>
<CreatedTimestamp>0001-01-01T00:00:00Z</CreatedTimestamp>
<LastSchemaUpdate>0001-01-01T00:00:00Z</LastSchemaUpdate>
<LastProcessed>0001-01-01T00:00:00Z</LastProcessed>
<State>Unprocessed</State>
<LastUpdate>0001-01-01T00:00:00Z</LastUpdate>
<DataSourceImpersonationInfo>
<ImpersonationMode>Default</ImpersonationMode>
<ImpersonationInfoSecurity>Unchanged</ImpersonationInfoSecurity>
</DataSourceImpersonationInfo>
</Database>
There are 2 of us working on the solution and the other fellow does not see this behavior.
The files do not show up in the VS2005 solution explorer. If they are user specific as I suspect (impersonation info?), then they should not be added to the .dtproj project file.
Thanks!
View 3 Replies
View Related
May 20, 2008
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
View 4 Replies
View Related
May 29, 2015
How can I copy data like firstname, lastname, email from Active Directory into a SQL Server table?
View 4 Replies
View Related
Oct 30, 2007
I'm attempting to find a reliable way to determine the file system folder in which the current package is located. (Please note that for this purpose I have no need to support SQL Server deployment, so the fact that there is no current folder in that context is not in scope for this post. )
Anyway, I've added the following code to a Script task in one of my packages:
Code BlockMsgBox(Environment.CurrentDirectory)
When I run the package from DTEXEC I get this the current working folder from which I executed the DTEXEC command line. When I run the package from DTEXECUI I get "C:Projects2005TFSRootSSISCurrentBranchProject", which is the actual folder where the package is located. When I run the package from within Visual Studio (right click, execute package) I get "C:Projects2005TFSRootDocumentationSSIS" which is what is throwing me off. I can't tell where this path is coming from; it is a valid path on my machine, but why it's showing up as the current folder is the mystery. There's no code there and never has been, although I do check in and check out documentation stored in this folder.
I've looked around online but have had no luck in finding any information aboutreliably determining the location of an executing SSIS package. The best I have found is this thread (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2202954&SiteID=1) which doesn't address the issue I'm seeing.
In short, I'm looking for a consistently reliable mechanism that will allow an SSIS package to be aware of its location at run time, regardless of the mechanism (DTEXEC, DTEXECUI, BIDS, etc.) through which the package is executed. My hopes aren't all that high, but I figured if I was going to get an answer anywhere it would be here.
Has anyone else had success with this?
Thanks in advance!
View 3 Replies
View Related
Feb 17, 2015
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 Related
Apr 6, 2007
Has anyone used this successfully from an OLEDB source component, or even from the Execute SQL Task? I've seen some examples of using a script component, but nothing that uses it through a connection manager.
View 6 Replies
View Related
