Need Help creating a SSIS Package (Newbie)

Following is what I would like to do, so I can keep updating my central SQL Server database with latest updates from the field. I like to use SSIS 2005 to create a package that could do this. Any help to get me started would be appreciated. I need some help soon, pls give me something to get started. APpreciate it. Thanks.

Open connection and read client location table on the local SQL Server database called PODO
For each location id in the table do the following:
Store locationid/clientid in a variable called CLLOC_ID
Construct file name with mdb extension and store in a variable MDB_FILE
Establish connection to the data import folder
Search for that MDB_FILE in the folder on the file system
If there is a file where match = true then do this:
1) Open the access database
2) Read and import the data from the customer experience table
3) Write that data to the SQL Server tables where location = CLLOC_ID
4) Exit process
IF there is no match, exit process
Keep looping until all the client ids/loc ids are read from the SQL Server client location table.

MA

SSIS won't "search" for files as such. You have to tell it where the files are. If you have a collection of .mdb files in a folder then you could loop over them using the ForEach loop.

Import data from Access can be done in a data-flow

There are a number of ways of discovering if the incoming data has a matchinng PK in the destination (which I think is what you are trying to do). Have a look at the LOOKUP component.

-Jamie

|||

Jamie:

Thanks for the advice. I would like to loop thru a SQL Server table and build file names for the mdb files , and then use those file names to match with what i have in the folder. For each file i construct, i would like to establish a connection to that file, and import data. How would i implement this functionality?

MA

|||

Here's how: http://blogs.conchango.com/jamiethomson/archive/2005/05/30/1489.aspx

-Jamie

|||

Hi Jamie:

I went ahead and added the ForEach Loop container, and would like to see if that is the right starting point for what I am trying to achieve. Can you give me like a step by step, on how to do this, since I am having a real tough time figuring out how to do things like build a filename from the data retrieved from the SQL execute task. Also then how do i pass that file name further down the process, and how do i create something that would take that built filename and compare it with the file names found in the actual folder that i am connecting to on my local hard drive. I have setup two connections using the connection manager, one is for the SQL Server, while the other one is right now pointing to one dummy access file in the folder. I would need to somehow make the connection manager dynamically look for filenames, and once a match is found, import data. The import data part is pretty straight forward, but i havent yet gotten there. The bigger issue is that i need to build a file name list on the fly by reading thru a sql server table, and then comparing the filenames with what i have in the folder on the local drive. If a match exisits, then i would like to continue with a data import process. Since I am extremely new to SSIS, and I am not a DBA/Database developer, i am more of an application developer on the front end, can you please give me a quick step by step directions. This will help me get started, and if i can figure out things like how to pass output from one sql statement to another task, and how to do comparisons within SSIS, and then how to dynamically read thru files with those variables, this will help in the future, as I will be building more of these type or ETL processes with much more advanced setup. I appreciate your time, and so far I really appreciate your help. Thanks and I look forward to your reply, and I know I am asking too much quesitons, but this is a real project, and I am up against a deadline. Thank you.

MA

sql

Need Execute SQL component to fail package if zero records

I have a SSIS package that has several Execute SQL Components. One of the first components reurns a Full Result Set of IDs based on a stored procedure call. The stored procedure can return multiple rows. I store the results to an ADO recordset (object variable) to be used later. I want the component to fail, and the package if the return of the stored procedure is zero records. What is the best way to do this? I had a raise error statement if @.@.rowcount was zero but this did not fail the component. Any other suggestions?A way could be to have an extra execute sql task at the begginig with a Select count(*) from...; put the value into a variable and then use an expression in the precedence constraint to continue only if the variable value is greater than 0.|||I have thought about that as a work around. But it seems to me that I should be able to throw an error via RAISERROR or some other method in the stored procedure and have it result in the execute sql statement in the dts package to error as well.|||I don't see another way of doing using a single Execute SQL task. To be honest I don't see anything wrong on implementing pre execution logic in yiour package as far a performande does not suffer too much. Perhaps, you may want to write your own solution using an script task.|||I guess that is the path I will go. I was just figuring there may be an easier way since the Single Result Set fails if nothing is returned, I thought the Full Result Set may have been able to function in the same way. Thanks for the input.

