I have a database surrogate key that increments so rapidly (+5000 every 30 mins). I need my SSIS package to reset this database surrogate key to avoid reaching an upper limit value for that field.
How can I do that using SSIS package?
thanks,
Aref
A quick Google revelas that you can use DBCC CHECKIDENT to do this
http://www.mssqlcity.com/FAQ/Devel/reset_identity_column.htm
You can issue the command using the Execute SQL Task.
-Jamie
Hi All,
I have a table A. I need output subset of a table A to a flat file using query, like:
select A.* from A inner join B on... ..... inner join C......where left(A.id, 3) = B.sid.... AND B.num between 100 and 200).
How can I do this in SSIS? Which data flow item I may need?
Thanks
Well there are several ways you could approach this.
One possibility is to use a "Data Reader Source" item. In the Advanced Editor of this item, under the Component Properties tab, you may specify a SqlCommand.
Another possible way is to use the "OLE DB Source" item. Double click the item to view the "OLE DB Source editor". Change the data access mode to "SQL command". Some controls will appear that can assist you with entering and validating your SQL statement.
|||SSISy Boy wrote:
Well there are several ways you could approach this.
One possibility is to use a "Data Reader Source" item. In the Advanced Editor of this item, under the Component Properties tab, you may specify a SqlCommand.
Another possible way is to use the "OLE DB Source" item. Double click the item to view the "OLE DB Source editor". Change the data access mode to "SQL command". Some controls will appear that can assist you with entering and validating your SQL statement.
Thanks, it works!
Hi,
We just moved from SQL 2000 to SQL 2005 Ent. To use SSIS service, I need install this package on client side. But when I install the 2005 on my PC(Win XP Prof), I did not find any place I can include it. Anyone has similar experience or know how to do it?
Thanks in advance,
Jie
Hi Jwei,
Take That CD 1 and choose Workstation components, Books Online and development tools.
That'll install the following components:
.BOL
.Native client
.BIDS
.SQLXML4
.MSXML6
.OWCL11 (Office Windows Components)
.SQL Server Backward-Compatibility Files
HTH
|||Stop! Make sure you understand the license changes for SSIS compared to DTS. Put simply you can no longer redistribute SSIS as a client component, like you could with DTS. SSIS is a server component, so machines must be licensed with server license, not just a CAL.
|||you're right...|||Thanks a lot for taking time to respond to my post.
I had problem with the license change, just could not believe it.
"no longer redistribute SSIS as a client component, like you could with DTS" is a key to me.
|||
Then, how can I install only SSIS Client on my work station?
Please help
Thanks
|||Run setup. Choose the Integration Services and Workstation components options.|||Then, how can I install only SSIS Client on my work station?
SSIS client components are installed when you install Workstation components - that gives you BIDS (with ability to design and debug SSIS projects and packages) and SSMS (with ability to connect to SSIS service and monitor package execution).
To run packages in production you need SSIS server components, which are installed when you install Integration Services. That requires server license.
|||Thanks a lot for the solution
Hi,
We just moved from SQL 2000 to SQL 2005 Ent. To use SSIS service, I need install this package on client side. But when I install the 2005 on my PC(Win XP Prof), I did not find any place I can include it. Anyone has similar experience or know how to do it?
Thanks in advance,
Jie
Hi Jwei,
Take That CD 1 and choose Workstation components, Books Online and development tools.
That'll install the following components:
.BOL
.Native client
.BIDS
.SQLXML4
.MSXML6
.OWCL11 (Office Windows Components)
.SQL Server Backward-Compatibility Files
HTH
|||Stop! Make sure you understand the license changes for SSIS compared to DTS. Put simply you can no longer redistribute SSIS as a client component, like you could with DTS. SSIS is a server component, so machines must be licensed with server license, not just a CAL.
|||you're right...|||Thanks a lot for taking time to respond to my post.
I had problem with the license change, just could not believe it.
"no longer redistribute SSIS as a client component, like you could with DTS" is a key to me.
|||
Then, how can I install only SSIS Client on my work station?
Please help
Thanks
|||Run setup. Choose the Integration Services and Workstation components options.|||Then, how can I install only SSIS Client on my work station?
SSIS client components are installed when you install Workstation components - that gives you BIDS (with ability to design and debug SSIS projects and packages) and SSMS (with ability to connect to SSIS service and monitor package execution).
To run packages in production you need SSIS server components, which are installed when you install Integration Services. That requires server license.
|||Thanks a lot for the solution
Hi:
I am a beginner at SSIS subjects, i have a data stored at DataReader Destination and i want continue working whit the data. Somebody can help me?
Regards. deniscuba
What exactly do you want to do with the data?
Why have you used a datareader destination?
If you really do want to use the data made available from the datareader destination then perhaps this article will help: http://msdn2.microsoft.com/zh-cn/library/ms135917.aspx
-Jamie
|||DataReader Destination
(http://msdn2.microsoft.com/en-us/library/ms140291(SQL.90).aspx)
Loading the Output of a Package
(http://msdn2.microsoft.com/en-us/library/ms135917.aspx)
Defining Datasets for Package Data from SQL Server Integration Services
(http://msdn2.microsoft.com/en-us/library/ms159215.aspx)
Normally you would use DtsClient (see Loading the Output of a Package above). You can also write your own consumer if you really wanted to, for example I have written a managed provider, so we can chain packages together, package 1 terminates in a DataReader Destination, package 2 starts with a DataReader Source that is pointed at the package 1. Not something you would normally do, but it suits our requirements.
Hello,
I am new to SSIS.I am trying to write a simple package that saves data into some tables from elsewhere in the database, extracts it into flat files and then FTPs them to another file system.
I have a control flow with three tasks. [1] Execute SQL (Executes a stored proc to fill the tables with data) [2] A data flow to extract the data into 3 flat files [3] An FTP task to move the data to the other system.
In my data flow I have three parallel streams each with an OLE-DB source and a Flat file destination so that three flat s files get produced in parallel.
The problem I have is that I cant get the FTP task to handle the files. I can get the flat files produced fine and I write them to the c: drive. But how do I program the FTP transform to pick up the three files and dispatch them? I am guessing that I need to edit the FTP task, choose the file transfer tab and edit the local parameters section to tell it to pick up the three files........but how?
As mentioned I am new to SSIS so simple+clear answers appreciated.Thanks in advance
MGale1
This is a completely reasonable request. What I would recommend is to put each file you create in a ForEach Loop Container. Each time you loop to create a new file, inside the loop you can use the FTP Task to send the one file. You can dynamically set the file you wish to send by setting an expression on the Connection Manager that holds the file's name. I know I didn't specify exactly how to each step but you can see a more in depth about how to use SSIS expressions to set the package to be dynamic here: http://www.jumpstarttv.com/Media.aspx?vid=34.
I know you mentioned that you wanted to create the file inside the same data flow. Unfortunately, the files will have to be FTP individually to the destination. If you were to receive files from a FTP site, the task accepts wild cards (give me all the *.txt files from the ftp server). When you send files though, it's a different story. SSIS is going to force you to use a File Connection Manager to specify which file you wish to send. You cannot send multiple files at one time other than doing it in a loop as I mentioned above. At least, I'm not sure of a way to do it :).
For a slew of SSIS how-to starter videos, you can see this link: http://www.jumpstarttv.com/Channel.aspx?cat=c871236d-8554-42e3-8683-4d422356c0bd
|||From what you've written, it sounds like you could use three FTP tasks, one for each file.
What exactly do you mean? By script/scheduling or simply looking for file system for any DLL or whatever?
|||Have a look at the service SQL Server Integration Services, if it is present you have SSIS installed. YOu can check that by open the SCM or type in the "net start" on the commandline on the server.HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
|||Thanks. I checked SQL Server Integration Services. It is disabled. I just started. Thanks.