Need Execute SQL component to fail package if zero records

I have a SSIS package that has several Execute SQL Components. One of the first components reurns a Full Result Set of IDs based on a stored procedure call. The stored procedure can return multiple rows. I store the results to an ADO recordset (object variable) to be used later. I want the component to fail, and the package if the return of the stored procedure is zero records. What is the best way to do this? I had a raise error statement if @.@.rowcount was zero but this did not fail the component. Any other suggestions?A way could be to have an extra execute sql task at the begginig with a Select count(*) from...; put the value into a variable and then use an expression in the precedence constraint to continue only if the variable value is greater than 0.|||I have thought about that as a work around. But it seems to me that I should be able to throw an error via RAISERROR or some other method in the stored procedure and have it result in the execute sql statement in the dts package to error as well.|||I don't see another way of doing using a single Execute SQL task. To be honest I don't see anything wrong on implementing pre execution logic in yiour package as far a performande does not suffer too much. Perhaps, you may want to write your own solution using an script task.|||I guess that is the path I will go. I was just figuring there may be an easier way since the Single Result Set fails if nothing is returned, I thought the Full Result Set may have been able to function in the same way. Thanks for the input.

Need dump 200 columns to a flat file

All,

I need to dump a table with more than 200 columns to a flat file. I tired in SSIS with the flat file destination, but it only allows 84 columns. What should I do if I want to dump all the columns to a text or cvs file.

Thanks in advance

Try the Export Data Wizard in SSMS.

Right-click on the database-->Tasks-->Export Data...

-Jamie

|||

Hi, Jamie,

I have to make it as part of my SSIS packages. Is there a way to do it within SSIS?

Thanks

|||

Yeah absolutely. Why not take the package constructed by the wizard and either:

1) Build your additional requirements into that package or

2) Copy and paste the objects from the wizard-created package into the package that you are trying to build.

-Jamie

|||Can you elaborate how the FF destination does not let you have more than 84 columns?

Need Assistance: Trying to run jobs and getting nowhere/SQLServerAgent

I've gone through hundreds of posts here and on the Net. I'm creating an ssis package to access my db on my desktop. I then save a copy as "Test" on the msdb database on our sql 2005 box.

Try to create a job and I'm getting nowhere. My SQL Server Agent account is a member of local Administrator group and a member of the domain.

Now what I noticed is if I run the dtsexec from the command prompt and use the command line string then it works. Just seems like I'm not able to run anything from the SQL Server>>Jobs interface.

Please Help :)

What errors do you get? Have you seen http://support.microsoft.com/kb/918760?|||

I have it working with the set password but I'd like not to do this in case other developers create packages and then forget. I'd like to use the below method but how do you do this --> "SQL Server Agent run the job as the account that created the package or as an account that has the required permissions.
"

Method 1: Use a SQL Server Agent proxy account

Create a SQL Server Agent proxy account. This proxy account must use a credential that lets SQL Server Agent run the job as the account that created the package or as an account that has the required permissions.

This method works to decrypt secrets and satisfies the key requirements by user. However, this method may have limited success because the SSIS package user keys involve the current user and the current computer. Therefore, if you move the package to another computer, this method may still fail, even if the job step uses the correct proxy account.

|||Books online have topic Creating SQL Server Agent Proxies under Administering the Database Engine > Automating Administrative Tasks (SQL Server Agent) > Implementing SQL Server Agent Security >

Need Assistance: Trying to run jobs and getting nowhere/SQLServerAgent

I've gone through hundreds of posts here and on the Net. I'm creating an ssis package to access my db on my desktop. I then save a copy as "Test" on the msdb database on our sql 2005 box.

Try to create a job and I'm getting nowhere. My SQL Server Agent account is a member of local Administrator group and a member of the domain.

Now what I noticed is if I run the dtsexec from the command prompt and use the command line string then it works. Just seems like I'm not able to run anything from the SQL Server>>Jobs interface.

Please Help :)

What errors do you get? Have you seen http://support.microsoft.com/kb/918760?|||

I have it working with the set password but I'd like not to do this in case other developers create packages and then forget. I'd like to use the below method but how do you do this --> "SQL Server Agent run the job as the account that created the package or as an account that has the required permissions.
"

Method 1: Use a SQL Server Agent proxy account

Create a SQL Server Agent proxy account. This proxy account must use a credential that lets SQL Server Agent run the job as the account that created the package or as an account that has the required permissions.

This method works to decrypt secrets and satisfies the key requirements by user. However, this method may have limited success because the SSIS package user keys involve the current user and the current computer. Therefore, if you move the package to another computer, this method may still fail, even if the job step uses the correct proxy account.

|||Books online have topicCreating SQL Server Agent Proxies under Administering the Database Engine > Automating Administrative Tasks (SQL Server Agent) > Implementing SQL Server Agent Security >

Need advise on SSIS?

I am very new in SSIS. I have the Report Server set up with two dozen of the OLAP reports in it. The reports are connected to the OLAP database on a seperate computer running on Sql 2005 server and the data source for the OLAP DB is pointed to the warehouse database on another computer running on Sql 2000 server. The warehouse server updates daily from a number of "Production" Sql servers running on Sql 6.5 and Sql 7.0 with the legacy front-end data entry application. I know the update from the "Production" Sql 6.5/7.0 servers to the warehouse server complete around 9am in the morning everyday. How do I set up a package in SSIS to schedule the update of the OLAP database say 9:30am so that end-user can have the latest data to their report. The OLAP database has one fact table, half a dozen of cubes and ten dimensions. I know it is not an easy task to set up the package in SSIS. Please provide sample and step on how to do this. Thanks. One last question how to prevent/warn users from opening the report when SSIS updating the OLAP database?

Wow! that is 'whole project' question.

I recommend you to read about the REAL project, which offers a end-to-end BI implementation using SQL Server 2005 (including SSIS)

http://www.microsoft.com/technet/prodtechnol/sql/2005/projreal.mspx#EZGAC

http://www.microsoft.com/sql/solutions/bi/projectreal.mspx

There are a few webcast to watch as well; just search on this forum to get the links to it.

Need advice on approach

I am new to DTS/SSIS and would like some input on an approach to solve a problem.

I have a solution using SQL Server 2005 and another legacy solution running on Access. Status records are written to a table in SQL Server and as they are written, I need to write a record to a table in Access. This needs to be as real-time as possible.

I thought about writting a managed stored procedure in C# so I could also access MS Access, but someone pointed me to DTS.

Records are constantly written to the status table and need to remain in there even after they are processed.

Can you recommend an approach or an article to read?

Thanks,

Steve

This could be done with SSIS with a little work but I think if your are talking about a very small amount of data that needs to be written to access each time, then I think the SP idea is better or something in general from the SQL engine to Access rather than involving SSIS. So, you may want to repost in one of the more general SQL forums.

Now if you have SSIS already involved in the pitcure some where, then the SSIS idea might still be worth investigating. For example if the part of your story "Status records are written to a table in SQL Server " was itself being done by SSIS, then enhancing the SSIS scope could be a good approach.

good luck

Need a list of Error Code

Hi,

When we configure a transformation to redirect the error record, SSIS gives 2 columns: 'error code' and 'error column'. In one article, I have seen how to get the error description using the 'error code'.

My questions are,

Where I can get list of all possible error codes? 'error column' gives the id of the column. How to get the name of the 'error column'?

Thanks.

Basically you need to know the metadata of the normal output, not that easy, but simon has wrapped this up in a Tx.

http://sqljunkies.com/WebLog/simons/archive/2005/12/15/SSIS_Enhanced_Error_Component.aspx

Some related info -

http://msdn2.microsoft.com/en-us/library/ms345163.aspx

The SSIS error codes are listed in BOL. I also have them on my wiki, because BOL only got them in an update and I wanted them before that, and also you can add notes on the errors on the wiki.

Need - help to create DLOOKUP with SSIS

Hi,

Does anybody knows how to create a DLOOKUP (dynamic lookup) in SSIS withour writing any kind of script?
I need to test records existance in destination from the source before inserting or updating in the destination (if the record exist in destination then update, else insert).

Any help apreciated.
I find the Lookup component works best for this. Jamie wrote an article that compares lookup as well as other methods.
http://www.sqlis.com/default.aspx?311
Adrian